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

On Fri, Jan 24, 2025 at 12:19 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> 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 for reporting. I’ve posted the patch in the other thread [1].


1] http://postgr.es/m/CAAJ_b974U3Vvf-qGwFyZ73DFHqyFJP9TOmuiXR2Kp8KVcJtP6w@mail.gmail.com


Regards,
Amul