Re: Idea: Avoid JOINs by using path expressions to follow FKs

On 3/28/21 10:04 AM, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 3/27/21 5:11 PM, Alvaro Herrera wrote:
>>> This seems pretty dangerous -- you just have to create one more FK, and
>>> suddenly a query that worked perfectly fine, now starts throwing errors
>>> because it's now ambiguous. Feels a bit like JOIN NATURAL, which many
>>> people discourage because of this problem.
>> Maybe. I don't recall ever having seen a column with more than one FK.
>> Is that a common thing? In itself it seems like a bad idea.
> Yeah, that aspect seems like a complete show-stopper. We have a way
> to enforce that you can't *drop* a constraint that some stored view
> depends on for semantic validity. We don't have a way to say that
> you can't *add* a constraint-with-certain-properties. And I don't
> think it'd be very practical to do (consider race conditions, if
> nothing more).
>
> However, that stumbling block is just dependent on the assumption
> that the foreign key constraint being used is implicit. If the
> syntax names it explicitly then you just have a normal constraint
> dependency and all's well.
>
> You might be able to have a shorthand notation in which the constraint
> isn't named and the system will accept it as long as there's just one
> candidate (but then, when dumping a stored view, the constraint name
> would always be shown explicitly). However I'm not sure that the
> "shorthand" would be any shorter. I'm imagining a syntax in which
> you give the constraint name instead of the column name. Thought
> experiment: how could the original syntax proposal make any use of
> a multi-column foreign key?

I guess we could have a special operator, which allows the LHS to be
either a column (in which case it must have only one single-valued FK
constraint) or a constraint name in which case it would match the
corresponding columns on both sides.

It gets kinda tricky though, as there are FKs going both ways:

    customers <- orders <- order_details -> products

and in fact this could make composing the query LESS clear. The natural
place to start this query (show me the name of every customer who
ordered chocolate) is with orders ISTM, but the example given starts
with order_details which seems somewhat unnatural.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com