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

On Tue, Mar 30, 2021 at 09:02:39AM +0200, Pavel Stehule wrote:
> út 30. 3. 2021 v 8:52 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com> napsal:
>
> > On Tue, Mar 30, 2021 at 08:03:09AM +0200, Pavel Stehule wrote:
> > >
> > > 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?
> >
> > The #1 cause of problems is probably unqualified outer references, and
> > unfortunately I don't think it's really possible to detect that in an
> > extension, as the required information is only available in the raw
> > parsetree.
> >
>
> the raw parsetree is available I think. I didn't check it. But it can be
> easy to attach or attach a copy to Query structure. Maybe there is no
> necessary hook. But it can be a good reason for implementing a post parsing
> hook.

It's not available in any existing hook. And even if it was you would have to
import most of transformTopLevelStmt() and all underlying functions to be able
to detect this case I think. This should be best done in core postgres.

> It should be easy to check if all joins are related to foreign key
> constraints.

Yes, and also if the referenced columns are covered by indexes for instance.
My concern is mostly that you won't be able to cover the unqualified outer
references, which can lead to wrong query results rather than just slow
queries.