Horton

Thread

Commits

  1. postgres_fdw: Inherit the local transaction's access/deferrable modes.

  2. Revert "postgres_fdw: Inherit the local transaction's access/deferrable modes."

  1. Options to control remote transactions’ access/deferrable modes in postgres_fdw

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-03-02T11:44:27Z

    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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-03-02T12:01:10Z

    On Sun, Mar 2, 2025 at 12:44 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> Attached is a small patch for these options.  I will add this to the
> 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

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

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2025-03-03T12:51:15Z

    Hi Fujita-san,


On Sun, Mar 2, 2025 at 5:14 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> 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)

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.

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

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.

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

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-03T15:49:30Z

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
> On Sun, Mar 2, 2025 at 5:14 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>> 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.

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

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

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

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

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

			regards, tom lane

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-03-25T10:31:08Z

    On Mon, Mar 3, 2025 at 4:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
> > On Sun, Mar 2, 2025 at 5:14 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> >> 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.
>
> > 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.
>
> 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.)
>
> >> 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.
>
> > 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.
>
> Yeah, same with this: we should either change it or not.  Multiple
> 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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-03-25T10:44:43Z

    On Mon, Mar 3, 2025 at 1:51 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> On Sun, Mar 2, 2025 at 5:14 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > 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:

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

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

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

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2025-03-27T12:25:24Z

    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.

-- 
Best Wishes,
Ashutosh Bapat

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-03-30T10:14:01Z

    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.

Thanks for the comment!

Best regards,
Etsuro Fujita

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-05-04T12:30:42Z

    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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-05-08T08:50:43Z

    On Sun, May 4, 2025 at 9:30 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> 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.

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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-05-25T05:39:50Z

    On Thu, May 8, 2025 at 5:50 PM Etsuro Fujita <etsuro.fujita@gmail.com> 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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-06-01T08:44:49Z

    On Sun, May 25, 2025 at 2:39 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> 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.

Pushed after extending the comment a little bit.

Best regards,
Etsuro Fujita

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-02-25T10:22:26Z

    On Sun, Jun 1, 2025 at 5:44 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Sun, May 25, 2025 at 2:39 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > 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.
>
> 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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-03-04T23:52:22Z

    On Wed, Feb 25, 2026 at 7:22 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> 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.

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

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

    Fujii Masao <masao.fujii@gmail.com> — 2026-03-05T03:10:50Z

    On Thu, Mar 5, 2026 at 8:52 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> On Wed, Feb 25, 2026 at 7:22 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > 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.
>
> 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!

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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-03-07T18:37:44Z

    On Thu, Mar 5, 2026 at 12:11 PM Fujii Masao <masao.fujii@gmail.com> 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

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

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-03-09T03:47:21Z

    On Sun, Mar 8, 2026 at 12:07 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> On Thu, Mar 5, 2026 at 12:11 PM Fujii Masao <masao.fujii@gmail.com> 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.

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-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com

-- 
Best Wishes,
Ashutosh Bapat

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-03-09T11:41:04Z

    On Mon, Mar 9, 2026 at 12:47 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> 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; I agree with you 100%.  Thanks for the comments!

Best regards,
Etsuro Fujita

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-03-27T11:41:29Z

    On Mon, Mar 9, 2026 at 8:41 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Mon, Mar 9, 2026 at 12:47 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > 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; I agree with you 100%.

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

Best regards,
Etsuro Fujita

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-04-05T10:02:01Z

    On Fri, Mar 27, 2026 at 8:41 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> Barring objections, I'll commit the patch early next week.

Pushed.

Best regards,
Etsuro Fujita