Re: Non-Stored Generated Columns

On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote:
> Polymorphic Foreign Keys are nigh impossible to model well in SQL,
> and I doubt that non-stored generated columns will solve that.
>

It is modelled. It works. As I already wrote above.
It's not for everyone, no doubt about it. it has its costs. But we
definitely must use it.

Non-stored generated columns simply makes that implementation more costly
in space that it could/should be.
Honestly, I'm not sure why supporting the non-stored variant of generated
columns is so controversial...

> > For NOT NULL PFKs, that more of a PITA [...]
>
> See?
>

And? I'm not sure I see you point Laurenz.
The alternative to NOT implementing PFKs is NOT having referential
integrity.
Which is a big no-no for us. The "costs" of PFKs are outweighed by lack of
RI.

> > So has Ron says. If SQLite and Oracle has them, that's not an accident.
>
> Oracle has a lot of things that are not enviable...
>

The same can't be said for SQLite :)
And yet it has them too!

> I am sure there are some use cases for "virtual" generated columns, and
> I don't think that a patch that tries to implement them will be rejected
> out of hand. It's just that I don't think it is a very important feature.
>

Fair enough. And a reaction I expected when I first posted.
The outright rejection of it ever being useful, that's what surprised me.
I'm not a PostgreSQL server developer. So a patch won't be coming from me
though...
I'm "only" a PostgreSQL / LIBPQ user, albeit maybe an "above average" one I
dare say.

> > Are the NULLs stored in these indexes, consuming extra space?
>
> Yes, NULLs are stored in indexes, just like everything else.
>

OK, that confirms my worry. Thanks.

> You could use conditional indexes, but then you have to make sure that
> the optimizer knows it can use these indexes.
>

I'm not following. Are you saying the planner is not good at that on its
own?
I need to do something from my end???

> The sum of the sizes of these indexes shouldn't exceed the size of an
> unconditional index by much, but they would still be more expensive:
> each statement would have to look through all the indexes to decide
> which ones it can use and which ones not.
>

Something I maybe didn't make clear. The XArc virtual columns are never
accessed.
Only the ID and CODE concrete columns are read and written.
The XArcs are an implementation detail solely to ensure RI of PFKs.
The only reason we index them is for efficient CASCADE'ing of the FKs (on
XArcs).

Surely the fact XArc "FK" indexes are partial/expression-based,
shouldn't prevent the PostgreSQL-internal CASCADE from using them, no?

Otherwise that would defeat having indexed FK columns, no?
Or did I misunderstand you last point?

Thanks again for taking time on this particular thread. Appreciated. --DD