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