From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: partitioned tables referenced by FKs
Date: Thu, 14 Mar 2019 16:36:35 -0300
Message-ID: <20190314193635.GA8910@alvherre.pgsql> (raw)
In-Reply-To: <CA+TgmoZmTQ0g9MpCQoJSKRO2dSx6q64YLg9R=iNbEFhZ-KK2XQ@mail.gmail.com>
On 2019-Mar-14, Robert Haas wrote:
> On Thu, Mar 14, 2019 at 1:40 PM Alvaro Herrera <alvherre@2ndquadrant.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, robertmhaas@gmail.com, Langote_Amit_f8@lab.ntt.co.jp
Subject: Re: partitioned tables referenced by FKs
In-Reply-To: <20190314193635.GA8910@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox