Re: partitioned tables referenced by FKs

On Thu, Mar 14, 2019 at 1:40 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> 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.

This made me laugh.

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

But there are other cases in which that logic doesn't hold. I can't
quite wrap my brain around the exact criteria at the moment. I think
it's something like: the partitioning columns of the referring table
are a prefix of the foreign key columns, and the opfamilies match, and
likewise for the referenced table. And the partition bounds match up
well enough. And maybe some other things.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company