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-18T14:02:15Z
Lists: pgsql-hackers 
Hi Amit

On 2019-Mar-18, Amit Langote wrote:

> On 2019/03/15 2:31, Alvaro Herrera wrote:
> > Once I was finished, fixed bugs and tested it, I realized that that was
> > a stupid thing to have done -- because THOSE ARE DIFFERENT CONSTRAINTS.
> > When you say "fk (a) references pk1" you're saying that all the values
> > in fk(a) must appear in pk1.  OTOH when you say "fk references pk" you
> > mean that the values might appear anywhere in pk, not just pk1.
> 
> Sure, then just drop the check trigger that queries only pk1, in favor of
> one that checks pk, that's already in place due to the parent constraint.
> Actually, if one doesn't drop it (that is, by way of dropping the
> constraint that created it), they won't be able to insert into fk anything
> but the subset of rows that pk1 allows as a partition;

That's true, and it is the correct behavior.  What they should be doing
(to prevent the insertion into the referencing relation from failing all
the time) is drop the constraint, as you suggest.

Converting the constraint so that it refers to a completely different
set of permitted values is second-guessing the user about what they
wanted to do; what if we get it wrong, and they really wanted the FK to
reference just exactly the subset of values that they specified, and not
a much larger superset of that?

-- 
Á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