cursor already in use, UPDATE RETURNING bug?

On Wed, Mar 31, 2021 at 6:09 AM Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

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)

cursor_bug() is called only once here.

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

but that's called as many time as the number of rows in t2 in the same
transaction. The first row will go fine. For the second row it will
find c1 is already open. Shouldn't cursor_bug() close c1 at the end?
Is it intended to be kept open when the function finishes? May be you
are expecting it to be closed automatically when the function
finishes. But that's not what is documented at
https://www.postgresql.org/docs/13/plpgsql-cursors.html.

--
Best Wishes,
Ashutosh Bapat

On Wed, Mar 31, 2021 at 7:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Mar 31, 2021 at 6:09 AM Jaime Casanova

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

but that's called as many time as the number of rows in t2 in the same
transaction. The first row will go fine. For the second row it will
find c1 is already open. Shouldn't cursor_bug() close c1 at the end?
Is it intended to be kept open when the function finishes? May be you
are expecting it to be closed automatically when the function
finishes. But that's not what is documented at
https://www.postgresql.org/docs/13/plpgsql-cursors.html.

Now that I see it again, after sleeping, I can see you're right! sorry
for the noise

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