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

From: Etsuro Fujita <[email-masked]>
Date: Sun, 2 Mar 2025 12:44:27 +0100

Hi,

postgres_fdw opens remote transactions in read/write mode in a local
transaction even if the local transaction is read-only.  I noticed
that this leads to surprising behavior like this:

CREATE TABLE test (a int);
CREATE FUNCTION testfunc() RETURNS int LANGUAGE SQL AS 'INSERT INTO
public.test VALUES (1) RETURNING *';
CREATE VIEW testview(a) AS SELECT testfunc();
CREATE FOREIGN TABLE testft (a int) SERVER loopback OPTIONS
(table_name 'testview');

START TRANSACTION READ ONLY;
SELECT * FROM testft;
 a
---
 1
(1 row)

COMMIT;
SELECT * FROM test;
 a
---
 1
(1 row)

The transaction is declared as READ ONLY, but the INSERT statement is
successfully executed in the remote side.

To avoid that, I would like to propose a server option,
inherit_read_only, to open the remote transactions in read-only mode
if the local transaction is read-only.

I would also like to propose a server option, inherit_deferrable, to
open the remote transactions in deferrable mode if the local
transaction is deferrable.

Attached is a small patch for these options.  I will add this to the
March commitfest as it is still open.

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 2 Mar 2025 13:01:10 +0100

> March commitfest as it is still open.

The CF was changed to in-progress just before, so I added it to the next CF.

Best regards,
Etsuro Fujita

---

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 3 Mar 2025 18:21:15 +0530

Hi Fujita-san,

> START TRANSACTION READ ONLY;

I am having a hard time deciding whether this is problematic behaviour
or not. Maybe the way example is setup - it's querying a view on a
remote database which doesn't return anything but modified data. If
there is no modification happening on the foreign server it won't
return any data. Thus we have no way to verify that the table changed
because of a READ ONLY transaction which is not expected to change any
data. Probably some other example which returns all the rows from test
while modifying some of it might be better.

> if the local transaction is read-only.

Why do we need a server option. Either we say that a local READ ONLY
transaction causing modifications on the foreign server is problematic
or it's expected. But what's the point in giving that choice to the
user? If we deem the behaviour problematic it should be considered as
a bug and we should fix it. Otherwise not fix it.

> transaction is deferrable.

The documentation about deferrable is quite confusing. It says "The
DEFERRABLE transaction property has no effect unless the transaction
is also SERIALIZABLE and READ ONLY." But it doesn't tell what's the
effect of deferrable transaction. But probably we don't need a server
option here as well.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Tom Lane <[email-masked]>
Date: Mon, 03 Mar 2025 10:49:30 -0500

> a bug and we should fix it. Otherwise not fix it.

I tend to agree with Ashutosh's position here.  Reasoning about
issues like this is hard enough already.  Having to figure out an
application's behavior under more than one setting makes it harder.
You may argue that "then the application can choose the behavior it
likes, so there's no need to figure out both behaviors".  But for a
lot of bits of code, that's not the situation; rather, they have to
be prepared to work under both settings, because someone else is
in charge of what the setting is.  (I don't know if either of you
recall our disastrous attempt at server-side autocommit, back around
7.3.  The reason that got reverted was exactly that there was too
much code that had to be prepared to work under either setting,
and it was too hard to make that happen.  So now I look with great
suspicion at anything that complicates our transactional behavior.)

> effect of deferrable transaction. But probably we don't need a server

Yeah, same with this: we should either change it or not.  Multiple
possible transactional behaviors don't do anyone any favors.

			regards, tom lane

---

From: Etsuro Fujita <[email-masked]>
Date: Tue, 25 Mar 2025 11:31:08 +0100

> possible transactional behaviors don't do anyone any favors.

I thought some users might rely on the current behavior that remote
transactions can write even if the local transaction is READ ONLY, so
it would be a good idea to add a server option for backwards
compatibility, but I agree that such an option would cause another,
more difficult problem you mentioned above.

I think the current behavior is not easy to understand, causing
unexpected results in a READ ONLY transaction as shown upthread, so I
would like to instead propose to fix it (for HEAD only as there are no
reports on this issue if I remember correctly).  I think those relying
on the current behavior should just re-declare their transactions as
READ WRITE.  Attached is an updated version of the patch, which fixes
the deferrable case as well.

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.

Thank you both!

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Tue, 25 Mar 2025 11:44:43 +0100

> while modifying some of it might be better.

How about something like this?

CREATE TABLE loct (f1 int, f2 text);
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 remt (f1 int, f2 text)
  SERVER loopback OPTIONS (table_name 'locv');
INSERT INTO loct VALUES (1, 'foo'), (2, 'bar');
SELECT * FROM loct;
 f1 | f2
----+-----
  1 | foo
  2 | bar
(2 rows)

SELECT * FROM remt;  -- should work
 f1 |   f2
----+--------
  1 | foofoo
  2 | barbar
(2 rows)

SELECT * FROM loct;
 f1 |   f2
----+--------
  1 | foofoo
  2 | barbar
(2 rows)

I added this test case to the updated patch [1].

Thanks for the comments!

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK14Btk0odkH6vwBhBGjCexmmWcM_D3DG0pJtObj8k_Unag%40mail.gmail.com

---

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 27 Mar 2025 17:55:24 +0530

> 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.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 30 Mar 2025 12:14:01 +0200

> subtransaction behaviour.

Ok, will do.

Thanks for the comment!

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 4 May 2025 21:30:42 +0900

> > subtransaction behaviour.

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

---

From: Etsuro Fujita <[email-masked]>
Date: Thu, 8 May 2025 17:50:43 +0900

On Sun, May 4, 2025 at 9:30 PM Etsuro Fujita <[email-masked]> wrote:
> As read-only

I noticed that an assertion I added to pgfdw_subxact_callback fails
when running some transactions.  To fix, I just removed the assertion.
Attached is an updated version of the patch.  I also added a test case
that was causing the failure, and tweaked some comments/docs a little
bit.

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 25 May 2025 14:39:50 +0900

On Thu, May 8, 2025 at 5:50 PM Etsuro Fujita <[email-masked]> wrote:
> Attached is an updated version of the patch.

Here is a new version of the patch where I added a comment for a new
function, fixed indentation, and added the commit message.  If there
are no objections, I will push this as a master-only fix, as noted in
the commit message.

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 1 Jun 2025 17:44:49 +0900

> are no objections, I will push this as a master-only fix, as noted in

Pushed after extending the comment a little bit.

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Wed, 25 Feb 2026 19:22:26 +0900

> Pushed after extending the comment a little bit.

This was reverted in commit 7d4667c62.  I'd like to re-propose it for
v19, as mentioned in [1].  Attached is a new patch, in which I added
to the documentation a note about login triggers executed on the
remote side, as discussed in [1].  Other than that, no changes.  I've
added this to the upcoming CF.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK14yYoO2iBg7SoT3WdnAKoCpWy_LPdoTDepJ21-Yf8TiKA%40mail.gmail.com

---

From: Etsuro Fujita <[email-masked]>
Date: Thu, 5 Mar 2026 08:52:22 +0900

> added this to the upcoming CF.

Here is an updated version of the patch.  Changes are:

* On second thought, I think the name of the variable
top_read_only_level added to connection.c by the patch is a bit long,
so I renamed it to top_read_only.  Does that make sense?  Other than
that, no code changes.
* I also added/modified some comments.

Comments welcome!

Best regards,
Etsuro Fujita

---

From: Fujii Masao <[email-masked]>
Date: Thu, 5 Mar 2026 12:10:50 +0900

> * I also added/modified some comments.

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.

Regards,

-- 
Fujii Masao

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 8 Mar 2026 03:37:44 +0900

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

---

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 9 Mar 2026 09:17:21 +0530

> start transaction read only;

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-=[email-masked]

-- 
Best Wishes,
Ashutosh Bapat

---

From: Etsuro Fujita <[email-masked]>
Date: Mon, 9 Mar 2026 20:41:04 +0900

> consumption, and they can provide backing data, adding the option

+1; I agree with you 100%.  Thanks for the comments!

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Fri, 27 Mar 2026 20:41:29 +0900

On Mon, Mar 9, 2026 at 8:41 PM Etsuro Fujita <[email-masked]> wrote:
> +1; I agree with you 100%.

Barring objections, I'll commit the patch early next week.

Best regards,
Etsuro Fujita

---

From: Etsuro Fujita <[email-masked]>
Date: Sun, 5 Apr 2026 19:02:01 +0900

> Barring objections, I'll commit the patch early next week.

Pushed.

Best regards,
Etsuro Fujita