Date: Mon, 21 Mar 2022 11:36:34 +0100
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Arne Roland <A.Roland@index.de>
Cc: Christophe Courtois <christophe.courtois@dalibo.com>, pgsql-hackers
 <pgsql-hackers@postgresql.org>
Subject: Re: Detaching a partition with a FK on itself is not possible
Message-ID: <20220321113634.68c09d4b@karst>
In-Reply-To: <666ad9554f9d4781aa45c4613544075d@index.de>
References: <06c75996-f72b-e57b-0efe-31e74a475c39@dalibo.com>
	<666ad9554f9d4781aa45c4613544075d@index.de>
Organization: Dalibo
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20220321113634.68c09d4b%40karst>
Precedence: bulk

Hi,

On Thu, 17 Mar 2022 17:58:04 +0000
Arne Roland <A.Roland@index.de> wrote:

> I don't think this a bug, but a feature request. I therefore think hackers
> would be more appropriate.

+1

I changed the list destination

> I don't see how an additional syntax to modify the constraint should help.

Me neiher.

> If I'd want to fix this, I'd try to teach the detach partition code about
> self referencing foreign keys. It seems to me like that would be the cleanest
> solution, because the user doesn't need to care about this at all.

Teaching the detach partition about self referencing means either:

* it's safe to remove the FK
* we can rewrite the FK for self referencing

Both solution are not ideal from the original schema and user perspective.

Another solution could be to teach the create partition to detect a self
referencing FK and actually create a self referencing FK, not pointing to the
partitioned table, and of course issuing a NOTICE to the client.