On Sun, Mar 8, 2026 at 12:07 AM Etsuro Fujita <[email protected]> wrote: > > 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.
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-=1khal8s4xvvzsozvrbmbbvg97p0obwew3sd5...@mail.gmail.com -- Best Wishes, Ashutosh Bapat
