Re: Options to control remote transactions’ access/deferrable modes in postgres_fdw - Mailing list pgsql-hackers

On Sun, Mar 30, 2025 at 7:14 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Thu, Mar 27, 2025 at 1:25 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > On Tue, Mar 25, 2025 at 4:01 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > > In the patch I also fixed a bug; I trusted XactReadOnly to see if the
> > > local transaction is READ ONLY, but I noticed that that is not 100%
> > > correct, because a transaction which started as READ WRITE can show as
> > > READ ONLY later within subtransactions, so I modified the patch so
> > > that postgres_fdw opens remote transactions in READ ONLY mode if the
> > > local transaction has been declared READ ONLY at the top level.
> >
> > Nice catch. postgres_fdw replicates the transaction stack on foreign
> > server. I think we need to replicate it along with the transaction
> > properties. And also we need a test which tests readonly
> > subtransaction behaviour.
>
> Ok, will do.

I noticed that we don’t need to replicate it.  As read-only
subtransactions can’t change to read-write, and a read-only
main-transaction can’t change to read-write after first snapshot,
either (note: begin_remote_xact is called after it), all we need to do
is track the nesting level of the outermost read-only transaction in
the local transaction and control the access mode of remote
transactions based on it so that they have the same access mode as the
local transaction at each subtransaction level, like the attached.
This makes the patch pretty simple.  I added the tests in the patch.

What do you think about that?

Best regards,
Etsuro Fujita