Feedback-ID: i0fe9450f:Fastmail
Date: Fri, 24 Jan 2025 15:48:54 +0900
From: Michael Paquier <michael@paquier.xyz>
To: =?iso-8859-1?Q?=C1lvaro?= Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Allow NOT VALID foreign key constraints on partitioned
 tables
Message-ID: <Z5M31tXuutq405K0@paquier.xyz>
References: <E1taygE-0036kE-Fh@gemulon.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="R8LLOdefNA/arVOV"
Content-Disposition: inline
In-Reply-To: <E1taygE-0036kE-Fh@gemulon.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/Z5M31tXuutq405K0%40paquier.xyz>
Precedence: bulk


--R8LLOdefNA/arVOV
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Hi Alvaro.

On Thu, Jan 23, 2025 at 02:59:38PM +0000, Alvaro Herrera wrote:
> Allow NOT VALID foreign key constraints on partitioned tables
>=20
> This feature was intentionally omitted when FKs were first implemented
> for partitioned tables, and had been requested a few times; the
> usefulness is clear.
>=20
> 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.
>=20
> 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 =3D 'fk_not=
partitioned_fk'::regclass;=20
[...]
+-- All constraints are now valid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid =3D 'fk_not=
partitioned_fk'::regclass;=20

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

Thanks,
--
Michael

--R8LLOdefNA/arVOV
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmeTN9YACgkQnvQgOdby
QH3t/Q/+KdXtnQn2v9DX0yHhTUxfqC0O41U5XE1nWvWgAc4OERrCRuBeAgQdM1Ma
tUnFTp7JmXv27MQkXmpee4wy21ETSSWYqLQRW+Ow2UAgQ9oSiZgJqAoM6dDX1Mg1
nScA8wHHBYJ6FjNMR5mvoiMhL71p7ukChbNWH1cx8TYNXMXPb4aqClAlTSj6JChD
D0AtcwO9GV2J9ZdpbnPdh5fGC8OCe6JObJxDBjJUlaZaADm9xEBOzNXb1BDY1MYB
MUGH2A12B+Otr0Uj6DG4wLn14p3vWu6qS79MAWZAIVpxdwsPJx+pwS4xwObBjb8C
gTkgaz+DP0Durus35tFQKMoIIAdNCnbJL/Nq6cLo14z5Jv6YvjAYXdVB339DCkJM
hjoQZdqYUgluA6j7cXKvFovkdEJ3MLuZZMl9VNC3cs4NfXbSCJyXvd6mEO/J0138
1Ojghy4lVI70rjmlM/9A6HE8EB8UQE2KAzmcI6l3EmEqkBK5rjmLdl3Z+MHfBN0x
fy09uC4o5f0gN920gvWktB92rbixJB6GsSRMsaxJr+Hyuewr1gk69R7GeSXDTYJ8
0ZSQY33LvA6llJlRK3YEx2hD1VuDOI2DCVYeX3sCQZ4zKf25803WqcCM08rj6YTi
zmQgrpsr7OX8L5zgZMkTn9XGogtL1DN1bgfT3TmUJZ0AfRpYeps=
=ulYi
-----END PGP SIGNATURE-----

--R8LLOdefNA/arVOV--