MIME-Version: 1.0
From: Amul Sul <sulamul@gmail.com>
Date: Thu, 16 Jan 2025 19:23:31 +0530
Message-ID: <CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com>
Subject: Bug in detaching a partition with a foreign key.
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000d130d7062bd320d2"
Archived-At: <https://www.postgresql.org/message-id/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh%2B0aMv-w64QcGrLhCOZZ6p%2Bg%40mail.gmail.com>
Precedence: bulk

--000000000000d130d7062bd320d2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

While detaching a partition with a foreign key referencing a partitioned ta=
ble,
I am getting the following error:

ERROR:  could not find ON INSERT check triggers of foreign key constraint 1=
6636

I haven=E2=80=99t looked closely at what the issue might be, but it seems t=
he logic
inside DetachPartitionFinalize(), which is supposed to iterate over inherit=
ed
foreign keys, is lacking. The attached trial patch fixes the issue for me, =
but
I=E2=80=99m not sure if it=E2=80=99s the correct fix. I=E2=80=99ll take a c=
loser look later.

Here is the test:

CREATE TABLE bar(id int PRIMARY KEY) PARTITION BY RANGE(id);
CREATE TABLE bar_p0 PARTITION OF bar FOR VALUES FROM (0) TO (100);

CREATE TABLE foo(id int) PARTITION BY RANGE(id);

CREATE TABLE foo_p0 PARTITION OF foo FOR VALUES FROM (0) TO (100)
PARTITION BY RANGE(id);
CREATE TABLE foo_p0_p0 PARTITION OF foo_p0 FOR VALUES FROM (0) TO (100);
ALTER TABLE foo_p0 ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES =
bar;

ALTER TABLE foo DETACH PARTITION foo_p0;

--
Regards,
Amul Sul
EDB: http://www.enterprisedb.com

--000000000000d130d7062bd320d2
Content-Type: application/x-patch; name="trial.patch"
Content-Disposition: attachment; filename="trial.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m5ze05qd0>
X-Attachment-Id: f_m5ze05qd0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3RhYmxlY21kcy5jIGIvc3JjL2JhY2tl
bmQvY29tbWFuZHMvdGFibGVjbWRzLmMKaW5kZXggYzQyYTc0MGNjZWYuLmNhY2RiOWI0NzVkIDEw
MDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy90YWJsZWNtZHMuYworKysgYi9zcmMvYmFj
a2VuZC9jb21tYW5kcy90YWJsZWNtZHMuYwpAQCAtMTk5MjYsNiArMTk5MjYsNyBAQCBEZXRhY2hQ
YXJ0aXRpb25GaW5hbGl6ZShSZWxhdGlvbiByZWwsIFJlbGF0aW9uIHBhcnRSZWwsIGJvb2wgY29u
Y3VycmVudCwKIAlIZWFwVHVwbGUJdHVwbGUsCiAJCQkJbmV3dHVwbGU7CiAJUmVsYXRpb24JdHJp
Z3JlbCA9IE5VTEw7CisJTGlzdAkgICAqZmtzaWRzOwogCiAJaWYgKGNvbmN1cnJlbnQpCiAJewpA
QCAtMTk5NDYsNiArMTk5NDcsMTEgQEAgRGV0YWNoUGFydGl0aW9uRmluYWxpemUoUmVsYXRpb24g
cmVsLCBSZWxhdGlvbiBwYXJ0UmVsLCBib29sIGNvbmN1cnJlbnQsCiAJZmtzID0gY29weU9iamVj
dChSZWxhdGlvbkdldEZLZXlMaXN0KHBhcnRSZWwpKTsKIAlpZiAoZmtzICE9IE5JTCkKIAkJdHJp
Z3JlbCA9IHRhYmxlX29wZW4oVHJpZ2dlclJlbGF0aW9uSWQsIFJvd0V4Y2x1c2l2ZUxvY2spOwor
CisJLyogQ29sbGVjdCBhbGwgdGhlIGNvbnN0cmFpbnQgaWRzICovCisJZm9yZWFjaChjZWxsLCBm
a3MpCisJCWZrc2lkcyA9IGxhcHBlbmRfb2lkKGZrc2lkcywgbGZpcnN0X25vZGUoRm9yZWlnbktl
eUNhY2hlSW5mbywgY2VsbCktPmNvbm9pZCk7CisKIAlmb3JlYWNoKGNlbGwsIGZrcykKIAl7CiAJ
CUZvcmVpZ25LZXlDYWNoZUluZm8gKmZrID0gbGZpcnN0KGNlbGwpOwpAQCAtMTk5NjEsNyArMTk5
NjcsOCBAQCBEZXRhY2hQYXJ0aXRpb25GaW5hbGl6ZShSZWxhdGlvbiByZWwsIFJlbGF0aW9uIHBh
cnRSZWwsIGJvb2wgY29uY3VycmVudCwKIAogCQkvKiBjb25zaWRlciBvbmx5IHRoZSBpbmhlcml0
ZWQgZm9yZWlnbiBrZXlzICovCiAJCWlmIChjb25mb3JtLT5jb250eXBlICE9IENPTlNUUkFJTlRf
Rk9SRUlHTiB8fAotCQkJIU9pZElzVmFsaWQoY29uZm9ybS0+Y29ucGFyZW50aWQpKQorCQkJIU9p
ZElzVmFsaWQoY29uZm9ybS0+Y29ucGFyZW50aWQpIHx8CisJCQlsaXN0X21lbWJlcl9vaWQoZmtz
aWRzLCBjb25mb3JtLT5jb25wYXJlbnRpZCkpCiAJCXsKIAkJCVJlbGVhc2VTeXNDYWNoZShjb250
dXApOwogCQkJY29udGludWU7Cg==
--000000000000d130d7062bd320d2--