On 2024-Nov-05, Tom Lane wrote: > 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? Yes -- as far as I can tell, a DROP CONSTRAINT of the offending constraint is successful and leaves no unwanted detritus. Perhaps one more task for me is to figure out a way to get a list of all the constraints that are broken because of this ... let me see if I can figure that out. > 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. As as far as I can see, it works and no triggers are spuriously removed. > 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. Yeah, that's correct: adding the constraint again after removing its broken self detects that there are values violating the RI. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ Thou shalt check the array bounds of all strings (indeed, all arrays), for surely where thou typest "foo" someone someday shall type "supercalifragilisticexpialidocious" (5th Commandment for C programmers)