#91 Problem using SAVEPOINT

open-later
libpq (15)
5
2007-04-05
2006-01-19
Peer
No

We are using PostgreSQL 8.1.1 with
pyPgSQL-2.4.win32-py2.3.exe.

Executing the following code in e.g. pgadmin works as
expected:

SAVEPOINT APS_SAVEPOINT2;
SELECT persons.oid FROM persons WHERE person_id = '9';
ROLLBACK TO APS_SAVEPOINT2;
SELECT persons.oid FROM persons WHERE person_id = '9'

Executing the code inside of the python interface to
PostgreSQL using:

from pyPgSQL import PgSQL

connection = PgSQL.connect(...)
cursor=connection.cursor()
cursor.execute("SAVEPOINT APS_SAVEPOINT2")
cursor.execute("SELECT persons.oid FROM persons WHERE
person_id = '9'")
cursor.execute("ROLLBACK TRANSACTION TO SAVEPOINT
APS_SAVEPOINT2")
cursor.execute("SAVEPOINT APS_SAVEPOINT3")
cursor.execute("SELECT persons.oid FROM persons WHERE
person_id = '9'")

does not work. An exception is raised:

libpq.OperationalError: ERROR: current transaction is
aborted, commands ignored until end of transaction block

I can't see the reason. But I think it lies somewhere
in libpq.

Discussion

  • Logged In: YES
    user_id=163326

    I believe it has to do with portals. If you close the cursor
    and create a new one before the "ROLLBACK TRANSACTION", you
    don't get this error message.

     
  • Billy G. Allie
    Billy G. Allie
    2007-04-05

    • assigned_to: nobody --> ballie01
    • status: open --> open-later
     
  • Billy G. Allie
    Billy G. Allie
    2007-04-05

    Logged In: YES
    user_id=8500
    Originator: NO

    The problem is that pyPgSQL does not understand savepoints. When the cursor sees a second SELECT without a connection.rollback() or connection.commit(), it assumes that the programmer is finished with the first SELECT and resets the cursor object back to it's initial state (rolling back the transaction if one exists).

    The workaround is to turn on autocommit and start (and end) the transaction yourself.

    The fix is to add support for savepoints to pyPgSQL.