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
thecorrective 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.regclassAS "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
WHEREcontype = '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)
ANDEXISTS (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
DELETESET 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.klaver@aklaver.com
