Re: Options to control remote transactions’ access/deferrable modes in postgres_fdw

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Etsuro Fujita <[email protected]>
To: Fujii Masao <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject:  Re: Options to control remote transactions’ access/deferrable modes in postgres_fdw
Date: Sun, 8 Mar 2026 03:37:44 +0900
Message-ID: <CAPmGK17-a8U=OdATsG5b2itLMbgEv3sOUP52NmTBGzS2TZjxjg@mail.gmail.com> (raw)
In-Reply-To: <CAHGQGwHjeZNppb4N7oG4vdPT1i2RY+=YRA=NUwhwYgpsjRyxfA@mail.gmail.com>
References: <CAPmGK16n_hcUUWuOdmeUS+w4Q6dZvTEDHb=OP=5JBzo-M3QmpQ@mail.gmail.com>
	<CAExHW5vOH-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com>
	<[email protected]>
	<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>

On Thu, Mar 5, 2026 at 12:11 PM Fujii Masao <[email protected]> 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 transaction
> starting in read-write mode while the corresponding local transaction on
> the standby is read-only).
>
> On the other hand, this change would force any remote transaction initiated 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 proposal,
> 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 = '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 = 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.

Anyway, I would like to know what other people think.

Best regards,
Etsuro Fujita

view thread (20+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re:  Re: Options to control remote transactions’ access/deferrable modes in postgres_fdw
  In-Reply-To: <CAPmGK17-a8U=OdATsG5b2itLMbgEv3sOUP52NmTBGzS2TZjxjg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox