Horton

Re: partitioned tables referenced by FKs

Alvaro Herrera <alvherre@2ndquadrant.com>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-26T21:39:24Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Support foreign keys that reference partitioned tables

  2. Fix partitioned index creation bug with dropped columns

  3. Improve psql's \d display of foreign key constraints

  4. Fix dependency recording bug for partitioned PKs

  5. Add index_get_partition convenience function

  6. Restore RI trigger sanity check

  7. Foreign keys on partitioned tables