pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19107: The hold cursor is unexpectedly released during rollback
4+ messages / 3 participants
[nested] [flat]
* BUG #19107: The hold cursor is unexpectedly released during rollback
@ 2025-11-10 10:46 PG Bug reporting form <noreply@postgresql.org>
2025-11-10 10:50 ` Re:BUG #19107: The hold cursor is unexpectedly released during rollback =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
2025-11-10 16:04 ` Re: BUG #19107: The hold cursor is unexpectedly released during rollback Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2025-11-10 10:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: zengman@halodbtech.com
The following bug has been logged on the website:
Bug reference: 19107
Logged by: Man Zeng
Email address: zengman@halodbtech.com
PostgreSQL version: 18.0
Operating system: Ubuntu 24.04
Description:
Hi, hackers.
While I was writing test cases for the hold cursor, I unexpectedly
discovered that commit and rollback handle it inconsistently—rollback
unexpectedly releases the hold cursor. Should this be regarded as a bug?
--
Regrads,
Man Zeng
postgres=# -- ok
postgres=# DO $$
DECLARE
val int;
BEGIN
FOR val IN SELECT generate_series(1,10) LOOP
raise notice 'val = %', val;
IF val % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END; $$;
NOTICE: val = 1
NOTICE: val = 2
NOTICE: val = 3
NOTICE: val = 4
NOTICE: val = 5
NOTICE: val = 6
NOTICE: val = 7
NOTICE: val = 8
NOTICE: val = 9
NOTICE: val = 10
DO
postgres=#
postgres=# -- hold_cursor commit ok
postgres=# do $$
declare
p_CurData refcursor := 'hold_cursor';
val int;
begin
execute 'declare hold_cursor CURSOR WITH HOLD FOR SELECT 42';
loop
fetch p_CurData into val;
exit when val is null;
raise notice 'val = %', val;
commit;
end loop;
close p_CurData;
end; $$;
NOTICE: val = 42
DO
postgres=#
postgres=# -- hold_cursor rollback error
postgres=# do $$
declare
p_CurData refcursor := 'hold_cursor';
val int;
begin
execute 'DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT 42';
loop
fetch p_CurData into val;
exit when val is null;
raise notice 'val = %', val;
rollback;
end loop;
close p_CurData;
end; $$;
NOTICE: val = 42
ERROR: cursor "hold_cursor" does not exist
CONTEXT: PL/pgSQL function inline_code_block line 8 at FETCH
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re:BUG #19107: The hold cursor is unexpectedly released during rollback
2025-11-10 10:46 BUG #19107: The hold cursor is unexpectedly released during rollback PG Bug reporting form <noreply@postgresql.org>
@ 2025-11-10 10:50 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
1 sibling, 0 replies; 4+ messages in thread
From: zengman @ 2025-11-10 10:50 UTC (permalink / raw)
To: 曾满 <zengman@halodbtech.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
If this is indeed a bug, a simple patch is attached to fix this issue.
Attachments:
[application/octet-stream] 001_rollback_no_release_holdcursor.patch (2.8K, ../../tencent_05B43F1B06161796748A12E1@qq.com/2-001_rollback_no_release_holdcursor.patch)
download | inline diff:
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 943da087c9f..15d5fdf448d 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1238,6 +1238,14 @@ HoldPinnedPortals(void)
HoldPortal(portal);
portal->autoHeld = true;
}
+ else if ((portal->cursorOptions & CURSOR_OPT_HOLD) &&
+ portal->createSubid != InvalidSubTransactionId &&
+ portal->status == PORTAL_READY)
+ {
+ /* Is it a holdable portal created in the current xact? */
+ HoldPortal(portal);
+ portal->autoHeld = true;
+ }
}
}
diff --git a/src/pl/plpgsql/src/expected/plpgsql_simple.out b/src/pl/plpgsql/src/expected/plpgsql_simple.out
index da351873e74..3a6342c3abd 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_simple.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_simple.out
@@ -129,3 +129,39 @@ begin
raise notice 'val = %', val;
end; $$;
NOTICE: val = 42
+-- Verify whether the behavior of the hold cursor is consistent during commit and rollback
+do $$
+declare
+ p_CurData refcursor := 'hold_cursor';
+ val int;
+begin
+ execute 'DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT 42';
+ loop
+ fetch p_CurData into val;
+ exit when val is null;
+ raise notice 'val = %', val;
+ commit;
+ end loop;
+ close p_CurData;
+end; $$;
+NOTICE: val = 42
+do $$
+declare
+ p_CurData refcursor := 'hold_cursor';
+ val int;
+begin
+ execute 'DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT 42';
+ loop
+ fetch p_CurData into val;
+ exit when val is null;
+ raise notice 'val = %', val;
+ rollback;
+ end loop;
+ close p_CurData;
+end; $$;
+NOTICE: val = 42
+select * from pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable | creation_time
+------+-----------+-------------+-----------+---------------+---------------
+(0 rows)
+
diff --git a/src/pl/plpgsql/src/sql/plpgsql_simple.sql b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
index 72d8afe4500..898721ea97b 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_simple.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
@@ -114,3 +114,36 @@ begin
fetch p_CurData into val;
raise notice 'val = %', val;
end; $$;
+
+-- Verify whether the behavior of the hold cursor is consistent during commit and rollback
+do $$
+declare
+ p_CurData refcursor := 'hold_cursor';
+ val int;
+begin
+ execute 'DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT 42';
+ loop
+ fetch p_CurData into val;
+ exit when val is null;
+ raise notice 'val = %', val;
+ commit;
+ end loop;
+ close p_CurData;
+end; $$;
+
+do $$
+declare
+ p_CurData refcursor := 'hold_cursor';
+ val int;
+begin
+ execute 'DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT 42';
+ loop
+ fetch p_CurData into val;
+ exit when val is null;
+ raise notice 'val = %', val;
+ rollback;
+ end loop;
+ close p_CurData;
+end; $$;
+
+select * from pg_cursors;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19107: The hold cursor is unexpectedly released during rollback
2025-11-10 10:46 BUG #19107: The hold cursor is unexpectedly released during rollback PG Bug reporting form <noreply@postgresql.org>
@ 2025-11-10 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-11-10 16:37 ` Re: BUG #19107: The hold cursor is unexpectedly released during rollback =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-11-10 16:04 UTC (permalink / raw)
To: zengman@halodbtech.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> While I was writing test cases for the hold cursor, I unexpectedly
> discovered that commit and rollback handle it inconsistently—rollback
> unexpectedly releases the hold cursor. Should this be regarded as a bug?
No, that's intentional. The rollback might be cleaning up a failure
in the held cursor.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19107: The hold cursor is unexpectedly released during rollback
2025-11-10 10:46 BUG #19107: The hold cursor is unexpectedly released during rollback PG Bug reporting form <noreply@postgresql.org>
2025-11-10 16:04 ` Re: BUG #19107: The hold cursor is unexpectedly released during rollback Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-11-10 16:37 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
0 siblings, 0 replies; 4+ messages in thread
From: zengman @ 2025-11-10 16:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Oh, I didn't think of that―thank you so much for your guidance!
Regrads,
Man Zeng
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-11-10 16:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-10 10:46 BUG #19107: The hold cursor is unexpectedly released during rollback PG Bug reporting form <noreply@postgresql.org>
2025-11-10 10:50 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
2025-11-10 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-11-10 16:37 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox