Horton

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

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tender Wang <tndrwang@gmail.com>
Cc: Junwang Zhao <zhjwpku@gmail.com>, Jehan-Guillaume de Rorthais <jgdr@dalibo.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Alexander Lakhin <exclusion@gmail.com>, Baehler Thomas SBB CFF FFS <thomas.baehler2@sbb.ch>, Guillaume Lelarge <guillaume@lelarge.info>
Date: 2024-08-21T22:00:46Z
Lists: pgsql-hackers

Attachments

On 2024-Aug-19, Alvaro Herrera wrote:

> I haven't pushed it yet, mostly because of being unsure about not doing
> anything for the oldest branches (14 and back).

Last night, after much mulling on this, it occurred to me that one easy
way out of this problem for the old branches, without having to write
more code, is to simply remove the constraint from the partition when
it's detached (but only if they reference a partitioned relation).  It's
not a great solution, but at least we're no longer leaving bogus catalog
entries around.  That would be like the attached patch, which was cut
from 14 and applies cleanly to 12 and 13.  I'd throw in a couple of
tests and call it a day.


(TBH the idea of leaving the partition without a foreign key feels to me
like travelling in a car without a seat belt -- it feels instinctively
dangerous.  This is why I went such lengths to keep FKs on detach
initially.  But I'm not inclined to spend more time on this issue.
However ... what about fixing catalog content that's already broken
after past detach ops?)

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

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