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

On Wed, Feb 9, 2022 at 10:15 PM Guyren Howe <guyren@gmail.com> wrote:

There are huge developer benefits available to focusing more on making a
great relational programming environment, well outside the SQL standard.

Sure

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.*
- *note how this simplifies maintaining views wrt changes in
tables*

Natural join sucks and "using" already does this. Not seeing how this

meaningfully impacts views. in their current form.

I have asked for this in the past though...but it is so contrary to the
fundamental design of SQL, and provides such limited benefit, that its
omission seems like a net positive (if you factor in both novelty and
development)

- *Let me put the FROM clause first*
- *if I can write FROM a join b SELECT a.height, a.name
<http://a.name&gt;, b.email then an editor can give me autocomplete when I’m
writing the select clause.*

select * from ..., then go back and change * to whatever columns you end

up wanting and your editor can, in theory, do what you want. Dance with
the person you brought.

- *Hierarchical schemas*

- *First-class functions.*
- *A global-only namespace is dumb. Schemas are only slightly less
dumb. The obvious way to store and call functions is as values of fields.
Let me organize them how I choose*
- *Also let me pass around functions as values, let me make new
ones and so on. Postgres is the best relational programming environment
already because it has a decent type system and more general features.
Let’s expand and also simplify that.*
- *Also nested function definitions, so top-level functions can be
built out of local auxiliary functions.*

See past and current discussions regarding modules. Improvement here is

IMO the most worthy of attention (among this list, not globally)

- *Other languages*
- *Tutorial D, Datalog, Quell, let’s open this puppy up!*

Go talk to them. Adding more of these to the core is undesirable.

- *SQL is a terrible, no good, very bad language*

I'd rather live with SQL as it is and develop static analyzers and other

related tools to make using it less problematic; in ways that we cannot do
in the server without breaking existing code.

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

Not really understanding what features you are including in this one.

Doesn't feel like something that belongs in core though. I'd rather
efforts be made to make extension writing and installing easier (including
the related "module" feature you note above).

I adore Postgres, but this is despite rather than because of SQL. By all
means, let’s have a close-to-standard subset of features, but let’s
fearlessly go beyond them when it would be obviously much better.

No one is saying we shouldn't or cannot do just that. Though frankly there
is enough WIP in our commitfest to keep the majority of volunteers
sufficiently busy on stuff that has two important attributes this email
lacks - someone championing the feature and a functioning patch.
David J.

Am Wed, Feb 09, 2022 at 09:14:39PM -0800 schrieb Guyren Howe:

There are huge developer benefits available to focusing
more on making a great relational programming environment,
well outside the SQL standard.

There's a seemingly small but conceptually rather significant
difference between going _beyond_ a standard and being _well
outside_ said standard.

Which do you propose ?

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Wed, Feb 9, 2022 at 11:15 PM Guyren Howe <guyren@gmail.com> 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.*

I could see this as being extremely useful, I would support non standard

ways of subtracting from '*',not necessarily in that syntax.

- *note how this simplifies maintaining views wrt changes in tables*
- *Let me put the FROM clause first*
- *if I can write FROM a join b SELECT a.height, a.name
<http://a.name&gt;, b.email then an editor can give me autocomplete when I’m
writing the select clause.*

I see the benefit, but it's not big enough to support standard deviation

IMO. SQL grammar is already very complex and this wouldn't help, even if
it's deterministically parsable.

- *Hierarchical schemas*

This would violate the standard, and would be unclear. Rules are set,

identifier is [database.].[schema.][table.].column. and your change would
introduce ambiguity.

*Examples of larger things Postgres might have:*

- *First-class functions.*
- *A global-only namespace is dumb. Schemas are only slightly less
dumb. The obvious way to store and call functions is as values of fields.
Let me organize them how I choose*

Not following this, can you elaborate specific uses?

- *Also let me pass around functions as values, let me make new ones
and so on. Postgres is the best relational programming environment already
because it has a decent type system and more general features. Let’s expand
and also simplify that.*

Ditto above. Mostly we can work around this with dynamic SQL today without

too much effort.

- *Also nested function definitions, so top-level functions can be
built out of local auxiliary functions.*
- *Other languages*
- *Tutorial D, Datalog, Quell, let’s open this puppy up!*
- *SQL is a terrible, no good, very bad language*

Postgres used to suport QUEL way back in the day. Being blunt, there is

zero chance of this happening in core. A query language translator out of
core, preprocessing the language into SQL, might work. SQL has evolved
far beyond what those languages could practically offer. Main benefit
here would be to better support relational division, which bizarrely has
yet to arrive in SQL.
*A portable, low-level API*

- *An alternative to SQLite that provides CRUD operations on a
Postgres database.*

This has been frequently discussed in the archives. Short version, won't

happen; it would put too many constraints on the architecture.

merlin

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!"

On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:

On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:

• 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).

Forgot to add: I think that the syntax would have to be more explicit.
It's too easy to mix up
SELECT * - b.a_id FROM ...
and
SELECT *, - b.a_id FROM ...

Maybe
SELECT * EXCEPT b.a_id FROM ...
?

hp

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

On 2022-02-10 10:13:16 +0100, Karsten Hilbert wrote:

Am Wed, Feb 09, 2022 at 09:14:39PM -0800 schrieb Guyren Howe:

There are huge developer benefits available to focusing
more on making a great relational programming environment,
well outside the SQL standard.

There's a seemingly small but conceptually rather significant
difference between going _beyond_ a standard and being _well
outside_ said standard.

In my opinion:

A change which doesn't alter the semantics of any
standard-conforming query (but only makes some queries valid which
are invalid according to the standard) is an extension.

Not sure if this is true for all of Guyren's proposals, although no
counter-examples immediatly pop into mind.

hp

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

On Thu, Feb 10, 2022 at 10:54 AM Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Feb 9, 2022 at 11:15 PM Guyren Howe <guyren@gmail.com> wrote:

- *Also nested function definitions, so top-level functions can be
built out of local auxiliary functions.*
- *Other languages*
- *Tutorial D, Datalog, Quell, let’s open this puppy up!*
- *SQL is a terrible, no good, very bad language*

Postgres used to suport QUEL way back in the day. Being blunt, there is

zero chance of this happening in core. A query language translator out of
core, preprocessing the language into SQL, might work. SQL has evolved
far beyond what those languages could practically offer. Main benefit
here would be to better support relational division, which bizarrely has
yet to arrive in SQL.
*A portable, low-level API*

FYI, there was a semi-serious commercial attempt to do this back in 2001,
Dataphor. It's been opensourced. Wikipedia has a pretty good write up on
it:
https://en.wikipedia.org/wiki/Dataphor

IMNSHO suggestions like these should travel this technical path; take the
data language you want and translate it into SQL. Some of these
translations will end up being complicated (read: slow). Iterating this
kind of thing outside of core would allow for faster development.

merlin

I’d like to point out that sum types would be great.

(Sum types let you have any of two or more different types in one value)

For example, I could work around the issues with NULL by defining an enumeration type with values like MISSING, UNKNOWN, INVALID, … and then I can have a column that is a sum of that type with the type we normally keep in the column.

We would need some machinery to declare how aggregates work, but I think that’s very much in the spirit of how types work in Postgres now.

In general, I would think we could make SQL a better functional programming language by just implementing good fp features like this.

On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote:

On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:

On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:

• 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).

Forgot to add: I think that the syntax would have to be more explicit.
It's too easy to mix up
SELECT * - b.a_id FROM ...
and
SELECT *, - b.a_id FROM ...

Maybe
SELECT * EXCEPT b.a_id FROM ...

Yes, this was proposed on hackers a few months ago and a patch was
proposed:

/messages/by-id/892708.1634233481@sss.pgh.pa.us

The last post was from October of 2021 so you can email the author to
ask about its status.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

This is a strange post. Why is SQL bad and how do your reconcile that with
managing 99%+ of all data? It's so bad that we have systems that plug into
sql to query data outside of tables like Athena or Excel.

Why are you not using pgadmin4? Yes. Psql as a command line isn't great for
humans. It's spectacular for computers though. So we have pgadmin4, which I
would take over any other database ui.

Do you not want your views to change with underlying base tables changing?
Do a fully specified select. It's better programming anyway. Do you want an
api? That seems like a bad idea (i would never implement it) but you also
have a postgres socket, flask, and sqlalchemy or psycopg2. It would take a
few hours to write your own. Again, please don't do that. You will almost
surely lose user information like who decided to delete your client data
and your api would likely require user privileges to get passed by token
(sso would be a nightmare to authenticate) or simply give root privileges
to an api. Both are honestly really bad.

Now if postgres had the ability to do schema change tracking with
rollback... now that would be a victory. But there are sort of 3rd party
solutions that sort of work some of the time. It's a hard problem and
automated sql generation, particularly automated schema migrations, are
really hard to build in general and there are specific things that are damn
hard to not break.

Thanks,
Ben

On Thu, Feb 10, 2022, 4:13 PM Bruce Momjian <bruce@momjian.us> wrote:

When you dig into it, the powerful idea here is the relational algebra, and its equivalence to a first-orderish logic.

I put up with SQL so I can use relations, and I love Postgres because it has the least bad SQL (by a mile!)

But SQL is a terrible, no good, very bad language.

I don’t really understand why folks who love the relational model aren’t perpetually up in arms about SQL being their only option. Much better query languages are known and well studied.

https://www.scattered-thoughts.net/writing/against-sql <https://www.scattered-thoughts.net/writing/against-sql&gt;

I’d love to see Postgres offer say Datalog. But until that Halcyon day, we could at least aggressively extend SQL to make it less awful.

On Thu, Feb 10, 2022 at 3:51 PM Guyren Howe <guyren@gmail.com> wrote:

But SQL is a terrible, no good, very bad language.

No, it's not. It is also not perfect.

I don’t really understand why folks who love the relational model aren’t

perpetually up in arms about SQL being their only option. Much better query
languages are known and well studied.

Because it isn't worth the stress.

That, and it's difficult to justify such behavior when the product you are
using is basically free.

David J.

P.S. How did you ever survive the Betamax vs. VHS period of human
civilization? ;)

Please, don't top-post.

On 2/10/22 17:51, Guyren Howe wrote:

When you dig into it, the powerful idea here is the relational
algebra, and its equivalence to a first-orderish logic.

I put up with SQL so I can use relations, and I love Postgres because
it has the least bad SQL (by a mile!)

As you've said, the basic motivation behind SQL is the relational
algebra from the branch of mathematics called "naive set theory". SQL
does that really well. Late Dr. Ted Codd was a mathematician, so he was
also aware of non-naive set theory so the "selection language" of
Zermelo's axiom of choice was also included. That is why we are
selecting elements of subsets and not defining subsets. Codd was also
working for IBM, once great company, whose name stands for "It's Better
Manually". IBM was doing lots of business with the banks (figures) so
the transaction handling part was modeled after the banking business.
When I've had my first course in SQL, back in 1988, I was given analogy
with writing checks: I A writes a $100 check to B, one of the following
two things can happen: either A has enough money to cover the check, in
which case B will have $100 more in his account and A will have $100
less or, if the transaction doesn't go through, nothing will happen. Any
mixed outcome in which B is $100 richer but A doesn't have $100 less
money in his account or A has $100 less money but B is not $100 richer
is strictly prohibited. That transaction must happen atomically, as a
whole or not happen at all. So, that's SQL for you: relational algebra
plus banking business. Relational algebra implementation is rather good,
although a tad too verbose. Transactional part is excellent and still
reflects the needs of the business community today.

But SQL is a terrible, no good, very bad language.

I cannot accept such a religious persecution of SQL without a detailed
explanation.

I don’t really understand why folks who love the relational model
aren’t perpetually up in arms about SQL being their only option. Much
better query languages are known and well studied.

https://www.scattered-thoughts.net/writing/against-sql

I’d love to see Postgres offer say Datalog. But until that Halcyon
day, we could at least aggressively extend SQL to make it less awful.

It will take much more than a blog post to eradicate decades of
investment into SQL by both computer scientists and business community.
I've seen the same crusade against SQL on Slashdot and I assume you were
behind that as well. So, if I were you, I would establish a company to
implement Datalog language in a database and try to sell that to the
people. That happened in 1979: Larry Ellison, Bob Miner and Ed Oates
established a company and offered a SQL product, competing with
hierarchical databases like IMS and CICS/DL1, to the world. The rest is
history. I am sure that if you do the same and if you are right about
the Datalog, a brilliant future is ahead of you. That is the way of the
Force. Anything can happen. A young student can come from Africa,
convinced that he can sell electric cars and be very successful while
doing that. So successful that he became the richest man in the world. I
am sure that your endeavor with Datalog will have similar success.
Market decides what is the standard. May the Force be with you. In the
mean time, I will still be using SQL.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 2/10/22 4:51 PM, Guyren Howe wrote:
[snip]

I don’t really understand why folks who love the relational model aren’t
perpetually up in arms about SQL being their only option. Much better
query languages are known and well studied.

Because it's Good Enough, and everyone with the wisdom of age knows that
perfect is the enemy of good enough.

--
Angular momentum makes the world go 'round.

On Thu, Feb 10, 2022 at 5:51 PM Guyren Howe <guyren@gmail.com> wrote:

When you dig into it, the powerful idea here is the relational algebra,
and its equivalence to a first-orderish logic.

I put up with SQL so I can use relations, and I love Postgres because it
has the least bad SQL (by a mile!)

But SQL is a terrible, no good, very bad language.

I don’t really understand why folks who love the relational model aren’t
perpetually up in arms about SQL being their only option. Much better query
languages are known and well studied.

I, for one, quite agree. The advances in languages have been marvelous,
and it's hard to imagine anyone today choosing to code in COBOL, or any
other English-like language. SQL holds its place because of the tools it
allows us to use, not on its own virtues... rather like Javascript with
browsers.

And the problem seems very serious to me. In the words of Alfred North
Whitehead, "By relieving the brain of all unnecessary work, a good notation
sets it free to concentrate on more advanced problems, and in effect
increases the mental power of the race." Conversely, a tool which imposes
needless overhead is an IQ tax we can ill-afford.

So far, though, that's just my unsupported opinion, and one can't expect to
convince people with generalities. But what a sprawling topic! You could
make dozens of suggestions for improvements, any one of which would need an
extensive conversation.

Here's one that I think is simple: why would we want a language where the
clauses must come in a particular order? `FROM mytable SELECT column` is
as clear an expression as `SELECT column FROM mytable`, and probably
better, in that it starts with the source and winnows from there.
Likewise, the order of WHERE, ORDER BY, and so on don't change what is
being said.

I believe the "why" is, "because parsing SQL is hard enough already", but
that's a problem unto itself. A language with a more regular syntax is
easier to work with and improve.

Now, while I'm not at all saying this is ideal, consider something as
simple as a shell:

cat mytable | cols x y z | where x > 2 | join table_2.y = mytable.y | sort z

The parts are atomic, and the structure is easy to see. If you wanted to
add a "command", you aren't going to run into questions of how to shoehorn
it into the existing language. Even if the example solution isn't to one's
taste, I hope the general point stands apart from it.

Also, I think it only fair to say: PostgreSQL has done quite a remarkable
job of polishing SQL into the best thing which can be made of it. I may
not be a fan of the language itself, but I'm truly grateful when I'm able
to use PG's dialect rather than the others I need to work with.

--
Ray Brinzer

On Feb 10, 2022, at 17:06 , Mladen Gogala <gogala.mladen@gmail.com> wrote:

But SQL is a terrible, no good, very bad language.

I cannot accept such a religious persecution of SQL without a detailed explanation.

I feel like anyone who is defending SQL here isn’t aware of how much better the alternatives are, and how bad SQL really is.

I mean: it’s hard to write, hard to read. It’s hard to generate. But that’s just the starting point.

One of the worst things about it that I don’t see much discussed is that it imposes assumptions about the storage model that aren’t part of the relational model. Like heavyweight, hard to change tables with transactional guarantees and such. Don’t get me wrong, those things are great to have available, but I don’t need them all the time.

The whole NoSQL movement has been such a tragedy. Having diagnosed a problem with SQL databases, they threw out the relational model and very often reimplemented a form of SQL when they should have done the opposite. There is no reason you can’t have a relational database with an on-demand schema, with eventual consistency, with all those fun things that various NoSQL databases provide. Those storage models have their place, but the SQL standard says you can’t use them.

But the biggest issue is the verbose, terrible, very bad query language. In the web development community where I spend my time, it is almost holy writ to treat the database as a dumb data bucket, and do everything in the application layer (even things like validations, even when that is a provably incorrect approach). Why? I think it’s because they’re used to working in a pleasant language like Ruby or Python, and they want to do everything there. And who can blame them?

But this is bad. Proper relational design can take over much (most!) of the design of a typical business app, with significant efficiency gains the result. But no *community* is going to choose that when most of the members of the community don’t want to learn SQL and who can blame them?

Another issue: everyone thinks “relational” is the same thing as “SQL”. If we could get folks to break that association, then relations should be a standard feature of high-level programming languages, just as arrays and hashes are.

Heck, give me a functional programming language with a good relational model, and I can implement OOP in that relational language without breaking a sweat.

Software *should* be designed around a logical/relational layer with minimal occasional forays into Turing completeness where necessary. But no-one is even thinking about software like that because relational is SQL and SQL is awful.

On Thu, Feb 10, 2022 at 11:56 PM Guyren Howe <guyren@gmail.com> wrote:

I feel like anyone who is defending SQL here isn’t aware of how much
better the alternatives are, and how bad SQL really is.

Have you written a language description we can read and talk about?

--
Ray Brinzer

I’m not proposing some crackpot half-baked idea here. There are well-defined and researched alternatives to SQL.

The most fully-developed you-can-use-today offering is Datomic, which uses Datalog as its query language. If you know Prolog, and how that is kind of database-like, Datomic is pretty much a variant of Prolog.

https://www.datomic.com

I don’t use it because it’s closed source.

On Thursday, February 10, 2022, Guyren Howe <guyren@gmail.com> wrote:

On Feb 10, 2022, at 17:06 , Mladen Gogala <gogala.mladen@gmail.com> wrote:

But SQL is a terrible, no good, very bad language.

I cannot accept such a religious persecution of SQL without a detailed
explanation.

I feel like anyone who is defending SQL here isn’t aware of how much
better the alternatives are, and how bad SQL really is.

I’m very comfortable in my ignorance and apathy here…since even you feel
resigned to use PostgreSQL presently. When you find an alternative you
will substitute for PostgreSQL let me know. I don’t see that PostgreSQL is
best served trying to make you happy on this front.

David J.