public inbox for [email protected]help / color / mirror / Atom feed
Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION 3+ messages / 3 participants [nested] [flat]
* Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION @ 2024-11-26 16:56 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Klaver @ 2024-11-26 16:56 UTC (permalink / raw) To: Paul Foerster <[email protected]>; pgsql-general list <[email protected]> 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 protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION @ 2024-11-26 19:02 Paul Foerster <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Paul Foerster @ 2024-11-26 19:02 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[email protected]> Hi Adrian, > On 26 Nov 2024, at 17:56, Adrian Klaver <[email protected]> wrote: > > Did you commit the statements? 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION @ 2024-11-26 21:25 Tom Lane <[email protected]> parent: Paul Foerster <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2024-11-26 21:25 UTC (permalink / raw) To: Paul Foerster <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general list <[email protected]> Paul Foerster <[email protected]> writes: >> On 26 Nov 2024, at 17:56, Adrian Klaver <[email protected]> wrote: >> 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. 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-26 21:25 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-11-26 16:56 Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION Adrian Klaver <[email protected]> 2024-11-26 19:02 ` Paul Foerster <[email protected]> 2024-11-26 21:25 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox