pgsql: Allow NOT VALID foreign key constraints on partitioned tables
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.
Author: Amul Sul <sulamul@gmail.com>
Discussion: /messages/by-id/CAAJ_b96Bp=-ZwihPPtuaNX=SrZ0U6ZsXD3+fgARO0JuKa8v2jQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/b663b9436e7509b5e73c8c372539f067cd6e66c1
Modified Files
--------------
doc/src/sgml/ref/alter_table.sgml | 2 -
src/backend/commands/tablecmds.c | 150 +++++++++++++++++++++---------
src/test/regress/expected/foreign_key.out | 97 +++++++++++++++++--
src/test/regress/sql/foreign_key.sql | 72 +++++++++++++-
4 files changed, 263 insertions(+), 58 deletions(-)
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
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] /messages/by-id/CAAJ_b974U3Vvf-qGwFyZ73DFHqyFJP9TOmuiXR2Kp8KVcJtP6w@mail.gmail.com
Regards,
Amul