Re: Transactions and savepoints
От | Marko Ristola |
---|---|
Тема | Re: Transactions and savepoints |
Дата | |
Msg-id | 424AFC81.6020409@kolumbus.fi обсуждение исходный текст |
Ответ на | Re: Transactions and savepoints (Peter Eisentraut <peter_e@gmx.net>) |
Список | pgsql-odbc |
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? > > >
В списке pgsql-odbc по дате отправления:
Следующее
От: "Greg Campbell"Дата:
Сообщение: Re: psqlODBC 'ERROR: invalid input syntax for integer: "