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-22T18:41:50Z
Lists: pgsql-hackers

Attachments

On 2024-Aug-22, Tender Wang wrote:

> I apply the v14 patch on branch REL_14_STABLE. I run this thread issue and I
> find below error.
> [...]
> ERROR:  cache lookup failed for constraint 16400
> 
> I haven't look into details to find out where cause above error.

Right, we try to drop the constraint twice.  We can dodge this by
collecting all constraints to drop in the loop and process them in a
single performMultipleDeletions, as in the attached v14-2.

TBH I think it's a bit infuriating that we lose the constraint (which
was explicitly declared) because of ATTACH/DETACH.  So the behavior of
v15 and above is better.

> By the way, I run above SQL sequences on REL_14_STABLE without your
> partch.  I didn't find reporting error, and running oidjoins.sql
> didn't report warnings.  Do I miss something?

I think the action triggers are missing, so if you keep rows in the r_1
table after you've detached them, you can still delete them from the
referenced table p, instead of getting the error that you should get.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle."  (Larry Wall, Apocalypse 6)

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