MIME-Version: 1.0
From: Etsuro Fujita <etsuro.fujita@gmail.com>
Date: Sun, 2 Mar 2025 12:44:27 +0100
Message-ID: <CAPmGK16n_hcUUWuOdmeUS+w4Q6dZvTEDHb=OP=5JBzo-M3QmpQ@mail.gmail.com>
Subject: =?UTF-8?Q?Options_to_control_remote_transactions=E2=80=99_access=2Fdef?=
	=?UTF-8?Q?errable_modes_in_postgres=5Ffdw?=
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000a3b645062f5a9095"
Archived-At: <https://www.postgresql.org/message-id/CAPmGK16n_hcUUWuOdmeUS%2Bw4Q6dZvTEDHb%3DOP%3D5JBzo-M3QmpQ%40mail.gmail.com>
Precedence: bulk

--000000000000a3b645062f5a9095
Content-Type: text/plain; charset="UTF-8"

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

--000000000000a3b645062f5a9095
Content-Type: application/octet-stream; 
	name="Inherit-xact-properties-in-postgres-fdw.patch"
Content-Disposition: attachment; 
	filename="Inherit-xact-properties-in-postgres-fdw.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m7rka3em0>
X-Attachment-Id: f_m7rka3em0

ZGlmZiAtLWdpdCBhL2NvbnRyaWIvcG9zdGdyZXNfZmR3L2Nvbm5lY3Rpb24uYyBiL2NvbnRyaWIv
cG9zdGdyZXNfZmR3L2Nvbm5lY3Rpb24uYwppbmRleCA4YThkM2I0NDgxLi5jNTBlODZjNmExIDEw
MDY0NAotLS0gYS9jb250cmliL3Bvc3RncmVzX2Zkdy9jb25uZWN0aW9uLmMKKysrIGIvY29udHJp
Yi9wb3N0Z3Jlc19mZHcvY29ubmVjdGlvbi5jCkBAIC02MSw2ICs2MSw4IEBAIHR5cGVkZWYgc3Ry
dWN0IENvbm5DYWNoZUVudHJ5CiAJYm9vbAkJaGF2ZV9wcmVwX3N0bXQ7IC8qIGhhdmUgd2UgcHJl
cGFyZWQgYW55IHN0bXRzIGluIHRoaXMgeGFjdD8gKi8KIAlib29sCQloYXZlX2Vycm9yOwkJLyog
aGF2ZSBhbnkgc3VieGFjdHMgYWJvcnRlZCBpbiB0aGlzIHhhY3Q/ICovCiAJYm9vbAkJY2hhbmdp
bmdfeGFjdF9zdGF0ZTsJLyogeGFjdCBzdGF0ZSBjaGFuZ2UgaW4gcHJvY2VzcyAqLworCWJvb2wJ
CWluaGVyaXRfcmVhZF9vbmx5OwkvKiBkbyB3ZSBvcGVuIHhhY3RzIGluIHJlYWQtb25seSBtb2Rl
PyAqLworCWJvb2wJCWluaGVyaXRfZGVmZXJyYWJsZTsJLyogZG8gd2Ugb3BlbiB4YWN0cyBpbiBk
ZWRlcnJhYmxlIG1vZGU/ICovCiAJYm9vbAkJcGFyYWxsZWxfY29tbWl0OwkvKiBkbyB3ZSBjb21t
aXQgKHN1Yil4YWN0cyBpbiBwYXJhbGxlbD8gKi8KIAlib29sCQlwYXJhbGxlbF9hYm9ydDsgLyog
ZG8gd2UgYWJvcnQgKHN1Yil4YWN0cyBpbiBwYXJhbGxlbD8gKi8KIAlib29sCQlpbnZhbGlkYXRl
ZDsJLyogdHJ1ZSBpZiByZWNvbm5lY3QgaXMgcGVuZGluZyAqLwpAQCAtMzkxLDYgKzM5Myw5IEBA
IG1ha2VfbmV3X2Nvbm5lY3Rpb24oQ29ubkNhY2hlRW50cnkgKmVudHJ5LCBVc2VyTWFwcGluZyAq
dXNlcikKIAkgKgogCSAqIEJ5IGRlZmF1bHQsIGFsbCB0aGUgY29ubmVjdGlvbnMgdG8gYW55IGZv
cmVpZ24gc2VydmVycyBhcmUga2VwdCBvcGVuLgogCSAqCisJICogQWxzbyBkZXRlcm1pbmUgd2hl
dGhlciB0byBvcGVuIHRyYW5zYWN0aW9ucyBpbiByZWFkLW9ubHkgYW5kL29yCisJICogZGVmZXJy
YWJsZSBtb2RlcyBvbiB0aGUgcmVtb3RlIHNlcnZlciwgd2hpY2ggaXMgZGlzYWJsZWQgYnkgZGVm
YXVsdC4KKwkgKgogCSAqIEFsc28gZGV0ZXJtaW5lIHdoZXRoZXIgdG8gY29tbWl0L2Fib3J0IChz
dWIpdHJhbnNhY3Rpb25zIG9wZW5lZCBvbiB0aGUKIAkgKiByZW1vdGUgc2VydmVyIGluIHBhcmFs
bGVsIGF0IChzdWIpdHJhbnNhY3Rpb24gZW5kLCB3aGljaCBpcyBkaXNhYmxlZCBieQogCSAqIGRl
ZmF1bHQuCkBAIC00MDAsNiArNDA1LDggQEAgbWFrZV9uZXdfY29ubmVjdGlvbihDb25uQ2FjaGVF
bnRyeSAqZW50cnksIFVzZXJNYXBwaW5nICp1c2VyKQogCSAqIHJlLW1hZGUgbGF0ZXIuCiAJICov
CiAJZW50cnktPmtlZXBfY29ubmVjdGlvbnMgPSB0cnVlOworCWVudHJ5LT5pbmhlcml0X3JlYWRf
b25seSA9IGZhbHNlOworCWVudHJ5LT5pbmhlcml0X2RlZmVycmFibGUgPSBmYWxzZTsKIAllbnRy
eS0+cGFyYWxsZWxfY29tbWl0ID0gZmFsc2U7CiAJZW50cnktPnBhcmFsbGVsX2Fib3J0ID0gZmFs
c2U7CiAJZm9yZWFjaChsYywgc2VydmVyLT5vcHRpb25zKQpAQCAtNDA4LDYgKzQxNSwxMCBAQCBt
YWtlX25ld19jb25uZWN0aW9uKENvbm5DYWNoZUVudHJ5ICplbnRyeSwgVXNlck1hcHBpbmcgKnVz
ZXIpCiAKIAkJaWYgKHN0cmNtcChkZWYtPmRlZm5hbWUsICJrZWVwX2Nvbm5lY3Rpb25zIikgPT0g
MCkKIAkJCWVudHJ5LT5rZWVwX2Nvbm5lY3Rpb25zID0gZGVmR2V0Qm9vbGVhbihkZWYpOworCQll
bHNlIGlmIChzdHJjbXAoZGVmLT5kZWZuYW1lLCAiaW5oZXJpdF9yZWFkX29ubHkiKSA9PSAwKQor
CQkJZW50cnktPmluaGVyaXRfcmVhZF9vbmx5ID0gZGVmR2V0Qm9vbGVhbihkZWYpOworCQllbHNl
IGlmIChzdHJjbXAoZGVmLT5kZWZuYW1lLCAiaW5oZXJpdF9kZWZlcnJhYmxlIikgPT0gMCkKKwkJ
CWVudHJ5LT5pbmhlcml0X2RlZmVycmFibGUgPSBkZWZHZXRCb29sZWFuKGRlZik7CiAJCWVsc2Ug
aWYgKHN0cmNtcChkZWYtPmRlZm5hbWUsICJwYXJhbGxlbF9jb21taXQiKSA9PSAwKQogCQkJZW50
cnktPnBhcmFsbGVsX2NvbW1pdCA9IGRlZkdldEJvb2xlYW4oZGVmKTsKIAkJZWxzZSBpZiAoc3Ry
Y21wKGRlZi0+ZGVmbmFtZSwgInBhcmFsbGVsX2Fib3J0IikgPT0gMCkKQEAgLTgzMSwxNyArODQy
LDI2IEBAIGJlZ2luX3JlbW90ZV94YWN0KENvbm5DYWNoZUVudHJ5ICplbnRyeSkKIAkvKiBTdGFy
dCBtYWluIHRyYW5zYWN0aW9uIGlmIHdlIGhhdmVuJ3QgeWV0ICovCiAJaWYgKGVudHJ5LT54YWN0
X2RlcHRoIDw9IDApCiAJewotCQljb25zdCBjaGFyICpzcWw7CisJCVN0cmluZ0luZm9EYXRhIHNx
bDsKIAogCQllbG9nKERFQlVHMywgInN0YXJ0aW5nIHJlbW90ZSB0cmFuc2FjdGlvbiBvbiBjb25u
ZWN0aW9uICVwIiwKIAkJCSBlbnRyeS0+Y29ubik7CiAKKwkJaW5pdFN0cmluZ0luZm8oJnNxbCk7
CisJCWFwcGVuZFN0cmluZ0luZm9TdHJpbmcoJnNxbCwgIlNUQVJUIFRSQU5TQUNUSU9OIElTT0xB
VElPTiBMRVZFTCAiKTsKIAkJaWYgKElzb2xhdGlvbklzU2VyaWFsaXphYmxlKCkpCi0JCQlzcWwg
PSAiU1RBUlQgVFJBTlNBQ1RJT04gSVNPTEFUSU9OIExFVkVMIFNFUklBTElaQUJMRSI7CisJCQlh
cHBlbmRTdHJpbmdJbmZvU3RyaW5nKCZzcWwsICJTRVJJQUxJWkFCTEUiKTsKIAkJZWxzZQotCQkJ
c3FsID0gIlNUQVJUIFRSQU5TQUNUSU9OIElTT0xBVElPTiBMRVZFTCBSRVBFQVRBQkxFIFJFQUQi
OworCQkJYXBwZW5kU3RyaW5nSW5mb1N0cmluZygmc3FsLCAiUkVQRUFUQUJMRSBSRUFEIik7CisK
KwkJLyogQXBwZW5kIGFjY2Vzcy9kZWZlcnJhYmxlIG1vZGVzIGlmIG5lZWRlZCAqLworCQlpZiAo
ZW50cnktPmluaGVyaXRfcmVhZF9vbmx5ICYmIFhhY3RSZWFkT25seSkKKwkJCWFwcGVuZFN0cmlu
Z0luZm9TdHJpbmcoJnNxbCwgIiBSRUFEIE9OTFkiKTsKKwkJaWYgKGVudHJ5LT5pbmhlcml0X2Rl
ZmVycmFibGUgJiYgWGFjdERlZmVycmFibGUpCisJCQlhcHBlbmRTdHJpbmdJbmZvU3RyaW5nKCZz
cWwsICIgREVGRVJSQUJMRSIpOworCiAJCWVudHJ5LT5jaGFuZ2luZ194YWN0X3N0YXRlID0gdHJ1
ZTsKLQkJZG9fc3FsX2NvbW1hbmQoZW50cnktPmNvbm4sIHNxbCk7CisJCWRvX3NxbF9jb21tYW5k
KGVudHJ5LT5jb25uLCBzcWwuZGF0YSk7CiAJCWVudHJ5LT54YWN0X2RlcHRoID0gMTsKIAkJZW50
cnktPmNoYW5naW5nX3hhY3Rfc3RhdGUgPSBmYWxzZTsKIAl9CmRpZmYgLS1naXQgYS9jb250cmli
L3Bvc3RncmVzX2Zkdy9vcHRpb24uYyBiL2NvbnRyaWIvcG9zdGdyZXNfZmR3L29wdGlvbi5jCmlu
ZGV4IGQwNzY2ZjAwN2QuLmU0MTExNTAzMTggMTAwNjQ0Ci0tLSBhL2NvbnRyaWIvcG9zdGdyZXNf
ZmR3L29wdGlvbi5jCisrKyBiL2NvbnRyaWIvcG9zdGdyZXNfZmR3L29wdGlvbi5jCkBAIC0xMjMs
NiArMTIzLDggQEAgcG9zdGdyZXNfZmR3X3ZhbGlkYXRvcihQR19GVU5DVElPTl9BUkdTKQogCQkJ
c3RyY21wKGRlZi0+ZGVmbmFtZSwgInVwZGF0YWJsZSIpID09IDAgfHwKIAkJCXN0cmNtcChkZWYt
PmRlZm5hbWUsICJ0cnVuY2F0YWJsZSIpID09IDAgfHwKIAkJCXN0cmNtcChkZWYtPmRlZm5hbWUs
ICJhc3luY19jYXBhYmxlIikgPT0gMCB8fAorCQkJc3RyY21wKGRlZi0+ZGVmbmFtZSwgImluaGVy
aXRfcmVhZF9vbmx5IikgPT0gMCB8fAorCQkJc3RyY21wKGRlZi0+ZGVmbmFtZSwgImluaGVyaXRf
ZGVmZXJyYWJsZSIpID09IDAgfHwKIAkJCXN0cmNtcChkZWYtPmRlZm5hbWUsICJwYXJhbGxlbF9j
b21taXQiKSA9PSAwIHx8CiAJCQlzdHJjbXAoZGVmLT5kZWZuYW1lLCAicGFyYWxsZWxfYWJvcnQi
KSA9PSAwIHx8CiAJCQlzdHJjbXAoZGVmLT5kZWZuYW1lLCAia2VlcF9jb25uZWN0aW9ucyIpID09
IDApCkBAIC0yNzAsNiArMjcyLDggQEAgSW5pdFBnRmR3T3B0aW9ucyh2b2lkKQogCQkvKiBhc3lu
Y19jYXBhYmxlIGlzIGF2YWlsYWJsZSBvbiBib3RoIHNlcnZlciBhbmQgdGFibGUgKi8KIAkJeyJh
c3luY19jYXBhYmxlIiwgRm9yZWlnblNlcnZlclJlbGF0aW9uSWQsIGZhbHNlfSwKIAkJeyJhc3lu
Y19jYXBhYmxlIiwgRm9yZWlnblRhYmxlUmVsYXRpb25JZCwgZmFsc2V9LAorCQl7ImluaGVyaXRf
cmVhZF9vbmx5IiwgRm9yZWlnblNlcnZlclJlbGF0aW9uSWQsIGZhbHNlfSwKKwkJeyJpbmhlcml0
X2RlZmVycmFibGUiLCBGb3JlaWduU2VydmVyUmVsYXRpb25JZCwgZmFsc2V9LAogCQl7InBhcmFs
bGVsX2NvbW1pdCIsIEZvcmVpZ25TZXJ2ZXJSZWxhdGlvbklkLCBmYWxzZX0sCiAJCXsicGFyYWxs
ZWxfYWJvcnQiLCBGb3JlaWduU2VydmVyUmVsYXRpb25JZCwgZmFsc2V9LAogCQl7ImtlZXBfY29u
bmVjdGlvbnMiLCBGb3JlaWduU2VydmVyUmVsYXRpb25JZCwgZmFsc2V9LApkaWZmIC0tZ2l0IGEv
ZG9jL3NyYy9zZ21sL3Bvc3RncmVzLWZkdy5zZ21sIGIvZG9jL3NyYy9zZ21sL3Bvc3RncmVzLWZk
dy5zZ21sCmluZGV4IGQyOTk4YzEzZDUuLjMzN2EyZDYyNTIgMTAwNjQ0Ci0tLSBhL2RvYy9zcmMv
c2dtbC9wb3N0Z3Jlcy1mZHcuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvcG9zdGdyZXMtZmR3LnNn
bWwKQEAgLTUyNCw2ICs1MjQsMzIgQEAgT1BUSU9OUyAoQUREIHBhc3N3b3JkX3JlcXVpcmVkICdm
YWxzZScpOwogCiAgICA8dmFyaWFibGVsaXN0PgogCisgICAgPHZhcmxpc3RlbnRyeT4KKyAgICAg
PHRlcm0+PGxpdGVyYWw+aW5oZXJpdF9yZWFkX29ubHk8L2xpdGVyYWw+ICg8dHlwZT5ib29sZWFu
PC90eXBlPik8L3Rlcm0+CisgICAgIDxsaXN0aXRlbT4KKyAgICAgIDxwYXJhPgorICAgICAgIFRo
aXMgb3B0aW9uIGNvbnRyb2xzIHdoZXRoZXIgPGZpbGVuYW1lPnBvc3RncmVzX2ZkdzwvZmlsZW5h
bWU+IG9wZW5zCisgICAgICAgcmVtb3RlIHRyYW5zYWN0aW9ucyBpbiByZWFkLW9ubHkgbW9kZSBv
biBhIGZvcmVpZ24gc2VydmVyIGluIGEgbG9jYWwKKyAgICAgICB0cmFuc2FjdGlvbiBpZiB0aGUg
bG9jYWwgdHJhbnNhY3Rpb24gaXMgcmVhZC1vbmx5LgorICAgICAgIFRoaXMgb3B0aW9uIGNhbiBv
bmx5IGJlIHNwZWNpZmllZCBmb3IgZm9yZWlnbiBzZXJ2ZXJzLCBub3QgcGVyLXRhYmxlLgorICAg
ICAgIFRoZSBkZWZhdWx0IGlzIDxsaXRlcmFsPmZhbHNlPC9saXRlcmFsPi4KKyAgICAgIDwvcGFy
YT4KKyAgICAgPC9saXN0aXRlbT4KKyAgICA8L3Zhcmxpc3RlbnRyeT4KKworICAgIDx2YXJsaXN0
ZW50cnk+CisgICAgIDx0ZXJtPjxsaXRlcmFsPmluaGVyaXRfZGVmZXJyYWJsZTwvbGl0ZXJhbD4g
KDx0eXBlPmJvb2xlYW48L3R5cGU+KTwvdGVybT4KKyAgICAgPGxpc3RpdGVtPgorICAgICAgPHBh
cmE+CisgICAgICAgVGhpcyBvcHRpb24gY29udHJvbHMgd2hldGhlciA8ZmlsZW5hbWU+cG9zdGdy
ZXNfZmR3PC9maWxlbmFtZT4gb3BlbnMKKyAgICAgICByZW1vdGUgdHJhbnNhY3Rpb25zIGluIGRl
ZmVycmFibGUgbW9kZSBvbiBhIGZvcmVpZ24gc2VydmVyIGluIGEgbG9jYWwKKyAgICAgICB0cmFu
c2FjdGlvbiBpZiB0aGUgbG9jYWwgdHJhbnNhY3Rpb24gaXMgZGVmZXJyYWJsZS4KKyAgICAgICBU
aGlzIG9wdGlvbiBjYW4gb25seSBiZSBzcGVjaWZpZWQgZm9yIGZvcmVpZ24gc2VydmVycywgbm90
IHBlci10YWJsZS4KKyAgICAgICBUaGUgZGVmYXVsdCBpcyA8bGl0ZXJhbD5mYWxzZTwvbGl0ZXJh
bD4uCisgICAgICA8L3BhcmE+CisgICAgIDwvbGlzdGl0ZW0+CisgICAgPC92YXJsaXN0ZW50cnk+
CisKICAgICA8dmFybGlzdGVudHJ5PgogICAgICA8dGVybT48bGl0ZXJhbD5wYXJhbGxlbF9jb21t
aXQ8L2xpdGVyYWw+ICg8dHlwZT5ib29sZWFuPC90eXBlPik8L3Rlcm0+CiAgICAgIDxsaXN0aXRl
bT4K
--000000000000a3b645062f5a9095--