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