Postgres Pro
Downloads
Home   >   mailing lists

Thread: cursor already in use, UPDATE RETURNING bug?

cursor already in use, UPDATE RETURNING bug?

From
Jaime Casanova
Date:
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

Re: cursor already in use, UPDATE RETURNING bug?

From
Ashutosh Bapat
Date:
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

Re: cursor already in use, UPDATE RETURNING bug?

From
Jaime Casanova
Date:
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