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

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
adrian(dot)klaver(at)aklaver(dot)com