Thread: The statement is re-executed (performed twice) on commit if it isdeclared as "cursor with hold" and the cursor is not closed yet

The statement is re-executed on commit if it is declared as "cursor with hold" and the cursor is not closed yet.

Tested on 11.2 and 9.6.12.

1) DDL:
create table test(id numeric);
create or replace function do_test() returns void
as $$
begin
  raise notice 'test executed!';
  insert into test(id) values(1);
end;
$$ LANGUAGE plpgsql VOLATILE security definer

DML statements below are executed with autocommit = off (for example in PgAdmin3)

2) DML (cursor is closed after commit):

begin

declare exec_cur binary no scroll cursor with hold for select do_test()

fetch forward 1 from exec_cur

--close exec_cur

commit

close exec_cur

select count(*) from test
---
2


3) DML (cursor is closed before commit):

begin

declare exec_cur binary no scroll cursor with hold for
  select do_test()

fetch forward 1 from exec_cur

close exec_cur

commit

--close exec_cur

select count(*) from test
---
1

The bug is not reproduced if declare the cursor query as
  select * from do_test()

declare exec_cur binary no scroll cursor with hold for select * from do_test();

пт, 12 июл. 2019 г. в 22:18, Rashid Abzalov <rashid.abzalov@gmail.com>:
The statement is re-executed on commit if it is declared as "cursor with hold" and the cursor is not closed yet.

Tested on 11.2 and 9.6.12.

1) DDL:
create table test(id numeric);
create or replace function do_test() returns void
as $$
begin
  raise notice 'test executed!';
  insert into test(id) values(1);
end;
$$ LANGUAGE plpgsql VOLATILE security definer

DML statements below are executed with autocommit = off (for example in PgAdmin3)

2) DML (cursor is closed after commit):

begin

declare exec_cur binary no scroll cursor with hold for select do_test()

fetch forward 1 from exec_cur

--close exec_cur

commit

close exec_cur

select count(*) from test
---
2


3) DML (cursor is closed before commit):

begin

declare exec_cur binary no scroll cursor with hold for
  select do_test()

fetch forward 1 from exec_cur

close exec_cur

commit

--close exec_cur

select count(*) from test
---
1

Rashid Abzalov <rashid.abzalov@gmail.com> writes:
> The statement is re-executed on commit if it is declared as "cursor with
> hold" and the cursor is not closed yet.

That is not a bug, it's how cursors with hold work.

(Volatile functions in cursors are a pretty fraught issue all around.
I do not think we make very many guarantees about how often they'll
be executed, if you do any re-reading or re-positioning of the cursor.)

            regards, tom lane



This is how cursors with hold work NOW. And this is the flaws of current implementation.
It is possible to agree with these statements.

But it is impossible to agree that they SHOULD work this way.
At a minimum, this is neither obvious nor logical, and also not documented anywhere. I think for many it would be a big surprise.

It is obvious that the DBMS needs to materialize the result of the query at the time of the end of the transaction, but for this it is not necessary to re-execute the query again.
It could begin to materialize at the moment of opening the cursor (if it is scrollable), and at the end of the transaction - materialize its rest, for example, by fetching the rest of cursor, or implement the ability the reading data from the point of view of the current transaction.


With regards to the reasons for using the cursor.
At the moment, it is a necessary measure, because Postgres does not provide alternatives for calling functions from the client, with INOUT or OUT parameters, with the further possibility of obtaining modified values after execution.
  - perform statement - is not allowed from clients
  - unnamed blocks - do not allow to work with parameters at all (neither before execution, nor after)

пт, 12 июл. 2019 г. в 23:33, Tom Lane <tgl@sss.pgh.pa.us>:
Rashid Abzalov <rashid.abzalov@gmail.com> writes:
> The statement is re-executed on commit if it is declared as "cursor with
> hold" and the cursor is not closed yet.

That is not a bug, it's how cursors with hold work.

(Volatile functions in cursors are a pretty fraught issue all around.
I do not think we make very many guarantees about how often they'll
be executed, if you do any re-reading or re-positioning of the cursor.)

                        regards, tom lane