BUG #19902: DECLARE CURSOR over trigger transition table fails with XX000

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.

Browse pgsql-bugs by date

  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