Andrew Dunstan <andrew@dunslane.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?
> Not saying I think this suggestion is a good idea, though. We've seen
> many frameworks that hide joins, and the results are ... less than
> universally good.
Yeah, I'm pretty much not sold on this idea either. I think it would
lead to the same problems we see with ORMs, namely that people write
queries that are impossible to execute efficiently and then blame
the database for their poor choice of schema.
regards, tom lane
