On Thu, Sep 10, 2026 at 9:34 AM Fujii Masao <[email protected]> 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
postgres_fdw-Fix-corner-cases-in-xact-mode-inh-v1.patch
Description: Binary data
