Re: partitioned tables referenced by FKs

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: partitioned tables referenced by FKs
Date: 2019-03-26 21:39:24
Message-ID: 20190326213924.GA2322@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hello Amit

On 2019-Mar-26, Amit Langote wrote:

> + Oid objectClass = getObjectClass(thisobj);
>
> I guess you meant to use ObjectClass, not Oid here.

Absolutely.

> Tested 0002 a bit more and found some problems.

Thanks for the thorough testing and bug analysis! It was spot-on. I've
applied your two proposed fixes, as well as added a new test setup that
covers both these bugs. The attached set is rebased on 7c366ac969ce.

In the course of testing those fixes, I noticed a stupid bug in
partitioned PKs -- one of the CompareIndexInfo was passing the wrong
attmap length. 0001 here fixes that, but it's really a backpatchable
bugfix so I'll put it on pg11 and master ahead of time.

> > As I said before, I'm thinking of getting rid of the whole business of
> > checking partitions on the referenced side of an FK at DROP time, and
> > instead jut forbid the DROP completely if any FKs reference an ancestor
> > of that partition.
>
> Will that allow `DROP TABLE parted_pk CASCADE` to succeed even if
> partitions still contain referenced data? I suppose that's the example
> you cited upthread as a bug that remains to be solved.

That's the idea, yes, it should do that: only allow a DROP of a
partition referenced by an FK if the topmost constraint is also being
dropped. Maybe this means I need to get rid of 0002 completely. But I
haven't got to doing that yet.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v8-0001-fix-CompareIndexInfo-related-bugs.patch text/x-diff 2.2 KB
v8-0002-Rework-deleteObjectsInList-to-allow-objtype-speci.patch text/x-diff 2.2 KB
v8-0003-support-FKs-referencing-partitioned-tables.patch text/x-diff 95.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ryan Lambert 2019-03-26 22:17:49 Re: Fix XML handling with DOCTYPE
Previous Message Andrew Dunstan 2019-03-26 21:25:16 Re: pgsql: Get rid of backtracking in jsonpath_scan.l