Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSyVl-000000000RD-1K4H for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 15:20:50 +0000 Received: from localhost ([127.0.0.1]:53100 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSyVk-00000001q9L-3BSh for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 15:20:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:54450) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSQzO-0000000ETNY-0QkY for pgsql-bugs@lists.postgresql.org; Sun, 15 Nov 2026 03:33:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSQzK-000000016g6-0xSt for pgsql-bugs@lists.postgresql.org; Sun, 15 Nov 2026 03:33:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=Q/buX7yPtV0HoFMyzKIxrx8LXc5BNnXoHVD4scvamN0=; b=Ws7MyH/fF/UJ1K8wzP8qf42EeL /dy1VG5XQA/olSiKxNL5sB7fl5zFwvCPUCOrNqPn56rsvBcyQWu/MwOMNzW1+a5bfYcea08Oi7zyx 4msWBRJleVnCKJc0WgasZwSPHt0R3EEHDfrzSPybPDkDwLTKt296xpGaBGNwrBNgVP8yDY3TIOq8r I2hKPPIAE01YBQDl9cJ2KsyPfUqVn2vxT63nWLVZsDKnOkRQG0obPBvEyAc2HlbTQVNQuTGXYT0v8 utCULMvq7CnCjsKKWTi8um0SfYaiJ3Shbr0RAEEfTcQCXw0JK0iDGlfhQxjeo6V47Q0vFqCRk+MVm I2Yn001Q==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xSQzI-00000002lyL-0tj1 for pgsql-bugs@lists.postgresql.org; Sun, 15 Nov 2026 03:33:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xSQzH-00000006tmu-0pDP for pgsql-bugs@lists.postgresql.org; Sun, 15 Nov 2026 03:33:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19902: DECLARE CURSOR over trigger transition table fails with XX000 To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Sun, 15 Nov 2026 03:32:13 +0000 Message-ID: <19902-3f34fad0a1199e2e@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 **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" =C3=A2=C2=80= =C2=94 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 =3D %', 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 =3D 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 :=3D '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 =C3=A2=C2=80= =C2=94 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()` =C3=A2=C2=86=C2=92 `PortalDefineQuery()= ` =C3=A2=C2=86=C2=92 `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 =3D _SPI_current->queryEnv` =C3=A2=C2=80=C2=94 that line was added by `5ebeb579= b9b` (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 =3D=3D NULL`, `DropQueryEnvPortals()` (portalmem.c) do= es 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` =C3=A2= =C2=80=C2=94 contrary to spi.sgml's "all cursors are automatically closed when the function exits". **Expected behavior:** `PerformCursorOpen()` should set `portal->queryEnv = =3D 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.