BUG #19902: DECLARE CURSOR over trigger transition table fails with XX000
BUG #19902: DECLARE CURSOR over trigger transition table fails with XX000
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website: Bug reference: 19902 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64; Docker 18.6 + 20devel master Description: **PostgreSQL version:** 18.6 (official Docker image); also reproduced on a current master build (20devel, `debfb3c8a8c`, 2026-11-12). The missing line dates back to 2017 (`5ebeb579b9b` introduced queryEnv propagation for SPI cursors but not for SQL DECLARE), so all supported versions are presumably affected. **Operating system:** Linux x86_64; platform-independent. **Description:** Inside a trigger function with a transition table (`REFERENCING NEW TABLE AS ...`), a plain SELECT over the transition table works, and a PL/pgSQL `OPEN ... FOR SELECT` cursor (SPI path) works too. But the equivalent SQL statement `DECLARE ... CURSOR FOR SELECT ... FROM newtab` fails with an internal error: ``` ERROR: XX000: executor could not find named tuplestore "newtab" CONTEXT: SQL statement "DECLARE g_decl_cur CURSOR FOR SELECT a FROM newtab" PL/pgSQL function g_decl() line 3 at EXECUTE LOCATION: ExecInitNamedTuplestoreScan, nodeNamedtuplestorescan.c:107 ``` The parser obviously resolves `newtab` (otherwise the error would be `42P01 relation does not exist`); the failure happens at executor startup. Per the docs, transition tables "act like read-only temporary tables that can be accessed by SQL commands issued within the trigger function" â DECLARE CURSOR is such a SQL command. An XX000 internal error reachable from ordinary user SQL is an error-diagnosis defect; the expected behavior is either a working cursor (force-closed at function exit like the SPI path) or a clean "not supported" error, not an internal error. **Reproduction (self-contained SQL; verified identically on 18.6 and master):** ```sql -- Prerequisite: search_path must point at a schema where you may create -- objects (the original finding used an isolated schema; run as the owner). CREATE TABLE g_src(a int); CREATE FUNCTION g_decl() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN EXECUTE 'DECLARE g_decl_cur CURSOR FOR SELECT a FROM newtab'; RETURN NULL; END $$; CREATE TRIGGER g3 AFTER INSERT ON g_src REFERENCING NEW TABLE AS newtab FOR EACH STATEMENT EXECUTE FUNCTION g_decl(); INSERT INTO g_src VALUES (6),(7); -- ERROR: executor could not find named tuplestore "newtab" -- Controls (same trigger function shape, same transition table): -- A) plain SELECT over the transition table: works CREATE FUNCTION g_sel() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE n int; BEGIN SELECT count(*) INTO n FROM newtab; RAISE NOTICE 'plain SELECT count = %', n; RETURN NULL; END $$; CREATE TRIGGER g1 AFTER INSERT ON g_src REFERENCING NEW TABLE AS newtab FOR EACH STATEMENT EXECUTE FUNCTION g_sel(); INSERT INTO g_src VALUES (1),(2),(3); -- NOTICE: plain SELECT count = 3 DROP TRIGGER g1 ON g_src; -- B) PL/pgSQL OPEN (SPI cursor) over the transition table: works CREATE FUNCTION g_open() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE r refcursor := 'g_open_cur'; x int; BEGIN OPEN r FOR SELECT a FROM newtab ORDER BY a; FETCH r INTO x; RAISE NOTICE 'OPEN/FETCH got %', x; RETURN NULL; END $$; CREATE TRIGGER g2 AFTER INSERT ON g_src REFERENCING NEW TABLE AS newtab FOR EACH STATEMENT EXECUTE FUNCTION g_open(); INSERT INTO g_src VALUES (4),(5); -- NOTICE: OPEN/FETCH got 4 DROP TRIGGER g2 ON g_src; ``` Additional verified variants (all fail with the same XX000): `DECLARE ... CURSOR WITH HOLD`, `SCROLL`/`BINARY` cursors, `OLD TABLE` transition tables, AFTER ROW triggers with transition tables, ENR referenced via IN-subquery / scalar subquery / CTE / LATERAL (whenever the planner keeps a NamedTuplestoreScan node). The error is catchable by `EXCEPTION WHEN OTHERS`, no portal is left behind, and the server stays alive â a diagnosis defect, not a crash. **Root cause:** `PerformCursorOpen()` (src/backend/commands/portalcmds.c) parses/rewrites using `pstate->p_queryEnv` (which is why the ENR RTE is built), then calls `CreatePortal()` â `PortalDefineQuery()` â `PortalStart()`, but never assigns `pstate->p_queryEnv` to `portal->queryEnv`. `PortalStart()` builds the QueryDesc from `portal->queryEnv`; `ExecInitNamedTuplestoreScan()` therefore gets a NULL query environment, `get_ENR(NULL, "newtab")` returns NULL, and the executor raises the internal error. The SPI path does it right: `SPI_cursor_open_internal()` (spi.c) sets `portal->queryEnv = _SPI_current->queryEnv` â that line was added by `5ebeb579b9b` (2017, "Follow-on cleanup for the transition table patch") which did not touch portalcmds.c. `git grep queryEnv src/backend/commands/portalcmds.c` finds zero occurrences on master and on REL_14..REL_19. A related contract mismatch with the same root: because the DECLARE-built portal has `queryEnv == NULL`, `DropQueryEnvPortals()` (portalmem.c) does not force-close it at SPI_finish, so such a cursor (when the planner happens to remove the ENR scan node, e.g. via `WHERE false AND ...`, the DECLARE succeeds) survives the function and remains visible in `pg_cursors` â contrary to spi.sgml's "all cursors are automatically closed when the function exits". **Expected behavior:** `PerformCursorOpen()` should set `portal->queryEnv = pstate->p_queryEnv` (mirroring SPI_cursor_open_internal), making SQL DECLARE over transition tables behave like the SPI path; the force-close machinery would then cover these portals too.