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
Inherit-xact-properties-in-postgres-fdw.patch
Description: Binary data
