MIME-Version: 1.0
References: 
 <CAPmGK16n_hcUUWuOdmeUS+w4Q6dZvTEDHb=OP=5JBzo-M3QmpQ@mail.gmail.com>
 <CAExHW5vOH-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com>
 <3660951.1741016970@sss.pgh.pa.us>
 <CAPmGK14Btk0odkH6vwBhBGjCexmmWcM_D3DG0pJtObj8k_Unag@mail.gmail.com>
 <CAExHW5vVwZdvW1Dfy3oJXVDBffJUvAwhaCw3HCSC38B1ie_H1A@mail.gmail.com>
 <CAPmGK16pn+k0tLNR8NvZdtiADpnjZPYK-HuaxwOmzHzUgTE7_A@mail.gmail.com>
 <CAPmGK14Zn9NwKPPmBrUyN9ALBJa2nv75-LOVKNBgRN=ehYnKkw@mail.gmail.com>
 <CAPmGK150ucoQy96dUu6nu5iXBxyqrPrpqR2KMZN2CCSrMq0USw@mail.gmail.com>
 <CAPmGK15AQBXTgF_8EK=AY-Q6OLmhTuQSTWittLSVrw9Rvec25g@mail.gmail.com>
 <CAPmGK14fT9_hdC40s7MWZX+-k87AvyusG0qjd6zvngEgumdNgw@mail.gmail.com>
 <CAPmGK14RxMWYmNEHcjn04ZuvqxZrbasPT+zMNdd3T2V3R9Rz2A@mail.gmail.com>
 <CAPmGK14ZTHRGPprEhzEe2TJxaCcjNVeWw6tue_gqp=9DzqYnMA@mail.gmail.com>
 <CAHGQGwHjeZNppb4N7oG4vdPT1i2RY+=YRA=NUwhwYgpsjRyxfA@mail.gmail.com>
 <CAPmGK17-a8U=OdATsG5b2itLMbgEv3sOUP52NmTBGzS2TZjxjg@mail.gmail.com>
In-Reply-To: 
 <CAPmGK17-a8U=OdATsG5b2itLMbgEv3sOUP52NmTBGzS2TZjxjg@mail.gmail.com>
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Mon, 9 Mar 2026 09:17:21 +0530
Message-ID: 
 <CAExHW5u5KCMvBhy8sbr69AGRxz489=ySqd0irgjF8myB+=b=hg@mail.gmail.com>
Subject: 
 =?UTF-8?Q?Re=3A_Options_to_control_remote_transactions=E2=80=99_access?=
	=?UTF-8?Q?=2Fdeferrable_modes_in_postgres=5Ffdw?=
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Fujii Masao <masao.fujii@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAExHW5u5KCMvBhy8sbr69AGRxz489%3DySqd0irgjF8myB%2B%3Db%3Dhg%40mail.gmail.com>
Precedence: bulk

On Sun, Mar 8, 2026 at 12:07=E2=80=AFAM Etsuro Fujita <etsuro.fujita@gmail.=
com> wrote:
>
> On Thu, Mar 5, 2026 at 12:11=E2=80=AFPM Fujii Masao <masao.fujii@gmail.co=
m> wrote:
> > I haven't yet realized the benefit from this change since I haven't
> > encountered issues caused by the current behavior (i.e., a remote trans=
action
> > starting in read-write mode while the corresponding local transaction o=
n
> > the standby is read-only).
> >
> > On the other hand, this change would force any remote transaction initi=
ated by
> > a standby transaction to start in read-only mode, completely preventing=
 it from
> > modifying data. Because transactions on a standby always start as read-=
only,
> > the remote transaction would also always be read-only under this propos=
al,
> > with no way to make it read-write.
> >
> > I'm concerned that this could break certain use cases without providing
> > a clear benefit.
>
> Thanks for the comments!
>
> The benefit is to make read-only transactions using postgres_fdw
> ensure read-only access.  We discussed this in a Postgres developer
> meetup held at Yokohama in Japan last Friday.  Let me explain again.
> Here is an example I used in that meetup to show the current behavior
> of such transactions:
>
> create server loopback
>     foreign data wrapper postgres_fdw
>     options (dbname 'postgres');
> create user mapping for current_user
>     server loopback;
> create table loct (f1 int, f2 text);
> create foreign table ft (f1 int, f2 text)
>     server loopback
>     options (table_name 'loct');
> insert into ft values (1, 'foo');
> insert into ft values (2, 'bar');
>
> They disallow INSERT/UPDATE/DELETE, which is good:
>
> start transaction read only;
> insert into ft values (3, 'baz');
> ERROR:  cannot execute INSERT in a read-only transaction
>
> start transaction read only;
> update ft set f2 =3D 'xyzzy';
> ERROR:  cannot execute UPDATE in a read-only transaction
>
> start transaction read only;
> delete from ft;
> ERROR:  cannot execute DELETE in a read-only transaction
>
> But if referencing foreign tables mapped to a remote view executing
> functions that modify data at the remote side, they can modify the
> data, which would be surprising:
>
> create function locf() returns setof loct language sql as
>     'update public.loct set f2 =3D f2 || f2 returning *';
> create view locv as select t.* from locf() t;
> create foreign table fv (f1 int, f2 text)
>     server loopback
>     options (table_name 'locv');
>
> start transaction read only;
> select * from fv;
>  f1 |   f2
> ----+--------
>   1 | foofoo
>   2 | barbar
> (2 rows)
>
> The root cause of this is that postgres_fdw opens a remote transaction
> in read-write mode even if the local transaction is read-only, so the
> patch I proposed addresses this by inheriting the read-only property
> from the local transaction.
>
> I didn't think of the use cases where postgres_fdw is used on a
> standby server, so I overlooked the breakage you mentioned above, but
> I got a lot of positive feedback from many participants regarding
> ensuring read-only access by that change.  So I strongly believe the
> patch is the right way to go.  I think it's unfortunate that it causes
> the breakage, though.  I might be missing something, but I think a
> solution for such a use case is to use other DB integration tool like
> dblink.

If the primary doesn't allow modifying data in the foreign table in a
read-only transaction, a standby shouldn't do that either. The users
who are expecting a read-only transaction to protect against any
writes to the foreign data on primary will also expect so on the
standby. If users want to use standby's ability to modify foreign data
for the sake of load balancing, that's a reasonable ask. However, we
need to figure out whether it's common enough to support. That
information is not readily available. I doubt that it's a common
usecase. If this fix breaks such applications, we will come to know
its spread. And such applications can use dblink. Alternately we can
add the option which I and Tom didn't like [1]. But I feel we should
do that only if there are complaints. It's going to be painful to
those users who experience application breakage. To ease that pain we
should highlight this as a compatibility break change in the beta
release notes, giving users a chance to complain during beta cycle so
that we can fix it by GA.

If others know that the current behaviour has a widespread
consumption, and they can provide backing data, adding the option
right away is better.

[1] postgr.es/m/CAExHW5vOH-=3D1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.=
gmail.com

--=20
Best Wishes,
Ashutosh Bapat