Re: postgres_fdw: transaction mode inheritance corner cases

On Thu, Sep 10, 2026 at 9:34 AM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> (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.

Fixed.

> (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.

Fixed.

> (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.

Fixed.

> (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.

This is expected behavior, so I would say it would be that mistake.

> (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.

Fixed.

Attached is a patch for that. I will add test cases for these in the
next version.

Best regards,
Etsuro Fujita