Re: Problem close curser after rollback

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