On Wed, Sep 30, 2026 at 11:27 AM Matheus Alcantara <[email protected]> wrote: > With the patch, the new begin_remote_xact() call in create_cursor opens a > remote SAVEPOINT s2 and creates the cursor inside it. ROLLBACK TO s1 then > destroys the remote cursor while the local side still thinks it exists.
Thanks, Matheus. My AI harness for testing reproduced this on REL_19_STABLE at 9e73b209 with Etsuro's v1 patch and prepared the attached incremental patch. It declares the remote cursor before advancing the remote savepoint level, then synchronizes the transaction mode before FETCH. The second FETCH fails with 34000 on v1 and succeeds with this patch. The postgres_fdw suite passes (4/4). This does not address the hot standby case, which needs a separate fix. Nik
From e0e670e4e95d04a8a37e03c86c363bc75e56ff6b Mon Sep 17 00:00:00 2001 From: Nikolay Samokhvalov <[email protected]> Date: Wed, 30 Sep 2026 16:36:41 -0700 Subject: [PATCH] postgres_fdw: Preserve outer cursors across subtransaction rollback --- .../postgres_fdw/expected/postgres_fdw.out | 20 +++++++++++++++ contrib/postgres_fdw/postgres_fdw.c | 25 ++++++++----------- contrib/postgres_fdw/sql/postgres_fdw.sql | 11 ++++++++ 3 files changed, 41 insertions(+), 15 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 3a17e02..953b01c 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -13375,3 +13375,23 @@ RESET client_min_messages; DROP FUNCTION wait_for_backend_termination(int); DROP FOREIGN TABLE remote_backend_pid; DROP VIEW my_backend_pid; +-- A cursor opened before a savepoint can first be fetched within it. +ALTER FOREIGN TABLE ft1 OPTIONS (ADD fetch_size '1'); +BEGIN; +DECLARE c CURSOR FOR SELECT c1 FROM ft1 ORDER BY c1; +SAVEPOINT s; +FETCH c; + c1 +---- + 1 +(1 row) + +ROLLBACK TO SAVEPOINT s; +FETCH c; + c1 +---- + 3 +(1 row) + +COMMIT; +ALTER FOREIGN TABLE ft1 OPTIONS (DROP fetch_size); diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 4e0f096..209a422 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -3826,13 +3826,6 @@ create_cursor(ForeignScanState *node) if (fsstate->conn_state->pendingAreq) process_pending_request(fsstate->conn_state->pendingAreq); - /* - * Ensure the local and remote (sub)transactions are synchronized. Note - * that we need to do this because this function can be called from open - * cursors, bypassing begin_remote_xact(). - */ - pgfdw_begin_remote_xact(fsstate->conn_state->entry); - /* * Construct array of query parameter values in text format. We do the * conversions in the short-lived per-tuple context, so as not to cause a @@ -3852,7 +3845,10 @@ create_cursor(ForeignScanState *node) MemoryContextSwitchTo(oldcontext); } - /* Construct the DECLARE CURSOR command */ + /* + * Declare the cursor before advancing the remote savepoint level, so a + * cursor opened by an outer local transaction survives rollback here. + */ initStringInfo(&buf); appendStringInfo(&buf, "DECLARE c%u CURSOR FOR\n%s", fsstate->cursor_number, fsstate->query); @@ -8324,17 +8320,16 @@ fetch_more_data_begin(AsyncRequest *areq) Assert(!fsstate->conn_state->pendingAreq); - /* - * Ensure the local and remote (sub)transactions are synchronized. Note - * that we need to do this because this function can be called from open - * cursors, bypassing begin_remote_xact(). - */ - pgfdw_begin_remote_xact(fsstate->conn_state->entry); - /* Create the cursor synchronously. */ if (!fsstate->cursor_exists) create_cursor(node); + /* + * Synchronize the remote (sub)transaction after declaring the cursor, so + * an outer-level local cursor survives rollback of this subtransaction. + */ + pgfdw_begin_remote_xact(fsstate->conn_state->entry); + /* We will send this query, but not wait for the response. */ snprintf(sql, sizeof(sql), "FETCH %d FROM c%u", fsstate->fetch_size, fsstate->cursor_number); diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 4b19c50..4cbb97b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -4814,3 +4814,14 @@ RESET client_min_messages; DROP FUNCTION wait_for_backend_termination(int); DROP FOREIGN TABLE remote_backend_pid; DROP VIEW my_backend_pid; + +-- A cursor opened before a savepoint can first be fetched within it. +ALTER FOREIGN TABLE ft1 OPTIONS (ADD fetch_size '1'); +BEGIN; +DECLARE c CURSOR FOR SELECT c1 FROM ft1 ORDER BY c1; +SAVEPOINT s; +FETCH c; +ROLLBACK TO SAVEPOINT s; +FETCH c; +COMMIT; +ALTER FOREIGN TABLE ft1 OPTIONS (DROP fetch_size);
