Thread: Problem close curser after rollback
Hi, we use the following statements in our applications, as described on the site: https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?"; EXEC SQL DECLARE foo_bar CURSOR FOR stmt1; /* when end of result set reached, break out of while loop */ EXEC SQL WHENEVER NOT FOUND DO BREAK; EXEC SQL OPEN foo_bar USING 100; ... while (1) { EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname; ... } EXEC SQL CLOSE foo_bar; After every fetch we open a transaction which is terminated with a rollback in the event of an error. The problem we now have with this is, that the cursor is obviously closed with the rollback, so the next fetch ends withthe error that the cursor no longer exists (sqlcode -400 What can I do so that the cursor is retained despite rollback? The procedure described is very often found in our applications, which have been ported from sybase to postgreSQL. Thanks and kind regards, Sigrid
El día miércoles, septiembre 30, 2020 a las 01:32:41p. m. +0000, Wiltsch,Sigrid escribió: > > Hi, > > we use the following statements in our applications, as described on the site: > > https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS > > EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?"; > EXEC SQL DECLARE foo_bar CURSOR FOR stmt1; > > ... I forgot to mention that the problem is with PostgreSQL 11.4 on SuSE Linux Enterprise. If the problem is not clear, we could rewrite this as a ten-liner in ESQL/C to reproduce it. The OP (Sigrid) and me we're working in the same team. Thanks matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
On Wed, 2020-09-30 at 13:32 +0000, Wiltsch,Sigrid wrote: > we use the following statements in our applications, as described on the site: > > https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS > > EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?"; > EXEC SQL DECLARE foo_bar CURSOR FOR stmt1; > > /* when end of result set reached, break out of while loop */ > EXEC SQL WHENEVER NOT FOUND DO BREAK; > > EXEC SQL OPEN foo_bar USING 100; > ... > while (1) > { > EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname; > ... > } > > EXEC SQL CLOSE foo_bar; > > After every fetch we open a transaction which is terminated with a rollback in the event of an error. > > The problem we now have with this is, that the cursor is obviously closed with the rollback, > so the next fetch ends with the error that the cursor no longer exists (sqlcode -400 > > What can I do so that the cursor is retained despite rollback? > > The procedure described is very often found in our applications, which have been ported from sybase to postgreSQL. You cannot start a transaction while you are reading a cursor; you probably get a warning "there is already a transaction in progress". You could try using a CURSOR WITH HOLD. Such cursors outlast a database transaction. They get materialized on the server during commit. So you could create the WITH HOLD cursor, commit and then start your individual transactions. Don't forget to close the cursor when you are done, else it will use server resources until you close the database connection. Another option is to use savepoints, but you may run into performance problems if you use more than 64 of them and have concurrent database activity. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe escribió: > On Wed, 2020-09-30 at 13:32 +0000, Wiltsch,Sigrid wrote: > > we use the following statements in our applications, as described on the site: > > > > https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS > > > > EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?"; > > EXEC SQL DECLARE foo_bar CURSOR FOR stmt1; > > > > /* when end of result set reached, break out of while loop */ > > EXEC SQL WHENEVER NOT FOUND DO BREAK; > > > > EXEC SQL OPEN foo_bar USING 100; > > ... > > while (1) > > { > > EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname; > > ... > > } > > > > EXEC SQL CLOSE foo_bar; > > > > After every fetch we open a transaction which is terminated with a rollback in the event of an error. > > > > The problem we now have with this is, that the cursor is obviously closed with the rollback, > > so the next fetch ends with the error that the cursor no longer exists (sqlcode -400 > > > > What can I do so that the cursor is retained despite rollback? > > > > The procedure described is very often found in our applications, which have been ported from sybase to postgreSQL. > > You cannot start a transaction while you are reading a cursor; you probably > get a warning "there is already a transaction in progress". Sigrid was not fully correct saying 'After every fetch we open a transaction...' Our layer between application and ESQL/C has a function call DB_strT() to start a transaction. But this does nothing for PostgreSQL because in PostgreSQL' ESQL/C there is no method "START TRANSACTION", see https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS There are only COMMIT and ROLLBACK. I think we will prepare the ten-liner in ESQL/C for further discussion. matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Matthias Apitz <guru@unixarea.de> writes:
> El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe escribió:
>> On Wed, 2020-09-30 at 13:32 +0000, Wiltsch,Sigrid wrote:
>>> What can I do so that the cursor is retained despite rollback?
>> You cannot start a transaction while you are reading a cursor; you probably
>> get a warning "there is already a transaction in progress".
> I think we will prepare the ten-liner in ESQL/C for further discussion.
I don't think you really need to: the point seems clear enough.
I don't especially like the idea you are suggesting though. The general
principle in SQL is that a rolled-back transaction should have no effect
after it's been rolled back. Allowing a cursor it creates to survive
the rollback would fly in the face of that principle.
Quite aside from that, there are technical issues. As a perhaps
egregious case, what if the cursor's output depends on objects that
will disappear in the rollback?
begin;
create type rainbow as enum ('red', 'green', 'blue');
create table r (f1 rainbow);
insert into r values ('red');
declare c cursor with hold for select * from r;
rollback;
fetch all from c;
Even if we surmount the implementation issues around holding onto the
bits emitted by the cursor, the FETCH is going to fail to return data
to the application, because it doesn't know how to interpret those bits
anymore.
So the short answer is you can't have that. Find another way to design
your application.
regards, tom lane
On Wednesday, 30 September 2020 20:37:23 CEST, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Matthias Apitz <guru@unixarea.de> writes: >> El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. >> +0200, Laurenz Albe escribió: >>> On Wed, 2020-09-30 at 13:32 +0000, Wiltsch,Sigrid wrote: >>>> What can I do so that the cursor is retained despite rollback? > >>> You cannot start a transaction while you are reading a >>> cursor; you probably >>> get a warning "there is already a transaction in progress". > >> I think we will prepare the ten-liner in ESQL/C for further discussion. > > I don't think you really need to: the point seems clear enough. > > I don't especially like the idea you are suggesting though. The general > principle in SQL is that a rolled-back transaction should have no effect > after it's been rolled back. Allowing a cursor it creates to survive > the rollback would fly in the face of that principle. The general idea of transactions is that you START one at a moment, go ahead and a ROLLBACK rolls back everything what was done *after* the point of starting it. We have here the case of not beeing able to define the start of the transaction. The application wants to set it *after* the first (and again after each next) fetch. You say, the transaction starts already with that fetch. We have to think how to deal with that missing feature in PostgreSQL ESQL/C to define the point of where the transaction starts. Btw: In all of the other DBS (Informix, Sybase, Oracle) we could define that point with START TRANSACTION. Thanks matthias -- Sent from my Ubuntu phone http://www.unixarea.de/ NO to the EU! NEIN zur EU!
On Wed, Sep 30, 2020 at 09:06:13PM +0200, Matthias Apitz wrote: > Btw: In all of the other DBS (Informix, Sybase, Oracle) we could define that > point with START TRANSACTION. You can always use SET SAVEPOINT. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
El día miércoles, septiembre 30, 2020 a las 02:37:23p. m. -0400, Tom Lane escribió:
> Matthias Apitz <guru@unixarea.de> writes:
> > El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe escribió:
> >> On Wed, 2020-09-30 at 13:32 +0000, Wiltsch,Sigrid wrote:
> >>> What can I do so that the cursor is retained despite rollback?
>
> >> You cannot start a transaction while you are reading a cursor; you probably
> >> get a warning "there is already a transaction in progress".
>
> > I think we will prepare the ten-liner in ESQL/C for further discussion.
>
> I don't think you really need to: the point seems clear enough.
I did wrote the ten-liner to play around with. Interestingly, there exists an
undocumented ESQL/C statement 'EXEC SQL START TRANSACTION' which gives
in the esqlc log:
ECPGtrans on line 48: action "start transaction"; connection "sisis"
What as well does work is the following sequence:
EXEC SQL PREPARE stmt1 FROM "SELECT tstchar25, tstint FROM dbctest WHERE tstint > ?";
EXEC SQL DECLARE foo_bar CURSOR WITH HOLD FOR stmt1 ;
/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL OPEN foo_bar USING 1;
while (1)
{
EXEC SQL FETCH NEXT FROM foo_bar INTO :tstchar25, :tstint;
EXEC SQL COMMIT ;
EXEC SQL START TRANSACTION ;
printf("fetched: [%s] [%d] \n", tstchar25, tstint);
// ... do something with the fetched data and because
// it went wrong, we issue a ROLLBACK
EXEC SQL ROLLBACK ;
}
This fetches nicely through the table in the while-loop; without the
additional COMMIT, the START TRANSACTION gives
ECPGtrans on line 48: action "start transaction"; connection "sisis"
ECPGnoticeReceiver: there is already a transaction in progress
We will think now about what we have learned and how to repair our
application.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)