[Pypgsql-devel] [ pypgsql-Bugs-1409818 ] Problem using SAVEPOINT

[SourceForge.net <no...@so...>]

Bugs item #1409818, was opened at 2006-01-19 06:03
Message generated for change (Comment added) made by ballie01
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1409818&group_id=16528

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: libpq
Group: None
Status: Open
>Resolution: Later
Priority: 5
Private: No
Submitted By: Peer (peergriebel)
>Assigned to: Billy G. Allie (ballie01)
Summary: Problem using SAVEPOINT

Initial Comment:
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.

----------------------------------------------------------------------

>Comment By: Billy G. Allie (ballie01)
Date: 2007-04-05 00:23

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.

----------------------------------------------------------------------

Comment By: Gerhard Häring (ghaering)
Date: 2006-06-02 09:55

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

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1409818&group_id=16528