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

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(dot)abzalov(at)gmail(dot)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*
>