pypgsql -- not preserving transactions ?
Gerhard Häring
gerhard.haering at gmx.de
Tue Sep 17 03:41:07 EDT 2002
Frank Miles wrote in comp.lang.python:
> Gerhard Häring <gerhard.haering at gmx.de> wrote:
>>* Frank Miles <fpm at u.washington.edu> [2002-09-16 03:15 +0000]:
>>> I've discovered that the 2.0 version of pyPgSQL packaged with Debian/woody
>>> doesn't preserve transactions...
> [snip]
>
>>I can't reproduce that behaviour here. What do you do to ignore the
>>PgSQL.OperationalError? Or is it a different exception that gets thrown?
>
> Yes, that exception gets thrown. Perhaps this simply reveals a gap in my
> understanding -- I thought that it was Postgres that was handling the
> transactions.
Yeah, but pyPgSQL keeps track of wether we're in a transaction
> Presuming that the cursor wasn't starting a new transaction,
Ignoring the autocommit behaviour that's off by default: In reality,
the first cursor in a connection will automatically start a new
transaction. So do all other methods on the cursor object, if there is
no open transaction.
> even if the dumb user used a try/except block to bypass the exception (and
> continue with succeeding INSERT attempts), shouldn't Postgres prevent those
> later INSERTs?
An implicit rollback will be issued in case of an OperationalError.
The next cursor.execute() will open a new transaction, because there
is no open transaction now, which leads to the behaviour you describe.
>>This area of code hasn't changed up to 2.2, IIRC. Could you provide
>>a test case [...]
> Sure, I've got some simple test code. But at this point I'm now
> thinking that this is more likely a difference in conception -- what
> _should_ pypgsql do?
As far as I see, the current behaviour is what it should do.
> Should the application have to track if/when exceptions occur? It's
> probably more efficient, though ISTM less clean to do it this way.
The problem as far as I can see is that you're using code like:
for item in data:
try:
cursor.execute("INSERT INTO ...")
except OperationalError:
pass # better log error here
conn.commit()
which leads to the behaviour I described above.
If you want either all or none of the inserts to succeed, you should
use code like this instead:
try:
for item in data:
cursor.execute("INSERT INTO ...")
conn.commit()
except OperationalError:
pass # better log error here
> Thanks for your reply, Gerhard. Can you (or someone else) confirm
> that the "error" is a difference in conception?
Looks like so.
-- Gerhard
More information about the Python-list
mailing list