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

On 2020-Feb-07, Jehan-Guillaume de Rorthais wrote:

> Well, when reading myself, I found a bug in my algorithm. When looking for
> parent constraints harvested during the first loop, I wasn't looking on
> pg_contraint.oid, but on conparentid again. So instead of gathering parent
> constraints to add the parent relation to the list of oids, I was only adding
> siblings constraints. Here the fix:
> 
>     ScanKeyInit(&key,
>   - 			Anum_pg_constraint_conparentid,
>   + 			Anum_pg_constraint_oid
>     			BTEqualStrategyNumber, F_OIDEQ,
>     			ObjectIdGetDatum(parent));
>     
>   -  fkeyScan = systable_beginscan(fkeyRel, ConstraintParentIndexId,
>   +  fkeyScan = systable_beginscan(fkeyRel, ConstraintOidIndexId,
>     							   true, NULL, 1,
>     &key);

Doh, of course.  I should have seen that.

Here's another take at the formulation; IMO the loop is more obvious
this way, with a flag to restart from the top rather than keeping track
of the list length.  But essentially this is your algorithm.

I couldn't find any fault in this.  It would be nice if the cascaded
truncation was more precise, ie. only truncate the referencing
partitions that overlap the ranges covered by the referenced partition
being truncated.  But that seems more difficult to achieve, as well as
less clearly defined; if you really want something like that, I think
you can detach the referenced partition.



-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services