Re: pgsql: Allow NOT VALID foreign key constraints on partitioned tables

Hi Alvaro.

On Thu, Jan 23, 2025 at 02:59:38PM +0000, Alvaro Herrera wrote:
> Allow NOT VALID foreign key constraints on partitioned tables
>
> This feature was intentionally omitted when FKs were first implemented
> for partitioned tables, and had been requested a few times; the
> usefulness is clear.
>
> Validation can happen for each partition individually, which is useful
> to contain the number of locks held and the duration; or it can be
> executed for the partitioning hierarchy as a single command, which
> validates all child constraints that haven't been validated already.
>
> This is also useful to implement NOT ENFORCED constraints on top.

morepork at [1] is telling that these two queries are missing an ORDER
BY to ensure a proper ordering of the output generated:
+-- Constraint will be invalid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;
[...]
+-- All constraints are now valid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;

[1]: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=morepork&dt=2025-01-23%2023%3A35%3A57

Thanks,
--
Michael