| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | 303677365(at)qq(dot)com |
| Subject: | BUG #19902: DECLARE CURSOR over trigger transition table fails with XX000 |
| Date: | 2026-11-15 03:32:13 |
| Message-ID: | 19902-3f34fad0a1199e2e@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19902
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | shihao zhong | 2026-11-15 03:33:17 | Re: bug: Gather rescan keeps the first scan's tuple bound in workers |
| Previous Message | Joao Rito | 2026-11-15 03:08:32 | Re: BUG #19798: failed to build any 4-way joins |