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

On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> Postgres has since the outset gone beyond the SQL standard in many ways :
> types, inheritance, programmability, generality are all well beyond what SQL
> used to mandate and still well beyond the current standard.
>
> There are huge developer benefits available to focusing more on making a great
> relational programming environment, well outside the SQL standard.
>
> Examples of small things Postgres could have:
>
>   • SELECT * - b.a_id from a natural join b
>       □ let me describe a select list by removing fields from a relation. In
>         the example, I get all fields in the join of  a  and b other than the
>         shared key, which I only get once.

Natural join already does this.

My use case for such a feature are tables which contain one column (or a
small number of columns) which you usually don't want to select: A bytea
column or a very wide text column. In a program I don't mind (in fact I
prefer) listing all the columns explicitely, but exploring a database
interactively with psql typing lots of column names is tedious
(especially since autocomplete doesn't work here).

>       □ note how this simplifies maintaining views wrt  changes in tables

Maybe. I'm not sure whether views that change automatically with their
underlying tables wouldn't do more harm than good.

>   • Let me put the FROM clause first
>       □ if I can write FROM a join b SELECT a.height, a.name, b.email then an
>         editor can give me autocomplete when I’m writing the select clause.

Logically from should be first and select should be last, I agree. That
would make life easier for editors, but it shouldn't be impossible for
an editor to look forward.

>   • Hierarchical schemas

I thought I would miss that when I learned SQL 25 years ago, but in
practice I didn't. Plus it's already not always obvious how names are
resolved and hierarchical schemas would almost certainly make that
worse.


> Examples of larger things Postgres might have:
>
>   • First-class functions.

I prefer to have as much application logic as feasible in the
application, so I'm rather indifferent to server-side programming
features.

>   • Other languages
>       □ Tutorial D, Datalog, Quell, let’s open this puppy up!
>       □ SQL is a terrible, no good, very bad language

I suspect that lots of the internals (especially in the optimizer) are quite
specific to how SQL works. So it's probably not that easy to provide a
different query language - at least not one which works efficiently.

But you are welcome to try.

>   • A portable, low-level API
>       □ An alternative to SQLite that provides CRUD operations on a Postgres
>         database.

I'm not familiar with the low level SQLite interface. I've only ever
used it with SQL. I did use dBase back in the 1980s, though ;-).

Are you really interested in a lower-level interface or do you just want
it in-process? I suspect that just adding in-process capability would
require a major overhaul.

        hp

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