Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

On Thu, 5 Sep 2024 00:57:28 +0200
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote:

> On Mon, 2 Sep 2024 23:01:47 +0200
> Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote:
> 
> […]
> 
> >   My proposal was to clean everything related to the old FK and use some
> >   existing code path to create a fresh and cleaner one. This requires some
> >   refactoring in existing code, but we would win a common path of code
> > between create/attach/detach, a cleaner catalog and easier code maintenance.
> > 
> >   I've finally been able to write a PoC that implement this by calling
> >   addFkRecurseReferenced() from DetachPartitionFinalize(). I can't join
> >   it here because it is currently an ugly draft and I still have some work
> >   to do. But I would really like to have a little more time (one or two
> > days) to explore this avenue further before you commit yours, if you don't
> > mind? Or maybe you already have considered this avenue and rejected it?  
> 
> Please, find in attachment a patch implementing this idea.

Please, find in attachment a set of patch based on the previous one.

v3-0001-Add-tests-about-FK-between-partitionned-tables.patch:

  This patch implement tests triggering the bugs discussed. Based on Michael
  advice, I added one level sub-partitioning to stress test the recursive code
  and some queries checking on the catalog objects.

v3-0002-Rework-foreign-key-mangling-during-ATTACH-DETACH.patch:

  The main patch, similar to v2 in my previous patch with more comments
  added/restored. I added some more explanations in the commit message about
  the refactoring itself, making addFkRecurseReferencing() and
  addFkRecurseReferenced() having the same logic.

v3-0003-Use-addFkConstraint-in-addFkRecurseReferencing.patch

  A new patch refactoring the constraint creation in addFkRecurseReferencing()
  to use the new addFkConstraint() function.

v3-0004-Use-addFkConstraint-in-CloneFkReferencing.patch

  A new patch refactoring the constraint creation in CloneFkReferencing()
  to use the new addFkConstraint() function.

TODO:

* I hadn't time to study last Tender Wang comment here:
  https://postgr.es/m/CAHewXNkuU2V7GfgFkwd265RJ99%2BBfJueNEZhrHSk39o3thqxNA%40mail.gmail.com
* I still think we should split v3-0002 in two different patch…
* backporting…

Regards,