Re: partitioned tables referenced by FKs

On 2019-Mar-14, Robert Haas wrote:

> On Thu, Mar 14, 2019 at 1:40 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:

> > 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. Have a
> > look at the triggers in pg_trigger that appear when you do "references
> > pk1" vs. when you do "references pk". The constraint itself might
> > appear identical, but the former adds check triggers that are not
> > present for the latter.
>
> It's probably not uncommon to have FKs between compatibly-partitioned
> tables, though, and in that case they are really equivalent. For
> example, if you have an orders table and an order_lines table and the
> latter has an FK pointing at the former, and both are partitioned on
> the order number, then it must be that every order_line references an
> order in the matching partition. Even if it's not practical to use
> the FK itself, it's a good excuse for skipping any validation scan you
> otherwise might have performed.

Well, I suppose that can be implemented as an optimization on top of
what we have, but I think that we should first get this feature right,
and later we can see about improving it. In any case, since the RI
queries are run via SPI, any unnecessary partitions should get pruned by
partition pruning based on each partition's constraint. So I'm not so
certain that this is a problem worth spending much time/effort/risk of
bugs on.

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