Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Hello Joel,

Thank you for your kind words and happy that you benefited from this patch.
We simply assert that the update/delete method used is supported currently
only "NO ACTION" and "RESTRICT", this can be extended in future patches
without rework, just extra logic.
Please don't hesitate to give your feedback.

I would love some help with some performance comparisons if you are up to
it, between Many-to-Many, Foreign Key Arrays and Gin Indexed Foreign Key
Arrays.

/Mark

On Tue, Jan 26, 2021 at 1:51 PM Joel Jacobson <joel(at)compiler(dot)org> wrote:

> Hi Mark,
>
> On Mon, Jan 25, 2021, at 09:14, Joel Jacobson wrote:
>
> I'll continue testing over the next couple of days and report if I find
> any more oddities.
>
>
> I've continued testing by trying to make full use of this feature in the
> catalog-diff-tool I'm working on.
>
> Today I became aware of a SQL feature that I must confess I've never used
> before,
> that turned out to be useful in my tool, as I then wouldn't need to follow
> FKs
> to do updates manually, but let the database do them automatically.
>
> I'm talking about "ON CASCADE UPDATE", which I see is not supported in the
> patch.
>
> In my tool, I could simplify the code for normal FKs by using ON CASCADE
> UPDATE,
> but will continue to need my hand-written traverse-FKs-recursively code
> for Foreign Key Arrays.
>
> I lived a long SQL life without ever needing ON CASCADE UPDATE,
> so I would definitively vote for Foreign Key Arrays to be added even
> without support for this.
>
> Would you say the patch is written in a way which would allow adding
> support for ON CASCADE UPDATE
> later on mostly by adding code, or would it require a major rewrite?
>
> I hesitated if I should share this with you, since I'm really grateful for
> this feature even without ON CASCADE UPDATE,
> but since this was discovered when testing real-life scenario and not some
> hypothetical example,
> I felt it should be noted that I stumbled upon this during testing.
>
> Again, thank you so much for working on this.
>
> /Joel
>