Subject: Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

From: Adrian Klaver <[email-masked]>
Date: Tue, 26 Nov 2024 08:56:20 -0800

On 11/26/24 01:59, Paul Foerster wrote:
> 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.

Did you commit the statements?

Are you using concurrent sessions to do this?

When you run the query again do you get the same two statements?

> 
> 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
> 

-- 
Adrian Klaver
[email-masked]

---

From: Paul Foerster <[email-masked]>
Date: Tue, 26 Nov 2024 20:02:33 +0100

Hi Adrian,

> On 26 Nov 2024, at 17:56, Adrian Klaver <[email-masked]> wrote:
> 

Yes. I have autocommit on, the psql default.

> Are you using concurrent sessions to do this?

No. I do this in one session. 1. select, 2. drop, 3. add, 4. select.

> When you run the query again do you get the same two statements?

Yes. I can repeat the above 4 steps as much as I want. The result remains the same. I would have expected to have an empty result doing the final repeated select, but it shows exactly the same output.

Cheers,
Paul

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Nov 2024 16:25:32 -0500

> Yes. I can repeat the above 4 steps as much as I want. The result remains the same. I would have expected to have an empty result doing the final repeated select, but it shows exactly the same outp…

I would have expected an empty result too.  Can you confirm that
p_ci_pipelines used to be a partition of something?  Can you show us
the full DDL (or psql \d+ output) for the partitioned table it
used to be part of, and for that matter also for p_ci_pipelines?
Did the FK used to reference the whole partitioned table, or just
this partition?

I'm suspicious that our repair recipe might not have accounted
for self-reference FKs fully, but that's just a gut feeling at
this point.

			regards, tom lane