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