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
