Re: Duplicate unique key values in inheritance tables

On Monday, July 15, 2024, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 16 Jul 2024 at 12:45, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > As a workaround for this issue, I'm considering whether we can skip
> > checking functional dependency on primary keys for inheritance
> > parents, given that we cannot guarantee uniqueness on the keys in this
> > case.
>
> Because it's a parser issue, I don't think we can fix it the same way
> as a5be4062f was fixed.
>
> I don't have any ideas on what we can do about this right now, but
> thought it was worth sharing the above.
>

Add another note to caveats in the docs and call it a feature. We produce
a valid answer for the data model encountered. The non-determinism isn’t
wrong, it’s just a poorly written query/model with non-deterministic
results. Since v15 we have an any_value aggregate - we basically are
applying this to the dependent columns implicitly. A bit of revisionist
history but I’d rather do that than break said queries. Especially at
parse time; I’d be a bit more open to execution-time enforcement if
functional dependency on the id turns out to have actually been violated.
But people want, and in other products have, any_value implicit aggregation
in this situation so it’s hard to say it is wrong even if we otherwise take
the position that we will not accept it.

David J.