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 <amitlangote09@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-19T12:13:11Z
Lists: pgsql-hackers 
On 2019-Mar-19, Amit Langote wrote:

> On Tue, Mar 19, 2019 at 8:49 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > On 2019-Mar-19, Amit Langote wrote:
> >
> > > Will it suffice or be OK if we skipped invoking the pre-drop callback for
> > > objects that are being "indirectly" dropped?  I came up with the attached
> > > patch to resolve this problem, if that idea has any merit.  We also get
> > > slightly better error message as seen the expected/foreign_key.out changes.
> >
> > I don't think this works ... consider putting some partition in a
> > different schema and then doing DROP SCHEMA CASCADE.
> 
> Ah, I did test DROP SCHEMA CASCADE, but only tested putting the top
> table into the schema, not a partition.

:-(

I think this is fixable by using a two-step strategy where we first
compile a list of constraints being dropped, and in the second step we
know to ignore those when checking partitions that are being dropped.
Now, maybe the order of objects visited guarantees that the constraint
is seen (by drop) before each corresponding partition, and in that case
we don't need two steps, just one.  I'll have a look at 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