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>
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
In-reply-to: <CAHewXNko_dibtzFYR7H-rRe-Fii3TiPVvdeu-GTzBN8oOMBsfQ@mail.gmail.com>
References: <CAHewXNk8hn_Kq=3gb583O1U1WU=T+VMRF8NDZfTa35wxcJ+xcA@mail.gmail.com> <202410280916.7zm2gv25farn@alvherre.pgsql> <CAHewXNko_dibtzFYR7H-rRe-Fii3TiPVvdeu-GTzBN8oOMBsfQ@mail.gmail.com>
Comments: In-reply-to Tender Wang <tndrwang@gmail.com>
	message dated "Mon, 28 Oct 2024 19:42:54 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3097657.1730840765.1@sss.pgh.pa.us>
Date: Tue, 05 Nov 2024 16:06:05 -0500
Message-ID: <3097658.1730840765@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3097658.1730840765%40sss.pgh.pa.us>
Precedence: bulk

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