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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"