On Fri Oct 2, 2026 at 12:54 PM -03, Etsuro Fujita wrote:
> On Fri, Oct 2, 2026 at 2:32 AM Etsuro Fujita <[email protected]> wrote:
>> On Fri, Oct 2, 2026 at 1:47 AM Nikolay Samokhvalov <[email protected]> wrote:
>> > 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.
>>
>> Will look into the patch.
>
> I think the patch assumes that create_cursor() is called at the same
> transaction nesting depth as the local cursor, but that doesn't always
> hold; for eg, the case I showed yesterday, that doesn't hold, so it
> still fails. So it's a partial solution as proposed. Rather than
> complicating the code, I'd like to propose to fix this by just
> disallowing first fetching of a cursor within a deeper subtransaction
> than it was created in. Here is an updated version for that. This is
> an existing issue, so I split it into two:
>
> * v2-0001-Fix-open-cursor-handling.patch
> This addresses the existing issue by disallowing the fetching (and the
> issue #1 reported by Fujii-san as a side effect).
>
> * v2-0002-Fix-xact-prop-issues.patch
> This addresses the remaining issues #2, #3 and #5 reported by
> Fujii-san (#4 is not a bug). I will add test cases next.
>
Thanks for the v2 patches! I tested them and the hot standby issue is
fixed by 0002, and the deferred trigger case works as expected. I found
two problems with 0001, 0002 looks good to me.
1: the check in 0001 doesn't cover sibling savepoints
Since created_at only keeps the nesting depth, a cursor declared in one
savepoint and first fetched in a sibling savepoint at the same depth
passes the check:
begin;
savepoint s1;
declare c cursor for select * from ft;
release s1;
savepoint s2;
fetch 1 from c;
rollback to s2;
fetch all from c;
commit;
ERROR: 34000: cursor "c1" does not exist
CONTEXT: remote SQL command: CLOSE c1
2: it rejects cases that work on master
A PL/pgSQL refcursor that is opened outside an exception block and first
fetched inside it works on master, but fails with 0001 with the new
error. It also fails if the exception handler swallows errors: the new
error is hidden inside the block, the portal is left failed, and the
later fetch outside the block fails with a confusing 'portal "<unnamed
portal 2>" cannot be run'. On master this works because no remote
savepoint exists yet, so the remote cursor is created at remote level 1.
If we keep this restriction, I'm wondering if needs a documentation note
and a release note, what do you think?
I'm attaching a prototype 0003 on top of 0001 and 0002 that fixes both
issues that I've mention. The idea is that the real problem is not the
local nesting depth, but whether the remote savepoint depth at the time
of the DECLARE is deeper than the level where the local cursor lives,
since rolling back a remote savepoint at or below that depth destroys
the remote cursor. So 0003 declares the remote cursor before
synchronizing the remote savepoint level, and raises the error only if
the current remote depth is deeper than the level of the local cursor.
To know that level it records the subtransaction ID and the nesting
level when the scan is created; if that subtransaction was already
released, it conservatively assumes the cursor lives at the top level.
With this, the cases above work, and your earlier example, where another
scan advances the remote savepoint level before the first fetch, still
errors. The postgres_fdw tests pass, and I adjusted the cursor tests of
0001 accordingly.
It's a prototype, I haven't tested the async path beyond the existing
tests. What do you think?
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
diff --git a/contrib/postgres_fdw/connection.c
b/contrib/postgres_fdw/connection.c
index b5d4cf3dccc..652fa4a943d 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -397,7 +397,8 @@ make_new_connection(ConnCacheEntry *entry, UserMapping
*user)
entry->mapping_hashvalue =
GetSysCacheHashValue1(USERMAPPINGOID,
ObjectIdGetDatum(user->umid));
- memset(&entry->state, 0, sizeof(entry->state));
+ entry->state.pendingAreq = NULL;
+ entry->state.entry = entry;
/*
* Determine whether to keep the connection that we're about to make
here
@@ -1061,6 +1062,20 @@ GetPrepStmtNumber(PGconn *conn)
return ++prep_stmt_number;
}
+/*
+ * Exported version of begin_remote_xact().
+ *
+ * This can be called for connections on which begin_remote_xact() has started
+ * a remote transaction.
+ */
+void
+pgfdw_begin_remote_xact(ConnCacheEntry *entry)
+{
+ Assert(entry);
+ Assert(entry->xact_depth > 0);
+ begin_remote_xact(entry);
+}
+
/*
* Submit a query and wait for the result.
*
@@ -1077,6 +1092,13 @@ pgfdw_exec_query(PGconn *conn, const char *query,
PgFdwConnState *state)
if (state && state->pendingAreq)
process_pending_request(state->pendingAreq);
+ /*
+ * Second, synchronize the local/remote transactions. Note that we need
+ * to do this because this function can be called from open cursors.
+ */
+ if (state)
+ pgfdw_begin_remote_xact(state->entry);
+
if (!PQsendQuery(conn, query))
return NULL;
return pgfdw_get_result(conn);
@@ -1929,10 +1951,10 @@ pgfdw_abort_cleanup(ConnCacheEntry *entry, bool
toplevel)
* If pendingAreq of the per-connection state is not NULL, it means that
* an asynchronous fetch begun by fetch_more_data_begin() was not done
* successfully and thus the per-connection state was not reset in
- * fetch_more_data(); in that case reset the per-connection state here.
+ * fetch_more_data(); in that case reset pendingAreq here.
*/
if (entry->state.pendingAreq)
- memset(&entry->state, 0, sizeof(entry->state));
+ entry->state.pendingAreq = NULL;
/* Disarm changing_xact_state if it all worked */
entry->changing_xact_state = false;
@@ -2221,9 +2243,9 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List
*cancel_requested,
entry->have_error = false;
}
- /* Reset the per-connection state if needed */
+ /* Reset pendingAreq here if any */
if (entry->state.pendingAreq)
- memset(&entry->state, 0, sizeof(entry->state));
+ entry->state.pendingAreq = NULL;
/* We're done with this entry; unset the changing_xact_state
flag */
entry->changing_xact_state = false;
@@ -2266,9 +2288,9 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List
*cancel_requested,
entry->have_prep_stmt = false;
entry->have_error = false;
- /* Reset the per-connection state if needed */
+ /* Reset pendingAreq here if any */
if (entry->state.pendingAreq)
- memset(&entry->state, 0, sizeof(entry->state));
+ entry->state.pendingAreq = NULL;
/* We're done with this entry; unset the changing_xact_state
flag */
entry->changing_xact_state = false;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 739f43af7bb..3aab56b0642 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -5332,6 +5332,8 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
+-- + cursors opened before a savepoint are disallowed to be first
+-- fetched within it
-- ===================================================================
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
@@ -5371,6 +5373,12 @@ SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
(1 row)
COMMIT;
+BEGIN;
+DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+SAVEPOINT s;
+FETCH c;
+ERROR: cannot perform the first fetch of a cursor within a deeper
subtransaction than it was created in
+ABORT;
-- ===================================================================
-- test handling of collations
-- ===================================================================
diff --git a/contrib/postgres_fdw/postgres_fdw.c
b/contrib/postgres_fdw/postgres_fdw.c
index 2bcff4b26b4..b1d75487c67 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "access/table.h"
+#include "access/xact.h"
#include "catalog/pg_opfamily.h"
#include "commands/defrem.h"
#include "commands/explain_format.h"
@@ -189,6 +190,7 @@ typedef struct PgFdwScanState
FmgrInfo *param_flinfo; /* output conversion functions for them
*/
List *param_exprs; /* executable expressions for param
values */
const char **param_values; /* textual values of query parameters */
+ int created_at; /* xact depth at which
the scan was created */
/* for storing result tuples */
HeapTuple *tuples; /* array of currently-retrieved
tuples */
@@ -1763,6 +1765,9 @@ postgresBeginForeignScan(ForeignScanState *node, int
eflags)
fsstate->cursor_number = GetCursorNumber(fsstate->conn);
fsstate->cursor_exists = false;
+ /* Get the current local transaction's nesting depth */
+ fsstate->created_at = GetCurrentTransactionNestLevel();
+
/* Get private info created by planner functions. */
fsstate->query = strVal(list_nth(fsplan->fdw_private,
FdwScanPrivateSelectSql));
@@ -4054,10 +4059,21 @@ create_cursor(ForeignScanState *node)
StringInfoData buf;
PGresult *res;
+ if (fsstate->created_at < GetCurrentTransactionNestLevel())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot perform the first fetch of a
cursor within a deeper subtransaction than it was created in")));
+
/* First, process a pending asynchronous request, if any. */
if (fsstate->conn_state->pendingAreq)
process_pending_request(fsstate->conn_state->pendingAreq);
+ /*
+ * Second, synchronize the local/remote transactions. Note that we need
+ * to do this because this function can be called from open cursors.
+ */
+ 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
@@ -4147,7 +4163,7 @@ fetch_more_data(ForeignScanState *node)
if (PQresultStatus(res) != PGRES_TUPLES_OK)
pgfdw_report_error(res, conn, fsstate->query);
- /* Reset per-connection state */
+ /* Reset the pending asynchronous request */
fsstate->conn_state->pendingAreq = NULL;
}
else
@@ -4394,6 +4410,9 @@ create_foreign_modify(EState *estate,
* result if any. (This is the shared guts of
postgresExecForeignInsert,
* postgresExecForeignBatchInsert, postgresExecForeignUpdate, and
* postgresExecForeignDelete.)
+ *
+ * Note: this function is never called from open cursors, thus no need to
+ * synchronize the local/remote transactions.
*/
static TupleTableSlot **
execute_foreign_modify(EState *estate,
@@ -4832,6 +4851,9 @@ rebuild_fdw_scan_tlist(ForeignScan *fscan, List *tlist)
/*
* Execute a direct UPDATE/DELETE statement.
+ *
+ * Note: this function is never called from open cursors, thus no need to
+ * synchronize the local/remote transactions.
*/
static void
execute_dml_stmt(ForeignScanState *node)
@@ -8840,9 +8862,14 @@ fetch_more_data_begin(AsyncRequest *areq)
Assert(!fsstate->conn_state->pendingAreq);
- /* Create the cursor synchronously. */
+ /*
+ * Create the cursor synchronously if not already done. Otherwise,
+ * synchronize the local/remote transactions before the data fetch.
+ */
if (!fsstate->cursor_exists)
create_cursor(node);
+ else
+ 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",
diff --git a/contrib/postgres_fdw/postgres_fdw.h
b/contrib/postgres_fdw/postgres_fdw.h
index da7da1c2ea9..b9b460141f9 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -146,7 +146,8 @@ typedef struct PgFdwRelationInfo
*/
typedef struct PgFdwConnState
{
- AsyncRequest *pendingAreq; /* pending async request */
+ AsyncRequest *pendingAreq; /* pending async request */
+ struct ConnCacheEntry *entry; /* link to containing ConnCacheEntry */
} PgFdwConnState;
/*
@@ -173,6 +174,7 @@ extern void ReleaseConnection(PGconn *conn);
extern unsigned int GetCursorNumber(PGconn *conn);
extern unsigned int GetPrepStmtNumber(PGconn *conn);
extern void do_sql_command(PGconn *conn, const char *sql);
+extern void pgfdw_begin_remote_xact(struct ConnCacheEntry *entry);
extern PGresult *pgfdw_get_result(PGconn *conn);
extern PGresult *pgfdw_exec_query(PGconn *conn, const char *query,
PgFdwConnState *state);
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index f1ca3204382..9c271953206 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1635,6 +1635,8 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
+-- + cursors opened before a savepoint are disallowed to be first
+-- fetched within it
-- ===================================================================
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
@@ -1650,6 +1652,12 @@ FETCH c;
SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
COMMIT;
+BEGIN;
+DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+SAVEPOINT s;
+FETCH c;
+ABORT;
+
-- ===================================================================
-- test handling of collations
-- ===================================================================
diff --git a/contrib/postgres_fdw/connection.c
b/contrib/postgres_fdw/connection.c
index 652fa4a943d..77aec0fd9eb 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -112,6 +112,20 @@ static uint32 pgfdw_we_get_result = 0;
*/
#define RETRY_CANCEL_TIMEOUT 1000
+/*
+ * Macro for constructing commit command to be sent
+ *
+ * We synchronize the read/write mode before committing remote transactions
+ * so deferred triggers on remote servers can run in the right mode.
+ */
+#define CONSTRUCT_COMMIT_COMMAND(sql, entry) \
+ do { \
+ if ((read_only_level > 0) && !(entry)->xact_read_only) \
+ strcpy((sql), "SET TRANSACTION READ ONLY; COMMIT
TRANSACTION"); \
+ else \
+ strcpy((sql), "COMMIT TRANSACTION"); \
+ } while(0)
+
/* Macro for constructing abort command to be sent */
#define CONSTRUCT_ABORT_COMMAND(sql, entry, toplevel) \
do { \
@@ -942,7 +956,7 @@ begin_remote_xact(ConnCacheEntry *entry)
appendStringInfoString(&sql, "REPEATABLE READ");
if (ro)
appendStringInfoString(&sql, " READ ONLY");
- if (XactDeferrable)
+ if (XactDeferrable && PQserverVersion(entry->conn) >= 90100)
appendStringInfoString(&sql, " DEFERRABLE");
entry->changing_xact_state = true;
do_sql_command(entry->conn, sql.data);
@@ -972,7 +986,7 @@ begin_remote_xact(ConnCacheEntry *entry)
if (entry->xact_depth == read_only_level)
{
entry->changing_xact_state = true;
- do_sql_command(entry->conn, "SET transaction_read_only
= on");
+ do_sql_command(entry->conn, "SET TRANSACTION READ
ONLY");
entry->xact_read_only = true;
entry->changing_xact_state = false;
}
@@ -1005,7 +1019,7 @@ begin_remote_xact(ConnCacheEntry *entry)
initStringInfo(&sql);
appendStringInfo(&sql, "SAVEPOINT s%d", entry->xact_depth + 1);
if (ro)
- appendStringInfoString(&sql, "; SET
transaction_read_only = on");
+ appendStringInfoString(&sql, "; SET TRANSACTION READ
ONLY");
entry->changing_xact_state = true;
do_sql_command(entry->conn, sql.data);
entry->xact_depth++;
@@ -1206,6 +1220,24 @@ pgfdw_xact_callback(XactEvent event, void *arg)
if (!xact_got_connection)
return;
+ /*
+ * If we are called for pre-commit cleanup, ensure read_only_level is
set
+ * for later processing. Note that we need to do this because the local
+ * transaction may have become read-only since the last remote
operation.
+ */
+ if (event == XACT_EVENT_PARALLEL_PRE_COMMIT ||
+ event == XACT_EVENT_PRE_COMMIT)
+ {
+ if (XactReadOnly)
+ {
+ if (read_only_level == 0)
+ read_only_level = 1;
+ Assert(read_only_level == 1);
+ }
+ else
+ Assert(read_only_level == 0);
+ }
+
/*
* Scan all connection cache entries to find open remote transactions,
and
* close them.
@@ -1222,6 +1254,8 @@ pgfdw_xact_callback(XactEvent event, void *arg)
/* If it has an open remote transaction, try to close it */
if (entry->xact_depth > 0)
{
+ char sql[100];
+
elog(DEBUG3, "closing remote transaction on connection
%p",
entry->conn);
@@ -1237,14 +1271,17 @@ pgfdw_xact_callback(XactEvent event, void *arg)
pgfdw_reject_incomplete_xact_state_change(entry);
/* Commit all remote transactions
during pre-commit */
+ CONSTRUCT_COMMIT_COMMAND(sql, entry);
entry->changing_xact_state = true;
if (entry->parallel_commit)
{
-
do_sql_command_begin(entry->conn, "COMMIT TRANSACTION");
+
do_sql_command_begin(entry->conn, sql);
pending_entries =
lappend(pending_entries, entry);
continue;
}
- do_sql_command(entry->conn, "COMMIT
TRANSACTION");
+ do_sql_command(entry->conn, sql);
+ if ((read_only_level > 0) &&
!entry->xact_read_only)
+ entry->xact_read_only = true;
entry->changing_xact_state = false;
/*
@@ -2041,6 +2078,8 @@ pgfdw_finish_pre_commit_cleanup(List *pending_entries)
*/
foreach(lc, pending_entries)
{
+ char sql[100];
+
entry = (ConnCacheEntry *) lfirst(lc);
Assert(entry->changing_xact_state);
@@ -2049,7 +2088,10 @@ pgfdw_finish_pre_commit_cleanup(List *pending_entries)
* We might already have received the result on the socket, so
pass
* consume_input=true to try to consume it first
*/
- do_sql_command_end(entry->conn, "COMMIT TRANSACTION", true);
+ CONSTRUCT_COMMIT_COMMAND(sql, entry);
+ do_sql_command_end(entry->conn, sql, true);
+ if ((read_only_level > 0) && !(entry)->xact_read_only)
+ entry->xact_read_only = true;
entry->changing_xact_state = false;
/* Do a DEALLOCATE ALL in parallel if needed */
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index fe4e6478e28..01577d8d69b 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -1148,20 +1148,17 @@ CREATE SUBSCRIPTION my_subscription SERVER
subscription_server PUBLICATION testp
</para>
<para>
- The remote transaction is opened in the same read/write mode as the local
- transaction: if the local transaction is <literal>READ ONLY</literal>,
- the remote transaction is opened in <literal>READ ONLY</literal> mode,
- otherwise it is opened in <literal>READ WRITE</literal> mode.
- (This rule is also applied to remote and local subtransactions.)
+ Local <literal>READ ONLY</literal> transactions propagate their read-only
+ mode to remote sessions.
+ (This rule is also applied to local subtransactions.)
Note that this does not prevent login triggers executed on the remote
server from writing.
</para>
<para>
- The remote transaction is also opened in the same deferrable mode as the
- local transaction: if the local transaction is
<literal>DEFERRABLE</literal>,
- the remote transaction is opened in <literal>DEFERRABLE</literal> mode,
- otherwise it is opened in <literal>NOT DEFERRABLE</literal> mode.
+ Also, local <literal>DEFERRABLE</literal> transactions propagate their
+ deferrable mode to remote sessions.
+ (This rule is only applied to remote servers 9.1 and newer.)
</para>
<para>
diff -ru a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
--- a/contrib/postgres_fdw/connection.c 2026-10-03 05:55:27
+++ b/contrib/postgres_fdw/connection.c 2026-10-03 05:55:27
@@ -1091,6 +1091,16 @@
}
/*
+ * Return the nesting depth of the remote (sub)transaction currently open on
+ * the connection (0 if none).
+ */
+int
+pgfdw_remote_xact_depth(ConnCacheEntry *entry)
+{
+ return entry->xact_depth;
+}
+
+/*
* Submit a query and wait for the result.
*
* Since we don't use non-blocking mode, this can't process interrupts while
diff -ru a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
--- a/contrib/postgres_fdw/expected/postgres_fdw.out 2026-10-03 05:55:27
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out 2026-10-03 05:55:27
@@ -5373,9 +5373,50 @@
(1 row)
COMMIT;
+-- first fetch within a savepoint is fine if remote savepoint level is
+-- not advanced beyond the one the cursor was created in
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
SAVEPOINT s;
+FETCH c;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1
| 1 | foo
+(1 row)
+
+ROLLBACK TO s;
+FETCH c;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2
| 2 | foo
+(1 row)
+
+COMMIT;
+-- ... but not otherwise
+BEGIN;
+DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+SAVEPOINT s;
+SELECT count(*) FROM ft1;
+ count
+-------
+ 1000
+(1 row)
+
+FETCH c;
+ERROR: cannot perform the first fetch of a cursor within a deeper
subtransaction than it was created in
+ABORT;
+-- a cursor created in a released savepoint is handed to its parent
+BEGIN;
+SAVEPOINT s1;
+DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+RELEASE s1;
+SAVEPOINT s2;
+SELECT count(*) FROM ft1;
+ count
+-------
+ 1000
+(1 row)
+
FETCH c;
ERROR: cannot perform the first fetch of a cursor within a deeper
subtransaction than it was created in
ABORT;
diff -ru a/contrib/postgres_fdw/postgres_fdw.c
b/contrib/postgres_fdw/postgres_fdw.c
--- a/contrib/postgres_fdw/postgres_fdw.c 2026-10-03 05:55:27
+++ b/contrib/postgres_fdw/postgres_fdw.c 2026-10-03 05:55:27
@@ -190,7 +190,8 @@
FmgrInfo *param_flinfo; /* output conversion functions for them
*/
List *param_exprs; /* executable expressions for param
values */
const char **param_values; /* textual values of query parameters */
- int created_at; /* xact depth at which
the scan was created */
+ SubTransactionId created_subid; /* subxact in which the scan was
created */
+ int created_level; /* its nesting depth at that
time */
/* for storing result tuples */
HeapTuple *tuples; /* array of currently-retrieved
tuples */
@@ -1765,8 +1766,9 @@
fsstate->cursor_number = GetCursorNumber(fsstate->conn);
fsstate->cursor_exists = false;
- /* Get the current local transaction's nesting depth */
- fsstate->created_at = GetCurrentTransactionNestLevel();
+ /* Remember the local (sub)transaction that the scan is created in */
+ fsstate->created_subid = GetCurrentSubTransactionId();
+ fsstate->created_level = GetCurrentTransactionNestLevel();
/* Get private info created by planner functions. */
fsstate->query = strVal(list_nth(fsplan->fdw_private,
@@ -4059,22 +4061,34 @@
StringInfoData buf;
PGresult *res;
- if (fsstate->created_at < GetCurrentTransactionNestLevel())
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot perform the first fetch of a
cursor within a deeper subtransaction than it was created in")));
+ /*
+ * The remote cursor is declared at the current remote (sub)transaction
+ * depth, and rolling back a remote savepoint at or below that depth
+ * destroys it. That is only safe if every local savepoint whose
rollback
+ * keeps the local cursor alive is deeper than that. The local cursor
+ * lives at the depth of the (sub)transaction that created it, if that
is
+ * still open; if it has been released, the cursor has been handed to
some
+ * enclosing level, which we conservatively assume is the top level.
+ */
+ {
+ int cursor_level;
+ if (SubTransactionIsActive(fsstate->created_subid))
+ cursor_level = fsstate->created_level;
+ else
+ cursor_level = 1;
+
+ if (pgfdw_remote_xact_depth(fsstate->conn_state->entry) >
cursor_level)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot perform the first fetch
of a cursor within a deeper subtransaction than it was created in")));
+ }
+
/* First, process a pending asynchronous request, if any. */
if (fsstate->conn_state->pendingAreq)
process_pending_request(fsstate->conn_state->pendingAreq);
/*
- * Second, synchronize the local/remote transactions. Note that we need
- * to do this because this function can be called from open cursors.
- */
- 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
* memory leak over repeated scans.
@@ -4116,6 +4130,15 @@
if (PQresultStatus(res) != PGRES_COMMAND_OK)
pgfdw_report_error(res, conn, fsstate->query);
PQclear(res);
+
+ /*
+ * Now synchronize the local/remote transactions. We do this after
+ * declaring the cursor, not before, so that the remote cursor is not
+ * created inside a remote savepoint that the local cursor doesn't
+ * belong to. (We need to synchronize here because this function can be
+ * called from open cursors.)
+ */
+ pgfdw_begin_remote_xact(fsstate->conn_state->entry);
/* Mark the cursor as created, and show no tuples have been retrieved */
fsstate->cursor_exists = true;
diff -ru a/contrib/postgres_fdw/postgres_fdw.h
b/contrib/postgres_fdw/postgres_fdw.h
--- a/contrib/postgres_fdw/postgres_fdw.h 2026-10-03 05:55:27
+++ b/contrib/postgres_fdw/postgres_fdw.h 2026-10-03 05:55:27
@@ -175,6 +175,7 @@
extern unsigned int GetPrepStmtNumber(PGconn *conn);
extern void do_sql_command(PGconn *conn, const char *sql);
extern void pgfdw_begin_remote_xact(struct ConnCacheEntry *entry);
+extern int pgfdw_remote_xact_depth(struct ConnCacheEntry *entry);
extern PGresult *pgfdw_get_result(PGconn *conn);
extern PGresult *pgfdw_exec_query(PGconn *conn, const char *query,
PgFdwConnState *state);
diff -ru a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql 2026-10-03 05:55:27
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql 2026-10-03 05:55:27
@@ -1652,9 +1652,31 @@
SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
COMMIT;
+-- first fetch within a savepoint is fine if remote savepoint level is
+-- not advanced beyond the one the cursor was created in
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
SAVEPOINT s;
+FETCH c;
+ROLLBACK TO s;
+FETCH c;
+COMMIT;
+
+-- ... but not otherwise
+BEGIN;
+DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+SAVEPOINT s;
+SELECT count(*) FROM ft1;
+FETCH c;
+ABORT;
+
+-- a cursor created in a released savepoint is handed to its parent
+BEGIN;
+SAVEPOINT s1;
+DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+RELEASE s1;
+SAVEPOINT s2;
+SELECT count(*) FROM ft1;
FETCH c;
ABORT;
