pypgsql -- not preserving transactions ?

[Gerhard Häring]

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