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 20200207113203.3e26b28d@firost
Whole thread Raw
In response to Re: FK violation in partitioned table after truncating a referencedpartition  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Responses Re: FK violation in partitioned table after truncating a referencedpartition
List pgsql-bugs
Thank you for the review and thoughts Alvaro.

On Thu, 6 Feb 2020 20:01:29 -0300
Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

> On 2020-Feb-06, Alvaro Herrera wrote:
> 
> > On 2020-Feb-06, Alvaro Herrera wrote:
> >   
> > > I agree that patching heap_truncate_find_FKs is a reasonable way to fix.
> > > I propose a slightly different formulation: instead of the loop that you
> > > have, we can just use the second loop, and add more parent constraints
> > > to the list if any constraint we scan in turn has a parent constraint.
> > > So we don't repeat the whole thing, but only that second loop.  
> > 
> > Hmm, this doesn't actually work; I modified your test case and I see
> > that my code fails to do the right thing.  
> 
> Yeah, AFAICS both algorithms posted so far (yours and mine) are wrong.

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);

[...]
> ENOTIME to complete it now, though ... also: I'm not sure about having
> heap_truncate_find_FKs() acquire the locks on partitions;

ExecuteTruncate and ExecuteTruncateGuts are responsible to open and
lock relations. It might be messy or racy between those and
heap_truncate_find_FKs if the later open/lock or open/nolock while looking for
relations.

> but what happens if there's a concurrent detach?

Not sure. Are you talking about the referenced or referencing side?

> This is a larger can of worms than I imagined.  Maybe a simpler solution
> is to say that you cannot truncate a partition; if you want that,
> truncate the topmost relation.

I thought about this as well, but it might be a feature regression in a minor
version.

> No functionality seems lost with that restriction, or is it?

It does. When truncating a partition, you left untouched other siblings. You
did not truncate the whole partioned table. this is the last query in my
original test.

I added some more words to the doc about this. Please, find in attachment a new
version of bug fix proposal.

Regards,

Attachment

pgsql-bugs by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: BUG #16246: Need compatible odbc driver to establish connectivitywith SAP BOBJ 4.2
Next
From: Amit Kapila
Date:
Subject: Re: ERROR: subtransaction logged without previous top-level txn record