Re: FK violation in partitioned table after truncating a referencedpartition - Mailing list pgsql-bugs

From Jehan-Guillaume de Rorthais
Subject Re: FK violation in partitioned table after truncating a referencedpartition
Date
Msg-id 20200207230314.350a59bc@firost
Whole thread Raw
In response to Re: FK violation in partitioned table after truncating a referencedpartition  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-bugs
On Fri, 7 Feb 2020 17:19:48 -0300
Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

> There's another key point I forgot -- which is that we only need to
> search for constraints on the topmost partitioned table, not each of its
> partitions.  The reason is that pg_constraint rows exist on the other
> side that reference that relation, for each partition on the other side.

Yes, I figured this as well when drawing things during debug time.

By this time, I kept it this way because I wasn't sure about potential
complications with sub-partitioning and FK to sub-partition only.


> So we can do this:
[...]

> that is, keep appending to the parent_cons list, and not touch the oids
> list, until we get to the top of the hierarchy.  Then when we redo the
> first loop, we'll get all partitions on the other side because they all
> have pg_constraint rows that reference the topmost rel.  (That is to
> say, all the intermediate-partition OIDs should be useless in the 'oids'
> list anyway.)

It makes the oids list smaller (depending on the partitioning depth). As it is
scanned for each FK in pg_constraint, it surely squeeze some more time.

I'll stick around irw the other FK violation thread. Please, keep me in the
loop.

Thank you for the discussion and commit.

Regards,



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16250: As the owner of a database, I can't CREATE EXTENSION postgis;
Next
From: Jehan-Guillaume de Rorthais
Date:
Subject: Re: Another FK violation when referencing a multi-level partitionedtable