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(at)dalibo(dot)com> wrote:

> On Mon, 2 Sep 2024 23:01:47 +0200
> Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)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,