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: Rashid Abzalov <rashid(dot)abzalov(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: The statement is re-executed (performed twice) on commit if it is declared as "cursor with hold" and the cursor is not closed yet
Date: 2019-07-12 19:18:49
Message-ID: CACrSCdEquRApzys++BED0=486rhAtrKnWrf4Y9mc2nhsGVUmrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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*

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Rashid Abzalov 2019-07-12 20:19:02 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
Previous Message Aayush Chaturvedi 2019-07-12 18:45:12 Re: BUG #15905: FATAL: the database system is starting up