pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure
2+ messages / 2 participants
[nested] [flat]
* BUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure
@ 2025-10-14 03:59 PG Bug reporting form <noreply@postgresql.org>
2025-10-14 05:34 ` Re: BUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-10-14 03:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: wangsiyan2@huawei.com
The following bug has been logged on the website:
Bug reference: 19085
Logged by: siyan wang
Email address: wangsiyan2@huawei.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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure
2025-10-14 03:59 BUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure PG Bug reporting form <noreply@postgresql.org>
@ 2025-10-14 05:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-10-14 05:34 UTC (permalink / raw)
To: wangsiyan2@huawei.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
I cannot reproduce this assertion, either in master or REL_18_STABLE
branch tip. Are you testing community Postgres, or some locally
modified version?
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-10-14 05:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-14 03:59 BUG #19085: Using EXECUTE to run a multi-statement SQL string causes an assertion failure PG Bug reporting form <noreply@postgresql.org>
2025-10-14 05:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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