Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\))
Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH
 PARTITION/DETACH PARTITION
From: Paul Foerster <paul.foerster@gmail.com>
In-Reply-To: <202411291715.lwona3kp2xvv@alvherre.pgsql>
Date: Fri, 29 Nov 2024 21:24:17 +0100
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Adrian Klaver <adrian.klaver@aklaver.com>,
 pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <41055379-D0D4-487D-9D8D-A624B79FEA56@gmail.com>
References: <202411291715.lwona3kp2xvv@alvherre.pgsql>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Archived-At: <https://www.postgresql.org/message-id/41055379-D0D4-487D-9D8D-A624B79FEA56%40gmail.com>
Precedence: bulk

Hi Alvaro,

> On 29 Nov 2024, at 18:15, Alvaro Herrera <alvherre@alvh.no-ip.org> =
wrote:
>=20
> This all was to say that the query in the release notes is undoubtedly
> wrong.  After thinking some more about it, I think the fix is to add 1
> to the number of constraints:
>=20
> SELECT conrelid::pg_catalog.regclass AS "constrained table",
>       conname AS constraint,
>       confrelid::pg_catalog.regclass AS "references",
>       pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
>                         conrelid::pg_catalog.regclass, conname) AS =
"drop",
>       pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
>                         conrelid::pg_catalog.regclass, conname,
>                         pg_catalog.pg_get_constraintdef(oid)) AS "add"
> FROM pg_catalog.pg_constraint c
> WHERE contype =3D 'f' AND conparentid =3D 0 AND
>   (SELECT count(*) FROM pg_catalog.pg_constraint c2
>    WHERE c2.conparentid =3D c.oid) <>
>   ((SELECT count(*) FROM pg_catalog.pg_inherits i
>    WHERE (i.inhparent =3D c.conrelid OR i.inhparent =3D c.confrelid) =
AND
>      EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
>              WHERE partrelid =3D i.inhparent)) +
>    CASE when pg_partition_root(conrelid) =3D confrelid THEN 1 ELSE 0 =
END);
>=20
> This reports case 2 as OK and case 1 as bogus, as should be.  I tried
> adding more partitions and this seems to hold correctly.  I was afraid
> though that this would fail if we create an FK in an intermediate =
level
> of the partition hierarchy ... but experimentation doesn't seem to =
give
> that result.  I've run out of time today to continue to look though.

Thanks very much for this really detailed analysis and sharing your =
insights. I'll give the new query a try on Monday when I'm back at work. =
Do I also need to recheck all other databases with this new query which =
didn't report anything with the original query?

> =C3=81lvaro Herrera               48=C2=B001'N 7=C2=B057'E  =E2=80=94  =
https://www.EnterpriseDB.com/
> "La vida es para el que se aventura"

You're located in the middle of the forest east of Freiburg im Breisgau =
in Germany? =F0=9F=A4=A3

Cheers,
Paul=