Subject: Allow NOT VALID foreign key constraints on partitioned tables.

From: Amul Sul <[email-masked]>
Date: Thu, 2 Jan 2025 22:19:13 +0530

Hi,

While working on NOT ENFORCED constraints[1], which are by default marked as NOT
VALID, I encountered an error when adding a NOT ENFORCED foreign key (FK)
constraint to a partitioned table [2]. Alvaro also confirmed off-list that NOT
VALID FK constraints have not yet been implemented. This patch addresses that
gap.

When adding a new FK constraint or attaching a partitioned table, where
matching FK constraints are merged, we allow the parent constraint to be NOT
VALID while the child constraint remains VALID, which is harmless. However, the
reverse scenario -- where the parent constraint is VALID and the child is NOT
VALID -- is incorrect. To address this, when merging a NOT VALID FK constraint
from the child with a VALID parent constraint, it implicitly validates the
child constraint against its existing data and marks it as VALID. This behavior
aligns with adding a new FK constraint directly to the child table, which would
also validate the existing data.

The 0001 patch focuses on code refactoring and does not modify or introduce new
behaviors. It splits ATExecValidateConstraint() into two separate functions for
handling FK and CHECK constraints. For this feature, I wanted to reuse the FK
validation logic and make it recursive for partitioned tables, necessitating
its separation. Although CHECK constraint validation isn't required for this
work, separating it simplifies ATExecValidateConstraint() and prepares the
codebase for adding support for other constraint types (e.g., NOT NULL) in the
future. Additional changes in the refactoring include renaming the variable
tuple to contuple within these functions, duplicating a few lines of code that
update pg_constraint.convalidated, and running pgindent, which rearranged the
code and comments. I hope the duplication is not a significant concern.

Please review the attached patches. Any comments or suggestions would
be greatly appreciated. Thank you!

1] https://postgr.es/m/[email-masked]
2] https://postgr.es/m/[email-masked]

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

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 02 Jan 2025 19:40:25 +0100

> also validate the existing data.

Hmm, I'm not sure about this, which may cause surprising delays. Maybe it would be better that the operation fails with an error, so that the user can do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the partitions have been so processed.