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
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 |