Re: FK violation in partitioned table after truncating a referenced partition

On Fri, 7 Feb 2020 14:27:51 -0300
Alvaro Herrera <alvherre(at)2ndquadrant(dot)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,