public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul Foerster <[email protected]>
To: pgsql-general list <[email protected]>
Subject: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
Date: Tue, 26 Nov 2024 10:59:40 +0100
Message-ID: <[email protected]> (raw)

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 = 'f' AND conparentid = 0 AND (SELECT count(*) FROM pg_catalog.pg_constraint c2 WHERE c2.conparentid = c.oid) <> (SELECT count(*) FROM pg_catalog.pg_inherits i WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table WHERE partrelid = 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox