Thread

BUG #19107: The hold cursor is unexpectedly released during rollback

PG Bug reporting form <noreply@postgresql.org> — 2025-11-10T10:46:44Z

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