Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 30 Sep 2026 15:27:10 -0300
Message-Id: <DLSV2T9AX7FC.OL3ZCGYSJ7UP@gmail.com>
Cc: "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: postgres_fdw: transaction mode inheritance corner cases
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Etsuro Fujita" <etsuro.fujita@gmail.com>, "Fujii Masao"
 <masao.fujii@gmail.com>
References: 
 <CAHGQGwE4UMaWGOZq1Z7YUTUSg-CgL4yAzO5YaGwTZe1-6Pjg2g@mail.gmail.com>
 <CAPmGK15biMpA0Qpjpt_3iTP++Wh=789jYF-bRnwpH-RO=RdiFg@mail.gmail.com>
In-Reply-To: 
 <CAPmGK15biMpA0Qpjpt_3iTP++Wh=789jYF-bRnwpH-RO=RdiFg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DLSV2T9AX7FC.OL3ZCGYSJ7UP%40gmail.com>
Precedence: bulk

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 COMM=
IT

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