Thread

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

   PG Bug reporting form <noreply@postgresql.org> — 2025-10-14T03:59:49Z

   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.
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-14T05:34:05Z

   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