postgres_fdw: transaction mode inheritance corner cases

Hi,

Commit de28140ded8 introduced transaction mode inheritance in
postgres_fdw, but I found several potential issues with it.

We should add this as PostgreSQL 19 Open Item?

(1) READ ONLY does not take effect for an existing cursor

DECLARE initializes the foreign scan and opens the remote transaction.
FETCH reuses that connection without calling begin_remote_xact(), so
a subsequent mode change is not propagated to the remote server.

In the following example, the query executed by FETCH runs in READ WRITE
mode on the remote server even though the local transaction is READ ONLY:

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft;
SET TRANSACTION READ ONLY;
FETCH ALL FROM c;
COMMIT;

(2) READ WRITE and NOT DEFERRABLE are not always inherited

begin_remote_xact() adds READ ONLY and DEFERRABLE when applicable,
but does not explicitly specify READ WRITE or NOT DEFERRABLE. Those
modes therefore depend on the defaults on the remote server.

This seems to conflict with the postgres_fdw docs:

The remote transaction is opened in the same read/write mode as the
local transaction: if the local transaction is READ ONLY, the remote
transaction is opened in READ ONLY mode, otherwise it is opened in READ
WRITE mode.

(3) DEFERRABLE breaks queries against PostgreSQL 9.0 and older

When the remote server is PostgreSQL 9.0 or older, the remote
START TRANSACTION can fail with a syntax error at DEFERRABLE, since
that option is not supported there. Since the docs still says that
read-only access is supported back to PostgreSQL 8.1, this case
should be handled.

(4) A loopback query can wait indefinitely after switching to READ ONLY

BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE DEFERRABLE;
SELECT * FROM t;
SET TRANSACTION READ ONLY;
SELECT * FROM ft;
ROLLBACK;

In this example, the foreign SELECT waits indefinitely.

The local transaction remains READ WRITE in SSI after taking its first
snapshot. The remote READ ONLY DEFERRABLE transaction waits for the
local transaction to finish before obtaining a safe snapshot, while the
local transaction waits for the remote query.

I'm not sure whether this is something we should fix or just consider an
operational mistake, but I wanted to share the case.

(5) Deferred remote triggers can write after switching to READ ONLY

pgfdw_xact_callback() sends COMMIT without synchronizing the
read-only mode, so a deferred trigger on the remote server can still run
in READ WRITE mode.

BEGIN;
INSERT INTO ft VALUES (...);
SET TRANSACTION READ ONLY;
COMMIT;

Regards,

--
Fujii Masao