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

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 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 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:

    https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9

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.

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

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.

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.

Raymond Brinzer <ray.brinzer@gmail.com> writes:
> Will it be accepted here?  I don't know; I'm not an insider, or in a
> position to say.  But it'd be a much better pitch than a pep talk, or
> speaking in generalities about SQL.  And that's coming from someone who
> actually agrees with you.  I'm 100% on board with the idea that something
> better is (badly) needed.  But is the idea, here, really to talk a highly
> successful project into doing a 180 based on this sort of argument?  If
> only the people writing the code saw the light, they'd go read the Datomic
> site, and start overhauling PostgreSQL?

Nah, probably not.  I mean, not only are we implementing SQL, but
we're implementing it in C.  I used better languages than C back
in the seventies ... but here we are.  Practical acceptance is
all about infrastructure and compatible tooling, which SQL and C
both have in spades, while academic designs really don't.

Also, I fear this discussion underestimates the difficulty of
putting some other query language on top of Postgres.  I know
you'll say "but the Berkeley guys pasted SQL onto a QUEL engine
back when, so how hard can it be?"  In the first place, that
was done on top of maybe ten years worth of work, but now there's
another twenty-five years of development agglomerated on top of
that.  So moving things would be more than 3X harder, even if
you make the very-naive assumption that the difficulty is merely
linear.  In the second place, QUEL and SQL aren't that far apart
conceptually, and yet we've still had lots of problems that can
be traced to their incompatibilities.  Something that was really
different from SQL would be a nightmare to embed into PG.  I'll
just point out one example: if you don't like SQL's semantics for
NULL (which no I don't much like either), changing that would
probably require touching tens of thousands of lines of code just
in the PG core, never mind breaking every API used by extensions.

So for better or worse, Postgres is a SQL engine now.  If you
want Datalog or $other_language, you'd be better off starting
or contributing to some other project.

That's not to say that we can't do stuff around the margins.
The idea of "select all columns except these", for instance,
has been discussed quite a bit, and would probably happen if
we could get consensus on the syntax.  But we're not going to
throw away thirty-five years' worth of work to chase some
blue-sky ideas.

            regards, tom lane

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.

    Peter J. Holzer wrote:

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

The solution to this by the SQL standard might be that it can be done with
a Polymorphic Table Function, introduced in SQL:2016.

https://webstore.iec.ch/preview/info_isoiec19075-7%7Bed1.0%7Den.pdf

A practical example with the Oracle implementation can be seen here:
https://blogs.oracle.com/sql/post/select-star-except-queries-in-oracle-database

Excluding all columns of a given type from a relation is also possible,
with both the relation and the type as parameters of the PTF.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

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.

OK. If there are better alternatives, I am sure you will be able to sell them to the world. Establish a company and have a go at it.

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.

Storage model and implementation are not part of SQL for good reason. Database vendors have different implementations. MySQL and Postgres are different. MySQL storage engines differ among themselves. Both of them are different from SQL Server which is in turn different from Oracle and DB2. Storage model determines the implementation. When there is only a single relational database vendor left in the market, then they can burn their storage implementation into the language standard. Until then, the more, the merrier.

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?

As a database architect who has successfully bridged two very different database systems, I can tell you that the application programmers put the business logic into the application because they frequently don't know what the options are. They know Java or Python and that's what they do, period. That has nothing to do with SQL.

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.

There is such thing as "free market". If you offer them a better alternative, people will come. You may be the next Larry Ellison. And then again, you may be not. There is only one way to tell, and that's not proselytizing on the Postgres mailing list.

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

So to summarize, people are bad programmers who refuse to learn SQL So SQL is the problem? Common. You cannot bring that to a postgres list serve.

Look. It's not perfect. It's a pain. It is hard to generate queries (oh my God why are you doing this?) and it's hard to work with. You are describing c++ to Python programmers and arguing why no one should ever use c++. And look, there are other languages that are way better like Python. So why would anyone ever chose c++?

There is probably a misunderstanding here. I haven't talked about people, I leave that to politicians. Second, I was defending SQL. You've got me confused with somebody else. Last but not least, I didn't bring anything to this list, I was just responding to the posts.

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

On 2/11/22 09:12, Mladen Gogala wrote:
> On 2/11/22 09:48, Benedict Holland wrote:
>> So to summarize, people are bad programmers who refuse to learn SQL 
>> So SQL is the problem? Common. You cannot bring that to a postgres 
>> list serve.
>>
>> Look. It's not perfect. It's a pain. It is hard to generate queries 
>> (oh my God why are you doing this?) and it's hard to work with. You 
>> are describing c++ to Python programmers and arguing why no one 
>> should ever use c++. And look, there are other languages that are way 
>> better like Python. So why would anyone ever chose c++?
>>
> There is probably a misunderstanding here. I haven't talked about 
> people, I leave that to politicians. Second, I was defending SQL. 
> You've got me confused with somebody else. Last but not least, I 
> didn't bring anything to this list, I was just responding to the posts.
>
>
Can we get back to discussing the code of conduct now?

On 2022-02-10 16:13:33 -0500, Bruce Momjian wrote:
> 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
> > >
> > > 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.
[...]
> > Maybe
> >     SELECT * EXCEPT b.a_id FROM ...
>
> Yes, this was proposed on hackers a few months ago and a patch was
> proposed:
>
>     https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9

Interesting idea, but quite different, actually: That puts the exclusion
into the table definition instead of the query.

But I think if I want to bake that into my data model I'll just use a
view.

But that thread led me back to a discussion on this list from almost
exactly 2 years ago ...

        hp

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

On 10/02/2022 18:22, Peter J. Holzer wrote:
> 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).

Maybe for this specific use case it's easier to teach psql how to do that,
instead of trying to amend the SQL implementation? Example:

SELECT * \- col1 \- col2 FROM table

psql looks up the columns, translates * into the actual list minus these two
columns and lets you continue entering the query.


Regards,

-- 
                Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

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
> >
> > 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).
>
> Maybe for this specific use case it's easier to teach psql how to do that,
> instead of trying to amend the SQL implementation? Example:
>
> SELECT * \- col1 \- col2 FROM table
>
> psql looks up the columns, translates * into the actual list minus these two
> columns and lets you continue entering the query.

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

        hp

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

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
>>> 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).
>> Maybe for this specific use case it's easier to teach psql how to do that,
>> instead of trying to amend the SQL implementation? Example:
>>
>> SELECT * \- col1 \- col2 FROM table
>>
>> psql looks up the columns, translates * into the actual list minus these two
>> columns and lets you continue entering the query.
> 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.

The main concern listed further upstream is "surfing the database", in
interactive mode. Not the first time I hear this problem.

Solving this specific case might reduce the need for a SQL extenson.

Note: the attempt above is just an idea, not an actual proposal how to
implement this.


Regards,

-- 
                Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

On 2022-02-12 20:50:57 +0100, 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
> > >
> > > 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).
> >
> > 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.

A shell could also provide an "expand select list" function using
explain.

In fact, you can sort of do that manually:

1) Prefix your query with explain(verbose)
2) Copy the "Output:" line of the top node.
3) Edit your query, remove the explain(verbose) and replace the select
list with the content of the clipboard
4) (optional) remove any unwanted columns

A bit cumbersome but less cumbersome than typing/copying lots of column
names from the result of a previous query or \d.

        hp

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

On 2/12/22 13:17, Peter J. Holzer wrote:
> On 2022-02-12 20:50:57 +0100, 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
>>>>
>>>> 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).
>>>
>>> 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.
> 
> A shell could also provide an "expand select list" function using
> explain.
> 
> In fact, you can sort of do that manually:
> 
> 1) Prefix your query with explain(verbose)
> 2) Copy the "Output:" line of the top node.
> 3) Edit your query, remove the explain(verbose) and replace the select
> list with the content of the clipboard
> 4) (optional) remove any unwanted columns

Or:

\pset format csv

select * from cell_per limit 0;

line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category

Longer version:

\pset format unaligned
\pset fieldsep ','

select * from cell_per  limit 0;
line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category
(0 rows)


> 
> A bit cumbersome but less cumbersome than typing/copying lots of column
> names from the result of a previous query or \d.
> 
>          hp
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

On 2022-02-12 13:23:39 -0800, Adrian Klaver wrote:
> On 2/12/22 13:17, Peter J. Holzer wrote:
> > A shell could also provide an "expand select list" function using
> > explain.
> >
> > In fact, you can sort of do that manually:
> >
> > 1) Prefix your query with explain(verbose)
> > 2) Copy the "Output:" line of the top node.
> > 3) Edit your query, remove the explain(verbose) and replace the select
> > list with the content of the clipboard
> > 4) (optional) remove any unwanted columns
>
> Or:
>
> \pset format csv
>
> select * from cell_per limit 0;
>
> line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category

Good idea. Even better:

select * from cell_per limit 0 \g (format=csv)

Saves you having to stash the query somewhere.

(You still have to look out for duplicate column names, though)

        hp

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

On 12/02/2022 22:34, Peter J. Holzer wrote:
> 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 complaint is not about complex queries, or CTEs, or Joins. This is
about simple queries where a user wants to discover - surf - the database
and look into specific tables, but exclude certain columns. More 
specifically,
this is when the user types in interactive queries.

Today psql tries to do autocomplete for certain scenarios, this too does
not work in complex queries, but nevertheless is a useful help if someone
tries to run simple, interactive queries.

Same can be true for the reserve situation where someone wants to exclude
specific columns, basically expanding * to a column list.


Regards,

-- 
                Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

The complaint is not about complex queries, or CTEs, or Joins. This is
about simple queries where a user wants to discover - surf - the database
and look into specific tables, but exclude certain columns. More specifically,
this is when the user types in interactive queries.

There is already something very similar to what you are describing:

https://www.psycopg.org/docs/cursor.html

Each cursor has its description, which consists of the column descriptions. Basically, it's like doing \d on a cursor. Unfortunately, it's not interactive, one has to do some pythong programming in order do to that. Unfortunately, it is not possible to just "describe the cursor", the description becomes available after the "execute" call. Hopefully, I understood you correctly.

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

On 2022-02-13 01:11:16 +0100, Andreas 'ads' Scherbaum wrote:
> On 12/02/2022 22:34, Peter J. Holzer wrote:
> > 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.
[...]
> > > 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 complaint is not about complex queries, or CTEs, or Joins. This is
> about simple queries where a user wants to discover - surf - the database
> and look into specific tables, but exclude certain columns.

If you look back through this thread you will notice that it was me who
brought up that specific scenario. You might trust me to know what I was
thinking about when I wrote it ;-).

> More specifically, this is when the user types in interactive queries.

I do write quite complex queries interactively. Even when "surfing",
joins are common because in a normalized schema many columns just
contain meaningless foreign keys. But I also do some data analysis
interactively (which is where CTEs usually come in) and I like to
develop queries interactively before putting them into (Python) programs.

But even if I didn't do that - if you add a feature like that to psql,
it should work for all queries, not just a tiny subset.

> Today psql tries to do autocomplete for certain scenarios, this too does
> not work in complex queries, but nevertheless is a useful help if someone
> tries to run simple, interactive queries.

Autocomplete doesn't even work well for simple queries. It's better than
nothing, but I don't think that it should serve as a model for new
features.

        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-12 20:12:02 -0500, Mladen Gogala wrote:
> On 2/12/22 19:11, Andreas 'ads' Scherbaum wrote:
>
>     The complaint is not about complex queries, or CTEs, or Joins. This is
>     about simple queries where a user wants to discover - surf - the database
>     and look into specific tables, but exclude certain columns. More
>     specifically,
>     this is when the user types in interactive queries.
>
> There is already something very similar to what you are describing:
>
> https://www.psycopg.org/docs/cursor.html

I'm not sure whether the PEP 249 notion of a cursor is relevant here.
That's quite Python specific and at least one step removed from the SQL
concept of a cursor.


> Each cursor has its description, which consists of the column descriptions.

Not really. While description is a property of the cursor object in
Python, it always describes the last query executed within that cursor:

% python3
Python 3.8.10 (default, Nov 26 2021, 20:14:08)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> db = psycopg2.connect("")
>>> csr = db.cursor()
>>> csr.description

(no output)

>>> csr.execute("select * from t1 natural join t2")
>>> csr.description
(Column(name='a', type_code=23), Column(name='b', type_code=23), Column(name='c', type_code=23))
>>> csr.fetchall()
[(1, 2, 11), (1, 2, 10)]
>>> csr.description
(Column(name='a', type_code=23), Column(name='b', type_code=23), Column(name='c', type_code=23))

(we can still refer to the description even after fetching all the data)

>>> csr.execute("select x from t1 natural join t2")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.errors.UndefinedColumn: column "x" does not exist
LINE 1: select x from t1 natural join t2

>>> csr.description

(no output again after a failed query)


> Basically, it's like doing \d on a cursor. Unfortunately, it's not interactive,
> one has to do some pythong programming in order do to that. Unfortunately, it
> is not possible to just "describe the cursor", the description becomes
> available after the "execute" call.

Yup, as demonstrated above. Which means that you have to actually
execute the query. Which is something that a should not happen as a side
effect of editing the query.

        hp

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

But there can be a valid second question - it can be nice to use extensions with availability to define their own communication protocol. Postgres has a special protocol for replication or for backup. With this possibility you can do what you need without the necessity of an external application server.

Regards

And here we are back on the Babelfish question. Babelfish defines its own protocol (TDS = Table Data Streaming) but it requires building PostgreSQL from the source, with some hooks for the additional protocol. A built in mechanism to do that without rebuilding would be nice. Babelfish Postgres is version 13.5, currently the most prevalent version in the data centers. A library to implement foreign communication protocol primitives would be a very nice thing indeed.

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

On Sun, Feb 13, 2022 at 4:00 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> ne 13. 2. 2022 v 10:45 odesílatel Guyren Howe <guyren@gmail.com> napsal:
>>
>>
>> The MySQL autocomplete is designed without context filtering. Maybe we can have this implementation too (as
alternative)
>>
>> so using all column names + all table names + aliases.column names (when we know defined alias)
>>
>> Another idea about column excluding. Any implementation on the client side is very complex, because you need to
parsesql. But maybe we can enhance SQL with some syntax. 
>>
>> SELECT ... and LAST optional clause can be HIDE, OR EXCLUDE NAME or EXCLUDE TYPE
>>
>> SELECT * FROM postgistab HIDE TYPE LIKE 'wkb%'
>> SELECT * FROM postgistab ORDER BY ... EXCLUDE COLUMN LIKE 'shape%'
>>
>> WITH x AS (SELECT * FROM xx)
>> SELECT * FROM x EXCLUDE COLUMN x1,x2
>>
>> The column excluding should be separate *last* clase.
>>
>> More with this syntax is less possibility of collision with ANSI SQL
>>
>> Not against this. Seems somewhere in here might be a nice quality of life change.
>>
>> Still.
>>
>> I originally suggested that SQL is terrible and we should fearlessly either replace it or migrate it toward
somethingbetter. And the thread winds up with a debate about a minor change to a development tool. 
>>
>> I’m back to just having no earthly idea why anyone who finds relations to be a productive tool for building a model
wouldthink that SQL being the only means to do that is Okay. 
>
> I think the rating of data langues is very subjective, and I am happy with SQL more than with special languages like
Dor Quel, or other. I know SQL has a lot of disadvantages, but it was designed for humans and it works for me. 

Exactly. SQL is proven to be more productive and code written in it
has longer longevity than alternatives.  It's also generally more
terse in the hands of a good author.  The authors of all the 'SQL
sucks' rants don't really explore why this is the case.  For example,
SQL has transactions and pretty much all other major languages don't.
They may have it in a limited sense but not standardized throughout
the syntax and the standard libraries.  High quality automatic
concurrency models are another factor.


merlin

On Tue, Feb 15, 2022 at 02:18:35PM -0600, Merlin Moncure wrote:
> Exactly. SQL is proven to be more productive and code written in it
> has longer longevity than alternatives.  It's also generally more
> terse in the hands of a good author.  The authors of all the 'SQL
> sucks' rants don't really explore why this is the case.  For example,
> SQL has transactions and pretty much all other major languages don't.
> They may have it in a limited sense but not standardized throughout
> the syntax and the standard libraries.  High quality automatic
> concurrency models are another factor.

What I found with QUEL was that simple things were easier than SQL, but
things like aggregates and subqueries were harder, confusing. or
impossible.

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

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

On 2022-Feb-13, Guyren Howe wrote:

> I’m back to just having no earthly idea why anyone who finds relations
> to be a productive tool for building a model would think that SQL
> being the only means to do that is Okay.

There are aspects other than technical reasons alone why some things
live on while "better" things barely survive without thriving, or even
die.  For example, the fact that there are multiples companies
furthering the development of the SQL language means that there's a
group of engineers working to introduce improvements to the language
after extensive discussions and exposure to requests from users.  If
Postgres decided to "fork" and go solitary with its own query language,
it would by itself have to produce all the language improvements, or be
left behind by the other products.  And it takes a lot of effort to
produce those improvements.  Have you looked at how SQL changed from one
version to the next?

Another aspect is inertia.  The amount of software products that rely on
SQL is just too high.  Suppose you came up with a technically-better
language that has all the current capabilities of SQL.  Do you think a
majority of those products would immediately switch to the new language?
My guess is no, they wouldn't, because the safe bet is that SQL will
continue to work in 10, 20 years, while there is no certainty at all
that your new language would.  So by ditching SQL, Postgres would no
longer be a database of choice for those products.

So, while SQL may not be the greatest possible relational language
possible, there are very good reasons for it to continue to be the
language of choice.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/