Recovering from Postgres errors

[Kent Johnson]

When accessing a Postgres database from Django ORM without any explicit
transaction management, any database query begins an implicit
transaction which ends with the next save().

If the save fails due to a database error (e.g. integrity violation,
timeout...) it raises an exception. At this point, any new queries will
fail with the error
psycopg2.ProgrammingError: current transaction is aborted, commands
ignored until end of transaction block

Basically the Postgres transaction is dead and has to be rolled back
before any new queries can be executed.

ISTM the Model.save() method itself could rollback the (implicit)
transaction when there is an error, something like

try:
... normal save code
except DatabaseError:
transaction.rollback_unless_managed()
raise

I have seen a bit of discussion of this in the list archives and Trac
but I haven't found any good solutions. Wrapping every save() in a try
block similar to the above seems very clumsy, as is wrapping every
save() in an explicit transaction block.

So, my questions are
- how do others deal with this?
- what is considered best practice, if anything?
- does it make sense to alter Model.save(), or my own model's save(), as
indicated above?

Thanks,
Kent

[Kent Johnson]

A simpler question - is there any reason *not* to include this code in
my model's save() method?

try:
Model.save(self)
except DatabaseError:
transaction.rollback_unless_managed()
raise

The benefits:
- automatic recovery from failed unmanaged transactions - very handy
when working in the shell, or when performing multiple updates that
don't have to succeed or fail as a group
- no effect when inside an explicit (managed) transaction

Thanks,
Kent

[meppum]

I have also begun to run into this issue, and I agree with Kent here.
If transaction management is set to the default (automatic) then
failed saves should automatically rollback.

[Joshua D. Drake]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 7 Mar 2008 09:44:27 -0800 (PST)
meppum <mme...@gmail.com> wrote:

>
> I have also begun to run into this issue, and I agree with Kent here.
> If transaction management is set to the default (automatic) then
> failed saves should automatically rollback.

That would seem to follow the whole point of transactions :P

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH0YFyATb/zqfZUUQRAo/ZAJ9M0TCYeVWICZRG2f2Siw6A6MVdmQCePyJO
+EoePyt43qdEysG2azdCA08=
=lTjR
-----END PGP SIGNATURE-----