Date: Fri, 7 Feb 2020 23:03:14 +0100
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: FK violation in partitioned table after truncating a referenced
 partition
Message-ID: <20200207230314.350a59bc@firost>
In-Reply-To: <20200207201948.GA16783@alvherre.pgsql>
References: <20200207192257.1145eadd@firost>
	<20200207201948.GA16783@alvherre.pgsql>
Organization: Dalibo
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

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,