Re: Inheritance in PostgreSQL

On Wed, Oct 18, 2023 at 7:43 AM Thomas Kellerer <shammat(at)gmx(dot)net> wrote:

> Merlin Moncure schrieb am 18.10.2023 um 03:20:
> > The only thing you can't really do in SQL easily without writing
> > nasty triggers are things like, 'this table must be linked from one
> > and only one of these candidate tables'. I think the language
> > probably ought to support this, but I don't think postgres would
> > unless the standard did.
>
> Isn't that what assertions are intended to solve in the SQL standard?
>

Hm, interesting, <googles> ...maybe, maybe not. SQL '92 -- wow. guessing
why not: Are they deferrable, always deferred? Doesn't seem like it,
simple inheritance cases would seem problematic otherwise, chicken/egg
conditions. This might explain why they are not in common use despite being
innovated 30 years ago. Here is an Oracle discussion on the topic:

https://forums.oracle.com/ords/apexds/post/sql-assertions-declarative-multi-row-constraints-8418

...with the discussion implying that interaction with the transaction state
may be an unsolved problem, at least within oracle. I suspect there may
also be challenges relating to performant implementation. Sadly, I think
the correct approach remains to utilize complex triggers, or nothing.

merlin