| From: | Raja Sai pranav <rajasaipranav0(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | 303677365(at)qq(dot)com |
| Subject: | Re: BUG #19902: DECLARE CURSOR over trigger transition table fails with XX000 |
| Date: | 2026-11-20 16:57:50 |
| Message-ID: | nh-4419e724-8f37-4d3b-8e87-3888c034f9e7@google.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi,
I reproduced this on master (4a26e36f87) and attached a patch.
As the report says, PerformCursorOpen() never sets portal->queryEnv, so the
executor can't find the transition table. The patch sets it from
pstate->p_queryEnv, but only when the cursor's query reads an ephemeral
named relation. Setting it always would also let SPI_finish() close every
cursor declared with EXECUTE inside a function; today those stay open after
the function returns (I checked on an unpatched build), so I kept that
behaviour. With the narrower check, only cursors over transition tables
change, and they are closed when the trigger function exits, like the SPI
path.
Tested:
- the case from the report, plus the OPEN ... FOR control
- DECLARE ... WITH HOLD over the transition table, and a cursor with WHERE
false: both work, and neither is left in pg_cursors after the function
returns
- an ordinary cursor declared with EXECUTE in the same trigger still stays
open, as before
- a new regression test in triggers.sql; the regress and plpgsql suites
pass, and the test fails without the fix with the reported XX000 error
Regards,
Sai Pranav
On Fri, Nov 20, 2026 10:25 PM, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:
> 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.
>
>
>
>
>
>
>
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Let-DECLARE-CURSOR-read-transition-tables-inside-.patch | text/x-patch | 5.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ayush Tiwari | 2026-11-20 16:58:52 | Re: BUG #19631: currtid2() on a view with GROUP BY ctid crashes with XX000 |
| Previous Message | Tom Lane | 2026-11-20 16:57:16 | Re: BUG #19922: CYCLE/SEARCH with a window in the recursive term: could not find replacement targetlist entry |