Re: Can we go beyond the standard to make Postgres radically better?

On 2022-02-12 22:09:25 +0100, Andreas 'ads' Scherbaum wrote:
> On 12/02/2022 20:50, Peter J. Holzer wrote:
> > On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote:
> > > On 10/02/2022 18:22, Peter J. Holzer wrote:
> > > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> > > > > Examples of small things Postgres could have:
> > > > >
> > > > > • SELECT * - b.a_id from a natural join b
[...]
> > > Maybe for this specific use case it's easier to teach psql how to do that,
[...]
> > I think the easiest way to get the columns would be to EXPLAIN(verbose)
> > the query. Otherwise psql (or whatever your shell is) would have to
> > completely parse the SQL statement to find the columns.
> >
> > (On a tangent, I'm wondering if this could work for autocomplete. The
> > problem with autocomplete is of course that you probably don't have
> > a syntactically correct query at the time you need it. So the editor
> > would have to patch that up before sending it to the database.)
>
> I was thinking about this problem for a while, and it's not easy to solve.
> Hence I came up with the idea that psql could - once the table is known
> and very specific psql syntax is there (\- as example) replace the * with
> the actual columns. All of this before the query is run, and as a user you
> can edit the column list further.

Yeah, but the problem is that it isn't that easy for psql to figure out
which table is involved. The query may involve joins, subquerys, CTEs
(and possibly other stuff I forgot). So it would have to parse the query
(which it currently has no need to do - it can just send it as it is to
the server) to find out which tables are involved, what columns they
have, how those columns are transformed, etc. Quite a bit of work and it
has to do it in the same way as the server (psql has a bit of advantage
there because it's in the same code base so it could probably borrow
some code from the server, but think of other shells like PgAdmin, which
aren't even in the same programming language).

So that was my first idea but I discarded that as too complicated.

Then I thought about running the query with «limit 0» to get the list of
columns. But that's unsafe - the query might change some data; you don't
want that to happen automatically.

So my third idea was to use explain to get the list of columns. I think
that's safe in that the code is never actually run. But it is a query
that can fail - which aborts the transaction. So you probably don't want
your shell to do that automatically, either. (OTOH, the query would very
likely have failed anyway.)

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"