On Wed Sep 30, 2026 at 7:45 AM -03, Etsuro Fujita wrote: > Attached is a patch for that. I will add test cases for these in the > next version. >
Hi, thanks for the patch! I tested it and I think that I may have found two issues: 1: read-write local transactions can no longer query a hot standby If I create a foreign server pointing to a standby, sending an explicit READ WRITE makes the standby reject the remote START TRANSACTION. A plain SELECT from a foreign table on a standby now fails, including in autocommit, because the local transaction is read-write by default. It works on unpatched master. Repro: -- on the primary (port 5433 is running a standby server) create extension postgres_fdw; create table t(a int); insert into t values (1),(2); create server sb foreign data wrapper postgres_fdw options (dbname 'postgres', port '5433'); create user mapping for current_user server sb; create foreign table fsb(a int) server sb options (table_name 't'); select * from fsb; ERROR: 0A000: cannot set transaction read-write mode during recovery CONTEXT: remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ READ WRITE NOT DEFERRABLE begin read only; select * from fsb; -- works commit; I'm not sure how much common is querying a standby through postgres_fdw, but I've already seen some cases, so I'm wondering if this needs some handling, what do you think? 2: a foreign cursor first fetched in a rolled-back savepoint breaks at COMMIT Repro: begin; declare c cursor for select * from ft; savepoint s1; fetch 1 from c; rollback to s1; fetch all from c; commit; ERROR: 34000: cursor "c1" does not exist CONTEXT: remote SQL command: CLOSE c1 This also works on unpatched master. There, the remote cursor is created lazily at the first FETCH, without a remote savepoint, so it lives at remote level 1 and survives ROLLBACK TO s1. 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. If the first FETCH happens before the savepoint, the same script works with the patch. I think the remote cursor needs to be created at the level where the scan started, or the remote/local cursor state needs to be reconciled some other way. Also, I didn't tested this case but on execute_foreign_modify and direct-modify results are read without going through begin_remote_xact(). So I'm wondering if a volatile function that runs SET TRANSACTION READ ONLY in the middle of a single INSERT ... SELECT f() could bypass the sync. -- Matheus Alcantara EDB: https://www.enterprisedb.com
