Re: Transactions and savepoints

Hello.

I have been thinking about savepoints.

What do you think about the following:

Create transaction.h and transaction.c.

They would keep the knoledge about the following things:
- Current transaction state (is in autocommit mode, in transaction,
and the savepoint stack structure.
- Each transaction stack point would maintain a list of open cursors.

The stack would contain the following things:
- At the bottom of the stack is the autocommit mode (tr[0]).
- tr[1] contains the transaction's BEGIN block state.
- tr[2] contains the information about the first savepoint.

This would make the savepoint rollback work with cursors:

ODBC side manages the open cursors during savepoint rollback.
Rolling back a savepoint destroys that savepoint's and the later savepoint's
cursors. ODBC must not try to close the cursor after rollback,
or a database error occurs.

What do you think? Is this transaction state tracking necessary in the
ODBC side? Or does or will the database backend handle this some day
some way?

Marko Ristola

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?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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