Re: pgsql: postgres_fdw: Inherit the local transaction's access/deferrable

On Wed, Jun 4, 2025 at 1:15 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> I agree this could be considered a fix if the new behavior has been
> clearly explained in the documentation from before or based on
> standards like SQL/MED. But if that's not the case, it seems more
> like a behavior change. In that case, I think it should wait for v19
> and be applied only after reaching consensus. Some systems might
> rely on the previous behavior.
>
> By the way, if a read-only transaction on the local server is meant
> to block all write operations on the remote server, this patch alone
> might not be sufficient, for example, that read-only transaction can
> invoke a login trigger on the remote server and it could still
> perform writes.

This patch 1) modifies postgres_fdw so that it opens remote
transactions in read-only mode if the corresponding local transaction
is read-only, as noted in the documentation, but 2) keeps the existing
behavior of login triggers that they can write even if the invoking
transaction is read-only. So declaring a transaction as read-only on
the local side doesn't mean it blocks all write operations on the
remote side; it still allows login triggers invoked on the remote side
to write. Considering typical use-cases of such triggers, this seems
reasonable to me. I think it might be a good idea to add a note about
it to the documentation, though.

I'd like to re-propose this patch for v19, as mentioned in this thread.

Thanks!

Best regards,
Etsuro Fujita