pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Allow NOT VALID foreign key constraints on partitioned tables
3+ messages / 3 participants
[nested] [flat]
* pgsql: Allow NOT VALID foreign key constraints on partitioned tables
@ 2025-01-23 14:59 Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-01-24 06:48 ` Re: pgsql: Allow NOT VALID foreign key constraints on partitioned tables Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 3+ messages in thread
From: Álvaro Herrera @ 2025-01-23 14:59 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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: https://postgr.es/m/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(-)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: Allow NOT VALID foreign key constraints on partitioned tables
2025-01-23 14:59 pgsql: Allow NOT VALID foreign key constraints on partitioned tables Álvaro Herrera <alvherre@alvh.no-ip.org>
@ 2025-01-24 06:48 ` Michael Paquier <michael@paquier.xyz>
2025-01-24 10:57 ` Re: pgsql: Allow NOT VALID foreign key constraints on partitioned tables Amul Sul <sulamul@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Paquier @ 2025-01-24 06:48 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-committers@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: Allow NOT VALID foreign key constraints on partitioned tables
2025-01-23 14:59 pgsql: Allow NOT VALID foreign key constraints on partitioned tables Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-01-24 06:48 ` Re: pgsql: Allow NOT VALID foreign key constraints on partitioned tables Michael Paquier <michael@paquier.xyz>
@ 2025-01-24 10:57 ` Amul Sul <sulamul@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Amul Sul @ 2025-01-24 10:57 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-01-24 10:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-23 14:59 pgsql: Allow NOT VALID foreign key constraints on partitioned tables Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-01-24 06:48 ` Michael Paquier <michael@paquier.xyz>
2025-01-24 10:57 ` Amul Sul <sulamul@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox