Horton

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Tender Wang <tndrwang@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, Jehan-Guillaume de Rorthais <jgdr@dalibo.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Guillaume Lelarge <guillaume@lelarge.info>
Date: 2024-11-05T21:06:05Z
Lists: pgsql-hackers 
I'm trying to write release notes for commits 53af9491a et al,
and it seems to me that we need to explain how to get out of
the mess that would be left behind by the old DETACH code.
There's no hint about that in the commit message :-(

Clearly, if you have now-inconsistent data, there's little
help for that but to manually fix the inconsistencies.
What I am worried about is how to get to a state where you
have correct catalog entries for the constraint.

Will ALTER TABLE DROP CONSTRAINT on the now stand-alone table
work to clean out the old catalog entries for the constraint?
I'm worried that it will either fail, or go through but remove
triggers on the referenced table that we still need for the
original partitioned table.  If that doesn't work I think we had
better create a recipe for manually removing the detritus.

Once the old entries are gone it should be possible to do ALTER TABLE
ADD CONSTRAINT (with an updated server), and that would validate
your data.  It's the DROP CONSTRAINT part that worries me.

			regards, tom lane

Commits

  1. Fix some more bugs in foreign keys connecting partitioned tables

  2. Restructure foreign key handling code for ATTACH/DETACH

  3. Create foreign key triggers in partitioned tables too