Re: cursor already in use, UPDATE RETURNING bug?
On Wed, Mar 31, 2021 at 7:50 AM Ashutosh Bapat 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
Re: cursor already in use, UPDATE RETURNING bug?
On Wed, Mar 31, 2021 at 6:09 AM Jaime Casanova 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
cursor already in use, UPDATE RETURNING bug?
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
