The statement is re-executed (performed twice) on commit if it is declared 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>:
Show quoted text
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>:
Show quoted text
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