From: Paul Foerster <paul.foerster@gmail.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\))
Subject: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH
 PARTITION
Message-Id: <5456A1D0-CD47-4315-9C65-71B27E7A2906@gmail.com>
Date: Tue, 26 Nov 2024 10:59:40 +0100
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/5456A1D0-CD47-4315-9C65-71B27E7A2906%40gmail.com>
Precedence: bulk

Hi,

I have a question regarding the recent security update for PostgreSQL =
15.

We have a gitlab database. It used to run on the PostgreSQL 15.8 =
software. I updated from 15.8 to 15.10 and executed the corrective =
actions as outlined in:

=
https://www.postgresql.org/about/news/postgresql-171-165-159-1414-1317-and=
-1221-released-2955/

I executed "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));" =
which gave the result below:


-[ RECORD 1 =
]-----+-------------------------------------------------------------------=
--------------------------------------------------------------------------=
----------------------------------------------------------------
constrained table | p_ci_pipelines
constraint        | fk_262d4c2d19_p
references        | p_ci_pipelines
drop              | alter table p_ci_pipelines drop constraint =
fk_262d4c2d19_p;
add               | alter table p_ci_pipelines add constraint =
fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, =
auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON =
UPDATE CASCADE ON DELETE SET NULL;

I then executed the two alter table statements without any problem. No =
error was reported and all seems ok.

Now, if I execute the query to find the constraints again, I would =
expect the result to be empty. But it is not.

Why is that and what am I supposed to do? Is the problem fixed now or is =
it still pending? Any ideas would be greatly appreciated.

Cheers
Paul=