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.
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.
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.