Date: Fri, 7 Feb 2020 19:22:57 +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: <20200207192257.1145eadd@firost>
In-Reply-To: <20200207172751.GA26691@alvherre.pgsql>
References: <20200207171933.77aaaba6@firost>
	<20200207172751.GA26691@alvherre.pgsql>
Organization: Dalibo
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="MP_/Hh5OBOP+/TWFwYf/ezBKPvu"
Precedence: bulk

--MP_/Hh5OBOP+/TWFwYf/ezBKPvu
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Fri, 7 Feb 2020 14:27:51 -0300
Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

> On 2020-Feb-07, Jehan-Guillaume de Rorthais wrote:
> 
> > Maybe I would just add:
> > 
> >  /*
> >   * If this constraint has a parent constraint which we have not seen
> >   * yet, keep track of it for the second loop, below.
> > + * Tracking parent constraint allows to climb up to the top-level
> > + * level constraint and look for all possible relation referencing 
> > + * the partioned table.
> >   */  
> 
> LGTM.

Added.

> BTW I was thinking that perhaps it would make sense to go up all levels
> at once when we see a "parented" constraint; this would avoid having to
> restart several times when there's N-levels partitioning.  It might be
> an issue if pg_constraint is large, because, you see, there's a seqscan
> there!

Indeed. See v4 in attachment. It saves 3 seqscans during the whole tests we
added.

> (Maybe now's the time to add an index to confrelid, but of
> course only in master).  This probably doesn't matter much normally
> because nobody uses that many partition levels ...

I have a colleague that enjoys experimenting with limits. But I'm not sure I'll
have feedback from him before next minor release (next week?).

Regards,

--MP_/Hh5OBOP+/TWFwYf/ezBKPvu
Content-Type: text/x-patch
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename=0001-v4-Fix-TRUNCATE-on-a-partition-to-apply-CASCADE-to-part.patch