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

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