Hi Fujita-san,
On Sun, Mar 2, 2025 at 5:14 PM Etsuro Fujita <etsuro.fuj...@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
