Re: Transactions and savepoints

The needed new feature, the savepoint, is implemented
into PostgreSQL 8.0.1.

Tom Zschockelt asked about his savepoint problem
from this list at February 15 this year.
Savepoints are a new great feature in 8.0.

PostgreSQL savepoints are almost equal with a
SQL standard. sql-savepoint.html tells one difference below.

Here are the PostgreSQL documents:

http://www.postgresql.org/docs/8.0/static/sql-savepoint.html
http://www.postgresql.org/docs/8.0/static/sql-release-savepoint.html
http://www.postgresql.org/docs/8.0/static/sql-rollback-to.html

I tested the savepoint a few days back.
Savepoint creation worked, but the following
command failed:
ROLLBACK TO SAVEPOINT my_savepoint;
This failed, because in CC_send_query sees the first ROLLBACK word,
but does not distinquishe between a savepoint rollback and
a transaction rollback.

I found also, that savepoint rollback closes the failed savepoint's cursors.

So here is the cursor problem (with psql):
marko=# begin;
BEGIN
marko=# savepoint aabb;
SAVEPOINT
marko=# declare cc cursor for select * from test1;
DECLARE CURSOR
marko=# rollback to aabb;
ROLLBACK
marko=# close cc;
ERROR: cursor "cc" does not exist
ERROR: cursor "cc" does not exist
marko=# select * from test1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
ERROR: current transaction is aborted, commands ignored until end of
transaction block
marko=# rollback;
ROLLBACK

So, with open select cursors the ODBC driver does not know
that the backend just closed a cursor with the rollback.
If the ODBC driver tries to close an already closed cursor,
thus making the outer transaction context fail (the case above).

So we need the Transaction* object or something,
to implement the stack for savepoint tracking and to keep track,
in which context each cursor has been created.

Maybe we could get some support from the backend in
PostgreSQL 8.1, and forget the backend state tracking completely?
A needed message might be a notification from the backend
about every closed cursor during savepoint rollback and savepoint commit.

Marko Ristola

Peter Eisentraut wrote:

>Marko Ristola wrote:
>
>
>>I have been thinking about savepoints.
>>What do you think about the following:
>>
>>
>
>The interface and behavior of the ODBC driver is bound by various public
>standards such as the SQL standard and the Microsoft ODBC
>pseudostandard. So any new functionality should be based on either of
>these sources. Does that apply to your proposal?
>
>
>
>>They would keep the knoledge about the following things:
>>- Current transaction state (is in autocommit mode, in transaction,
>> and the savepoint stack structure.
>>
>>
>
>This information is available on the protocol level and does not to be
>tracked.
>
>
>
>>This would make the savepoint rollback work with cursors:
>>
>>
>
>I'm not exactly up to speed on how savepoints interact with cursors
>nowadays. Perhaps you could first explain what problem you are trying
>to solve?
>
>
>