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

po 29. 3. 2021 v 23:00 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Mon, Mar 29, 2021, at 20:53, Vik Fearing wrote:
>
> On 3/29/21 7:55 PM, Joel Jacobson wrote:
> > Do you know if REF is meant to be a replacement for foreign keys?
> >
> > Are they a different thing meant to co-exist with foreign keys,
> > or are they actually foreign keys "under the hood"
> > or something else entirely?
>
> They're supposed to be OOP where each row in the typed table is an
> instance of the object. Types can also have methods associated with
> them, and the instance tables can have subtables similar to our table
> inheritance. The dereference operator is replaced by a subquery.
>
> There is a whole slew of things in this area of the standard that
> apparently never caught on.
>
>
> Hmm. Since it never caught on, maybe it was partly due to too much
> complexity, and maybe can invent a simpler solution?
>
> I would also be against this idea if the complexity cost would be too high,
> but I think Tom's foreign key constraint name idea looks fruitful since
> it's simple and non-invasive.
>

Maybe there were no technical problems. Just this technology was coming at
a bad time. The people who needed (wanted) OOP access to data got the
Hibernate, and there was no necessity to do this work on SQL level. In this
time, there was possibility to use GUI for databases, and in this time
there were a lot of graphic query designers. I don't like the idea of
foreign key constraint names - it doesn't look comfortable to me. I don't
say it is a bad idea, but it is not SQL, and I am not sure if it needs more
or less work than explicitly to write PK=FK.

On second hand, it can be very nice to have some special strict mode in
Postgres - maybe slower, not compatible, that disallow some dangerous or
unsafe queries. But it is possible to solve in extensions, but nobody did
it. Something like plpgsql_check for SQL - who will write sql_check?

Regards

Pavel

> /Joel
>