cursor already in use, UPDATE RETURNING bug?

From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: cursor already in use, UPDATE RETURNING bug?
Date: 2021-03-31 00:39:14
Message-ID: CAJKUy5ikWZu1z7o8Ku0oN0Fiyu3_NV-CpbgSjTNTC0D=-vg89w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Just noted an interesting behaviour when using a cursor in a function
in an UPDATE RETURNING (note that INSERT RETURNING has no problem).

I have seen this problem in all versions I tested (9.4 thru master).
Steps to reproduce:

prepare the test
```
create table t1 as select random() * foo i from generate_series(1, 100) foo;
create table t2 as select random() * foo i from generate_series(1, 100) foo;

CREATE OR REPLACE FUNCTION cursor_bug()
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare
c1 cursor (p1 int) for select count(*) from t1 where i = p1;
n int4;
begin
open c1 (77);
fetch c1 into n;
return n;
end $function$
;
```

-- this ends fine
insert into t2 values(5) returning cursor_bug() as c1;
c1
----
0
(1 row)

-- this fails
update t2 set i = 5 returning cursor_bug() as c1;
ERROR: cursor "c1" already in use
CONTEXT: PL/pgSQL function cursor_bug() line 6 at OPEN

--
Jaime Casanova
Director de Servicios Profesionales
SYSTEMGUARDS - Consultores de PostgreSQL

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2021-03-31 00:41:56 Re: multi-install PostgresNode fails with older postgres versions
Previous Message Michael Paquier 2021-03-31 00:37:44 Re: Extensions not dumped when --schema is used