MIME-Version: 1.0
From: Yasuo Honda <yasuo.honda@gmail.com>
Date: Fri, 27 Feb 2026 18:51:02 +0900
Message-ID: <CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com>
Subject: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT
 ENFORCED / ENFORCED
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000820e9d064bcb2d36"
Archived-At: <https://www.postgresql.org/message-id/CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ%40mail.gmail.com>
Precedence: bulk

--000000000000820e9d064bcb2d36
Content-Type: text/plain; charset="UTF-8"

Hi,

I have encountered an unexpected behavior where the DEFERRABLE and
INITIALLY DEFERRED properties of foreign keys are lost after toggling
them from NOT ENFORCED to ENFORCED.

Background

In the Ruby on Rails framework, there is a built-in mechanism to
temporarily bypass foreign key checks while loading test data.
Currently, this is implemented using: ALTER TABLE ... DISABLE TRIGGER
ALL; ALTER TABLE ... ENABLE TRIGGER ALL;

However, this requires superuser privileges. With the newly introduced
support for "NOT ENFORCED" foreign keys in PostgreSQL 18, I am
interested in switching to: ALTER TABLE ... ALTER CONSTRAINT ... NOT
ENFORCED; ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED;

This would allow the operation to be performed by the table owner
without superuser rights. However, I discovered that switching the
state back to ENFORCED unexpectedly strips away the DEFERRABLE
property.

Problem

When re-enforcing a constraint, there is a discrepancy between the
constraint definition and its underlying triggers.
While the flags in pg_constraint remain correct, the corresponding
triggers in pg_trigger (tgdeferrable and tginitdeferred) are reset to
defaults ('f') when they are reconstructed during the ENFORCED
operation.

I have attached a reproduction SQL script that demonstrates this by
comparing the values in pg_constraint and pg_trigger. In the current
PostgreSQL 18.3, you can see that the triggers lose their
deferrability even though the constraint itself is still defined as
DEFERRABLE. This causes "SET CONSTRAINTS ... DEFERRED" to fail.

After Patch

* The tgdeferrable and tginitdeferred flags in pg_trigger are
correctly preserved to match pg_constraint after the toggle, and
deferred execution works as expected.

I've attached the reproduction SQL script and a patch to fix this in
src/backend/commands/tablecmds.c. The patch and reproduction SQL
script were developed with the assistance of Claude Code. I have
reviewed and verified the code myself.

Any feedback is appreciated.
Regards,
--
Yasuo Honda

--000000000000820e9d064bcb2d36
Content-Type: application/octet-stream; 
	name="0001-Restore-tgdeferrable-and-tginitdeferred-after-NOT-EN.patch"
Content-Disposition: attachment; 
	filename="0001-Restore-tgdeferrable-and-tginitdeferred-after-NOT-EN.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mm4poqoo0>
X-Attachment-Id: f_mm4poqoo0

RnJvbSBhMDYxYmRkMjMyZWU5NjMwMmMwYWY0ZDRhN2Y0YzBmOTAxMjYyY2E5IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBZYXN1byBIb25kYSA8eWFzdW8uaG9uZGFAZ21haWwuY29tPgpE
YXRlOiBGcmksIDI3IEZlYiAyMDI2IDE3OjMwOjA0ICswOTAwClN1YmplY3Q6IFtQQVRDSF0gUmVz
dG9yZSB0Z2RlZmVycmFibGUgYW5kIHRnaW5pdGRlZmVycmVkIGFmdGVyIE5PVCBFTkZPUkNFRAog
dGhlbiBFTkZPUkNFRAoKLS0tCiBzcmMvYmFja2VuZC9jb21tYW5kcy90YWJsZWNtZHMuYyAgICAg
ICAgICB8ICAyICsrCiBzcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2ZvcmVpZ25fa2V5Lm91dCB8
IDE4ICsrKysrKysrKysrKysrKysrKwogc3JjL3Rlc3QvcmVncmVzcy9zcWwvZm9yZWlnbl9rZXku
c3FsICAgICAgfCAxMCArKysrKysrKysrCiAzIGZpbGVzIGNoYW5nZWQsIDMwIGluc2VydGlvbnMo
KykKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9jb21tYW5kcy90YWJsZWNtZHMuYyBiL3NyYy9i
YWNrZW5kL2NvbW1hbmRzL3RhYmxlY21kcy5jCmluZGV4IGIwNGIwZGJkMmEwLi5iYzA3ZGZlMmNl
OCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKKysrIGIvc3Jj
L2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKQEAgLTEyNTAxLDYgKzEyNTAxLDggQEAgQVRF
eGVjQWx0ZXJDb25zdHJFbmZvcmNlYWJpbGl0eShMaXN0ICoqd3F1ZXVlLCBBVEFsdGVyQ29uc3Ry
YWludCAqY21kY29uLAogCQlma2NvbnN0cmFpbnQtPmZrX21hdGNodHlwZSA9IGN1cnJjb24tPmNv
bmZtYXRjaHR5cGU7CiAJCWZrY29uc3RyYWludC0+ZmtfdXBkX2FjdGlvbiA9IGN1cnJjb24tPmNv
bmZ1cGR0eXBlOwogCQlma2NvbnN0cmFpbnQtPmZrX2RlbF9hY3Rpb24gPSBjdXJyY29uLT5jb25m
ZGVsdHlwZTsKKwkJZmtjb25zdHJhaW50LT5kZWZlcnJhYmxlID0gY3VycmNvbi0+Y29uZGVmZXJy
YWJsZTsKKwkJZmtjb25zdHJhaW50LT5pbml0ZGVmZXJyZWQgPSBjdXJyY29uLT5jb25kZWZlcnJl
ZDsKIAogCQkvKiBDcmVhdGUgcmVmZXJlbmNlZCB0cmlnZ2VycyAqLwogCQlpZiAoY3VycmNvbi0+
Y29ucmVsaWQgPT0gZmtyZWxpZCkKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0
ZWQvZm9yZWlnbl9rZXkub3V0IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9mb3JlaWduX2tl
eS5vdXQKaW5kZXggOWFlNGRiZjFiMGEuLmQwNjFlNTFkYTZiIDEwMDY0NAotLS0gYS9zcmMvdGVz
dC9yZWdyZXNzL2V4cGVjdGVkL2ZvcmVpZ25fa2V5Lm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNz
L2V4cGVjdGVkL2ZvcmVpZ25fa2V5Lm91dApAQCAtMTM0OSw2ICsxMzQ5LDI0IEBAIFVQREFURSBw
a3RhYmxlIFNFVCBpZCA9IDEwIFdIRVJFIGlkID0gNTsKIC0tIGRvZXNuJ3QgbWF0Y2ggUEssIGJ1
dCBubyBlcnJvci4KIElOU0VSVCBJTlRPIGZrdGFibGUgVkFMVUVTICgwLCAyMCk7CiBST0xMQkFD
SzsKKy0tIHZlcmlmeSB0aGF0IHRnZGVmZXJyYWJsZS90Z2luaXRkZWZlcnJlZCBhcmUgcHJlc2Vy
dmVkIGFmdGVyIE5PVCBFTkZPUkNFRCAtPiBFTkZPUkNFRAorQUxURVIgVEFCTEUgRktUQUJMRSBB
TFRFUiBDT05TVFJBSU5UIGZrdGFibGVfZmtfZmtleSBERUZFUlJBQkxFIElOSVRJQUxMWSBERUZF
UlJFRDsKK0FMVEVSIFRBQkxFIEZLVEFCTEUgQUxURVIgQ09OU1RSQUlOVCBma3RhYmxlX2ZrX2Zr
ZXkgTk9UIEVORk9SQ0VEOworQUxURVIgVEFCTEUgRktUQUJMRSBBTFRFUiBDT05TVFJBSU5UIGZr
dGFibGVfZmtfZmtleSBFTkZPUkNFRDsKK1NFTEVDVCB0Z2RlZmVycmFibGUsIHRnaW5pdGRlZmVy
cmVkIEZST00gcGdfdHJpZ2dlcgorV0hFUkUgdGdjb25zdHJhaW50ID0gKFNFTEVDVCBvaWQgRlJP
TSBwZ19jb25zdHJhaW50CisgICAgICAgICAgICAgICAgICAgICAgV0hFUkUgY29ucmVsaWQgPSAn
Zmt0YWJsZSc6OnJlZ2NsYXNzCisgICAgICAgICAgICAgICAgICAgICAgQU5EIGNvbm5hbWUgPSAn
Zmt0YWJsZV9ma19ma2V5Jyk7CisgdGdkZWZlcnJhYmxlIHwgdGdpbml0ZGVmZXJyZWQgCistLS0t
LS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tCisgdCAgICAgICAgICAgIHwgdAorIHQgICAgICAg
ICAgICB8IHQKKyB0ICAgICAgICAgICAgfCB0CisgdCAgICAgICAgICAgIHwgdAorKDQgcm93cykK
KworLS0gcmVzZXQKK0FMVEVSIFRBQkxFIEZLVEFCTEUgQUxURVIgQ09OU1RSQUlOVCBma3RhYmxl
X2ZrX2ZrZXkgTk9UIERFRkVSUkFCTEU7CiAtLSB0cnkgYWRkaXRpb25hbCBzeW50YXgKIEFMVEVS
IFRBQkxFIGZrdGFibGUgQUxURVIgQ09OU1RSQUlOVCBma3RhYmxlX2ZrX2ZrZXkgTk9UIERFRkVS
UkFCTEU7CiAtLSBpbGxlZ2FsIG9wdGlvbnMKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mv
c3FsL2ZvcmVpZ25fa2V5LnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2ZvcmVpZ25fa2V5LnNx
bAppbmRleCAzYjhjOTViZjg5My4uMGFmZWQ2MGY2ODggMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3Jl
Z3Jlc3Mvc3FsL2ZvcmVpZ25fa2V5LnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9mb3Jl
aWduX2tleS5zcWwKQEAgLTEwMjMsNiArMTAyMywxNiBAQCBVUERBVEUgcGt0YWJsZSBTRVQgaWQg
PSAxMCBXSEVSRSBpZCA9IDU7CiBJTlNFUlQgSU5UTyBma3RhYmxlIFZBTFVFUyAoMCwgMjApOwog
CiBST0xMQkFDSzsKKy0tIHZlcmlmeSB0aGF0IHRnZGVmZXJyYWJsZS90Z2luaXRkZWZlcnJlZCBh
cmUgcHJlc2VydmVkIGFmdGVyIE5PVCBFTkZPUkNFRCAtPiBFTkZPUkNFRAorQUxURVIgVEFCTEUg
RktUQUJMRSBBTFRFUiBDT05TVFJBSU5UIGZrdGFibGVfZmtfZmtleSBERUZFUlJBQkxFIElOSVRJ
QUxMWSBERUZFUlJFRDsKK0FMVEVSIFRBQkxFIEZLVEFCTEUgQUxURVIgQ09OU1RSQUlOVCBma3Rh
YmxlX2ZrX2ZrZXkgTk9UIEVORk9SQ0VEOworQUxURVIgVEFCTEUgRktUQUJMRSBBTFRFUiBDT05T
VFJBSU5UIGZrdGFibGVfZmtfZmtleSBFTkZPUkNFRDsKK1NFTEVDVCB0Z2RlZmVycmFibGUsIHRn
aW5pdGRlZmVycmVkIEZST00gcGdfdHJpZ2dlcgorV0hFUkUgdGdjb25zdHJhaW50ID0gKFNFTEVD
VCBvaWQgRlJPTSBwZ19jb25zdHJhaW50CisgICAgICAgICAgICAgICAgICAgICAgV0hFUkUgY29u
cmVsaWQgPSAnZmt0YWJsZSc6OnJlZ2NsYXNzCisgICAgICAgICAgICAgICAgICAgICAgQU5EIGNv
bm5hbWUgPSAnZmt0YWJsZV9ma19ma2V5Jyk7CistLSByZXNldAorQUxURVIgVEFCTEUgRktUQUJM
RSBBTFRFUiBDT05TVFJBSU5UIGZrdGFibGVfZmtfZmtleSBOT1QgREVGRVJSQUJMRTsKIAogLS0g
dHJ5IGFkZGl0aW9uYWwgc3ludGF4CiBBTFRFUiBUQUJMRSBma3RhYmxlIEFMVEVSIENPTlNUUkFJ
TlQgZmt0YWJsZV9ma19ma2V5IE5PVCBERUZFUlJBQkxFOwotLSAKMi41My4wCgo=
--000000000000820e9d064bcb2d36
Content-Type: application/octet-stream; 
	name="tgdeferrable-tginitdeferred-not-restored.sql"
Content-Disposition: attachment; 
	filename="tgdeferrable-tginitdeferred-not-restored.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mm4poqow1>
X-Attachment-Id: f_mm4poqow1

LS0gQ2xlYW4gdXAgZXhpc3RpbmcgdGFibGVzCkRST1AgVEFCTEUgSUYgRVhJU1RTIGZrdGFibGU7
CkRST1AgVEFCTEUgSUYgRVhJU1RTIHBrdGFibGU7CgotLSAxLiBDcmVhdGUgdGFibGVzIGZvciB0
ZXN0aW5nCkNSRUFURSBUQUJMRSBwa3RhYmxlICgKICAgIGlkIElOVCBQUklNQVJZIEtFWQopOwoK
Q1JFQVRFIFRBQkxFIGZrdGFibGUgKAogICAgaWQgSU5UIFBSSU1BUlkgS0VZLAogICAgcGFyZW50
X2lkIElOVCwKICAgIC0tIERlZmluZSBhIGZvcmVpZ24ga2V5IHdpdGggREVGRVJSQUJMRSBJTklU
SUFMTFkgREVGRVJSRUQKICAgIENPTlNUUkFJTlQgZmtfdGVzdCBGT1JFSUdOIEtFWSAocGFyZW50
X2lkKSBSRUZFUkVOQ0VTIHBrdGFibGUoaWQpCiAgICBERUZFUlJBQkxFIElOSVRJQUxMWSBERUZF
UlJFRAopOwoKLS0gMi4gQ2hlY2sgaW5pdGlhbCBzdGF0ZSAoQm90aCBzaG91bGQgYmUgJ3QnKQpT
RUxFQ1QgCiAgICBjb25uYW1lLCAKICAgIGNvbmRlZmVycmFibGUsCiAgICBjb25kZWZlcnJlZApG
Uk9NIHBnX2NvbnN0cmFpbnQgCldIRVJFIGNvbm5hbWUgPSAnZmtfdGVzdCc7CgpTRUxFQ1QgCiAg
ICB0Z25hbWUsIAogICAgdGdkZWZlcnJhYmxlLAogICAgdGdpbml0ZGVmZXJyZWQKRlJPTSBwZ190
cmlnZ2VyIApXSEVSRSB0Z25hbWUgTElLRSAnUklfQ29uc3RyYWludFRyaWdnZXIlJzsKCi0tIDMu
IFRvZ2dsZSBOT1QgRU5GT1JDRUQgdGhlbiBiYWNrIHRvIEVORk9SQ0VECkFMVEVSIFRBQkxFIGZr
dGFibGUgQUxURVIgQ09OU1RSQUlOVCBma190ZXN0IE5PVCBFTkZPUkNFRDsKQUxURVIgVEFCTEUg
Zmt0YWJsZSBBTFRFUiBDT05TVFJBSU5UIGZrX3Rlc3QgRU5GT1JDRUQ7CgotLSA0LiBDaGVjayBz
dGF0ZSBhZnRlciB0b2dnbGUKLS0gQnVnOiBwZ19jb25zdHJhaW50IHJlbWFpbnMgJ3QnLCBidXQg
cGdfdHJpZ2dlciBpcyByZXNldCB0byAnZicKU0VMRUNUIAogICAgY29ubmFtZSwgCiAgICBjb25k
ZWZlcnJhYmxlLAogICAgY29uZGVmZXJyZWQKRlJPTSBwZ19jb25zdHJhaW50IApXSEVSRSBjb25u
YW1lID0gJ2ZrX3Rlc3QnOwoKU0VMRUNUIAogICAgdGduYW1lLCAKICAgIHRnZGVmZXJyYWJsZSwK
ICAgIHRnaW5pdGRlZmVycmVkCkZST00gcGdfdHJpZ2dlciAKV0hFUkUgdGduYW1lIExJS0UgJ1JJ
X0NvbnN0cmFpbnRUcmlnZ2VyJSc7CgotLSA1LiBWZXJpZnkgYWN0dWFsIGJlaGF2aW9yCi0tIElm
IHRoZSBidWcgZXhpc3RzLCBJTlNFUlQgZmFpbHMgaW1tZWRpYXRlbHkgZXZlbiBhZnRlciBTRVQg
Q09OU1RSQUlOVFMgREVGRVJSRUQKQkVHSU47ClNFVCBDT05TVFJBSU5UUyBma190ZXN0IERFRkVS
UkVEOwpJTlNFUlQgSU5UTyBma3RhYmxlIChpZCwgcGFyZW50X2lkKSBWQUxVRVMgKDEsIDk5OSk7
IAotLSBFeHBlY3RlZDogTm8gZXJyb3IgaGVyZSwgZGVsYXkgdmVyaWZpY2F0aW9uIHVudGlsIENP
TU1JVC4KLS0gQWN0dWFsOiBGYWlscyBpbW1lZGlhdGVseSBpZiB0Z2RlZmVycmFibGUgaXMgJ2Yn
LgpDT01NSVQ7CgotLSBDbGVhbiB1cApEUk9QIFRBQkxFIElGIEVYSVNUUyBma3RhYmxlOwpEUk9Q
IFRBQkxFIElGIEVYSVNUUyBwa3RhYmxlOwo=
--000000000000820e9d064bcb2d36--