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 (performed twice) on commit if it isdeclared as "cursor with hold" and the cursor is not closed yet
From
Rashid Abzalov
Date:
The statement is re-executed on commit if it is declared as "cursor with hold" and the cursor is not closed yet.
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
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
Re: The statement is re-executed (performed twice) on commit if it isdeclared as "cursor with hold" and the cursor is not closed yet
From
Rashid Abzalov
Date:
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
Re: The statement is re-executed (performed twice) on commit if it is declared as "cursor with hold" and the cursor is not closed yet
From
Tom Lane
Date:
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
Re: The statement is re-executed (performed twice) on commit if it isdeclared as "cursor with hold" and the cursor is not closed yet
From
Rashid Abzalov
Date:
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.
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