BUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure

The following bug has been logged on the website:

Bug reference: 19085
Logged by: siyan wang
Email address: wangsiyan2(at)huawei(dot)com
PostgreSQL version: 18.0
Operating system: centos 7.5
Description:

The issue is triggered by a PL/pgSQL procedure that dynamically executes a
multi-statement SQL string via EXECUTE, combining DECLARE CURSOR and FETCH
(e.g., "DECLARE test_cursor_1_1 CURSOR FOR SELECT * FROM test_table_1; FETCH
200 FROM test_cursor_1_1"). This fails with
Assert(ActiveSnapshot->as_snap->regd_count == 0) in snapmgr.c:754 because
the snapshot's registration count (regd_count) reaches 2 during SPI's
multi-statement processing loop.

Reproduction Steps
1. Create a sequence (test_cursor_seq) and tables.
CREATE OR REPLACE PROCEDURE create_mass_tables(num_tables INT)
LANGUAGE plpgsql
AS $$
DECLARE
table_name text;
seq_num int;
BEGIN
IF num_tables <= 0 THEN
RAISE EXCEPTION 'Number of tables must be a positive integer';
END IF;

DROP SEQUENCE IF EXISTS test_table_seq;
CREATE SEQUENCE test_table_seq START 1;

FOR i IN 1..num_tables LOOP
seq_num := nextval('test_table_seq');
table_name := format('test_table_%s', seq_num);

EXECUTE format('
CREATE TABLE IF NOT EXISTS %I (
id int,
data TEXT
); INSERT INTO %I
VALUES(generate_series(1,3200),''aSdewqE12dShajKdaDsAd3_wr'');', table_name,
table_name);

IF i % 1000 = 0 THEN
RAISE NOTICE 'Progress: Created %/% tables', i, num_tables;
END IF;
END LOOP;

RAISE NOTICE 'Completed: % tables created', num_tables;
END;
$$;
2. Define the procedure open_multicursors (as shown in user input), which
uses EXECUTE to run a multi-statement cursor creation and fetch.
CREATE SEQUENCE test_cursor_seq START 1;

CREATE OR REPLACE PROCEDURE open_multicursors(cursor_num integer)
LANGUAGE plpgsql
AS $$
DECLARE
seq_num int;
cursor_name text;
target_table text;
i integer;
o integer;
BEGIN

FOR i IN 1..cursor_num LOOP
seq_num := nextval('test_cursor_seq');
target_table := 'test_table_' || seq_num;
cursor_name := 'test_cursor_' || seq_num || '_1';
EXECUTE format('DECLARE %I CURSOR FOR SELECT * FROM %I',
cursor_name, target_table);
EXECUTE format('FETCH 200 FROM %I', cursor_name);
END LOOP;

PERFORM pg_sleep(180000);

EXCEPTION
WHEN others THEN
ROLLBACK;
RAISE;
END;
$$;
3. Execute 'CALL create_mass_tables(10);'
4. Execute 'CALL open_multicursors(1);'
5. Observe the assertion failure: TRAP: failed
Assert("ActiveSnapshot->as_snap->regd_count == 0"), with server abort
(signal 6).

GDB Observations
1. First UpdateActiveSnapshotCommandId(for CALL create_mass_tables) has
regd_count=0.
2. Second UpdateActiveSnapshotCommandId(for CALL open_multicursors) has
regd_count=2, triggering the failure in UpdateActiveSnapshotCommandId.

I guess this problem is because SPI processes multi-statement EXECUTE as a
single plan, looping over sub-statements. DECLARE CURSOR registers a
snapshot for cursor stability, and FETCH may increment regd_count again. The
subsequent CID update fails the assertion, as registered snapshots should be
immutable.
Becasue when I changed open_multicursors as following, the error is
disappeared.
CREATE OR REPLACE PROCEDURE open_multicursors(cursor_num integer)
LANGUAGE plpgsql
AS $$
DECLARE
seq_num int;
cursor_name text;
target_table text;
i integer;
o integer;
BEGIN

FOR i IN 1..cursor_num LOOP
seq_num := nextval('test_cursor_seq');
target_table := 'test_table_' || seq_num;
cursor_name := 'test_cursor_' || seq_num || '_1';
EXECUTE format('DECLARE %I CURSOR FOR SELECT * FROM %I',
cursor_name, target_table);
EXECUTE format('FETCH 200 FROM %I', cursor_name);
END LOOP;

PERFORM pg_sleep(180000);

EXCEPTION
WHEN others THEN
ROLLBACK;
RAISE;
END;
$$;

My question is:
1. If assertions are disabled (production), modifying a registered
snapshot's CID could cause visibility issues, will it violating
read-committed isolation?
2. Typically, EXECUTE does not execute multiple statements. Should we
prohibit executing multiple SQL statements within EXECUTE, or should we
modify the assert?

This bug, reproducible in PostgreSQL 18.0 and earlier.