A Small psql Suggestion
Greetings, There is (for me) a small speed bump in psql. I think it's worth mentioning, minor though it is, because psql is such a polished tool generally, and because it's something which affects me many, many times a day. As it is, \d is a shortcut for \dtmvs. What I actually want to see, on a regular basis, are my relations: \dtmv. Most of the time, the sequences are clutter. If my habits are like most people's in this (and I suspect they are), excluding sequences from \d would optimize for the common case. Just $0.02, submitted for your gracious consideration. -- Ray Brinzer
Re: A Small psql Suggestion
I was really busy with work last week, so I didn't get around to thanking you, depesz. Setting d is a clever trick which hadn't occurred to me, and it has indeed made things nicer for me. I do think it would be a good thing to actually change in psql nevertheless, since I think the suggested behavior is better most of the time, especially if all or most of your tables have sequences. The built-in ability to work around it (to a good approximation of the desired behavior) certainly does diminish the importance of the issue, though such a solution won't be obvious to most people. Again, though, my thanks. This has been like a rough spot on the handle of a tool: trivial for occasional use, prone to raise a blister over thousands of repetitions. On Wed, Feb 1, 2023 at 11:04 AM hubert depesz lubaczewski wrote: > On Tue, Jan 31, 2023 at 11:17:16AM -0500, Raymond Brinzer wrote: > > Greetings, > > > > There is (for me) a small speed bump in psql. I think it's worth > > mentioning, minor though it is, because psql is such a polished tool > > generally, and because it's something which affects me many, many times a > > day. > > > > As it is, \d is a shortcut for \dtmvs. What I actually want to see, on a > > regular basis, are my relations: \dtmv. Most of the time, the sequences > > are clutter. If my habits are like most people's in this (and I suspect > > they are), excluding sequences from \d would optimize for the common > case. > > Perhaps just add this yourself? > \set d '\\dtmv' > and then > :d > > or just bind \dtmv to some key like f1 or something like this? > > Best regards, > > depesz > > -- Ray Brinzer
Re: simple division
On Wed, Dec 5, 2018 at 7:55 AM Geoff Winkless wrote: > Where's the logical progression in step 3 here: > > 1 You asked the computer a question > > 2 The values you passed to it don't have decimal points > > ... > > 4 Ergo, you wanted an answer that was incorrect. > Well put. However the nature of the question you asked does not necessarily allow for a correct finite answer. If I ask for 10/3, for instance, or 1/0. To play devil's advocate, then: you should have known that some of the answers would need to be truncated. This just truncates a little more aggressively. ;-) -- Ray Brinzer
Re: Where **not** to use PostgreSQL?
I often avoid PostgreSQL when using software for which PostgreSQL support is secondary. Usually this is the case where MySQL is the default, but PostgreSQL is on the "also supported" list. "Also" is too often a synonym for "badly", here, and unless I really want to be able to approach the underlying database *as a database*, the better choice is to go with the flow. Even when I do need that, I'll consider whether the features I need are exclusive to, or much better on, PostgreSQL. When developing something myself, I've also chosen MySQL because the other technical people likely to be involved are at least somewhat familiar with it. A person who is marginally competent with databases doesn't need the added difficulty of learning a new DBMS while learning whatever I created. It's always a pleasure when I don't have such issues, and I can use PostgreSQL. On Thu, Feb 28, 2019 at 6:47 AM Thomas Güttler wrote: > > Hi experts, > > where would you suggest someone to **not** use PostgreSQL? > > Why would you do this? > > What alternative would you suggest instead? > > > Regards, >Thomas Güttler > > > -- > Thomas Guettler http://www.thomas-guettler.de/ > I am looking for feedback: https://github.com/guettli/programming-guidelines > -- Ray Brinzer
Records, Types, and Arrays
Greetings, all.
It's been a down-the-rabbit-hole day for me. It all started out with a
simple problem. I have defined a composite type. There are functions
which return arrays whose values would be suitable to the type I defined.
How do I turn arrays into composite typed values?
Conceptually, this is straightforward. Any given array can be mapped to a
corresponding record with the same elements, so this expression would make
sense:
ARRAY[1,2,3]::RECORD
If the result happens to be a valid instance of my_type, you might say:
ARRAY[1,2,3]::RECORD::my_type
Or, ideally, just:
ARRAY[1,2,3]::my_type
It seems to be a rather long way from the idea to the implementation,
however. A helpful soul from the IRC channel did manage to make this
happen in a single expression:
(format('(%s)', array_to_string(the_array, ','))::my_type).*
While I'm happy to have it, that's ugly even by SQL's syntactic yardstick.
So, I figured I'd see about hiding it behind a function and a custom cast.
These efforts have not been successful, for reasons I'll probably share in
a subsequent email, as the details would distract from the point of this
one.
Getting to that point... we have these three kinds of things:
* Arrays
* Composite Values / Records
* Typed Composite Values (instances of composite types)
(Note on the second: while section 8.16.2 of the documentation talks about
constructing "composite values", pg_typeof() reports these to be of the
"record" pseudo-type. To (hopefully) avoid confusion, I'm going to
exclusively say "record" here.)
Here's the thing about these: in the abstract, they're mostly the same. A
record is simply an ordered multiset. If you ignore implementation,
syntax, and whatnot, you could say that arrays are the subset of records
where all the members are of the same type. Objects of composite type can
be considered records with an additional feature: each member has a name.
It seems to me, then, that:
1) Switching between these things should be dead easy; and
2) One should be able to treat them as similarly as their actual
differences allow.
On the first point (speaking of arrays and composite types generically),
there are six possible casts. One of these already works, when members are
compatible:
record::composite_type
(Mostly, anyway; I did run into a kink with it, which I'll explain when I
discuss what I've tried.)
These casts would always be valid:
array::record
composite_type::record
These would be valid where the member sets are compatible:
array::composite_type
record::array
composite_type::array
It seems like having all six casts available would be very handy. But
(here's point 2) to the extent that you don't have to bother switching
between them at all, so much the better. For instance:
(ARRAY[5,6,7])[1]
(ROW(5,6,7))[1]
(ROW(5,6,7)::my_type)[1]
all make perfect sense. It would be lovely to be able to treat these types
interchangeably where appropriate. It seems to me (having failed to
imagine a counterexample) that any operation you could apply to an array
should be applicable to a record, and any operation you could apply to a
record should be applicable to an instance of a composite type.
While the second point is rather far-reaching and idealistic, the first
seems well-defined and reasonably easy.
If you've taken the time to read all this, thank you. If you take the idea
seriously, or have practical suggestions, thank you even more. If you
correct me on something important... well, I owe much of what I know to
people like you, so please accept my deepest gratitude.
--
Yours,
Ray Brinzer
Re: Records, Types, and Arrays
On Fri, May 19, 2023 at 1:42 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, May 18, 2023 at 10:06 PM Raymond Brinzer > wrote: > >> How do I turn arrays into composite typed values? >> > > Using just SQL syntax and no string munging: > > (array_val[1]::col1_type, array_val[2]::col2_type)::composite_type > Assuming one wants to write a specific solution, rather than a general one, sure. And when you want to deal with an unnamed array returned from a function? Well, you can throw *that* in a CTE to give it a name, or perform some other such contortion. The aggregate load of having to phrase such simple ideas in complicated ways really isn't good. > > > None of what you are saying is likely to ever see the light of day. If > you want to learn the SQL-way might be easier to just forget about your > idealized equivalency between composite types and array containers. > The problem with "easier" is that addressing directly in front of you is always easier in the immediate sense than actually attacking the problem itself. It also dooms you to the (after)life of Sisyphus, always rolling the same rock up the same hill. > ARRAY[...] is a constructor, its output is an array container. You can > either type the elements within the constructor or leave them untyped and > put a syntactical-sugar cast on the result. > > ARRAY['2023-01-01'::date,'2023-02-01'::date] > ARRAY['2023-01-01','2023-02-01']::date[] > > While you've said that having all this stuff would "be quite handy" that > isn't obvious to me. It is even less obvious that any benefit would likely > be small compared to the effort to make all of this actually work. > Well, making one small part of it work would be a boon to me. Is a simple, generic cast from an array to a record really rocket science? I can't imagine why that would be. > Even if I could write: composite_type[1] instead of composite_type.column1 > I don't know why I'd want to give up the expressiveness of writing the > column name. > Naturally, you wouldn't give up the ability to do that. You'd merely gain the ability to do it another way. -- Ray Brinzer
Re: Records, Types, and Arrays
On a problem which came up while trying to implement a solution, perhaps
someone could explain this:
scratch=# create type test_type as (a int, b int);
CREATE TYPE
scratch=# create function get_row() returns record as $$ select row(2,3);
$$ language sql;
CREATE FUNCTION
scratch=# select get_row();
get_row
-
(2,3)
(1 row)
scratch=# select pg_typeof( get_row() );
pg_typeof
---
record
(1 row)
scratch=# select pg_typeof( row(2,3) );
pg_typeof
---
record
(1 row)
scratch=# select row(2,3)::test_type;
row
---
(2,3)
(1 row)
scratch=# select get_row()::test_type;
ERROR: cannot cast type record to test_type
LINE 1: select get_row()::test_type;
If row(2,3) and get_row() are both of type record, and the records have the
same values, why can one be cast to test_type, and the other not?
On Fri, May 19, 2023 at 1:07 AM Raymond Brinzer
wrote:
> Greetings, all.
>
> It's been a down-the-rabbit-hole day for me. It all started out with a
> simple problem. I have defined a composite type. There are functions
> which return arrays whose values would be suitable to the type I defined.
> How do I turn arrays into composite typed values?
>
> Conceptually, this is straightforward. Any given array can be mapped to a
> corresponding record with the same elements, so this expression would make
> sense:
>
> ARRAY[1,2,3]::RECORD
>
> If the result happens to be a valid instance of my_type, you might say:
>
> ARRAY[1,2,3]::RECORD::my_type
>
> Or, ideally, just:
>
> ARRAY[1,2,3]::my_type
>
> It seems to be a rather long way from the idea to the implementation,
> however. A helpful soul from the IRC channel did manage to make this
> happen in a single expression:
>
> (format('(%s)', array_to_string(the_array, ','))::my_type).*
>
> While I'm happy to have it, that's ugly even by SQL's syntactic
> yardstick. So, I figured I'd see about hiding it behind a function and a
> custom cast. These efforts have not been successful, for reasons I'll
> probably share in a subsequent email, as the details would distract from
> the point of this one.
>
> Getting to that point... we have these three kinds of things:
>
> * Arrays
> * Composite Values / Records
> * Typed Composite Values (instances of composite types)
>
> (Note on the second: while section 8.16.2 of the documentation talks
> about constructing "composite values", pg_typeof() reports these to be of
> the "record" pseudo-type. To (hopefully) avoid confusion, I'm going to
> exclusively say "record" here.)
>
> Here's the thing about these: in the abstract, they're mostly the same.
> A record is simply an ordered multiset. If you ignore implementation,
> syntax, and whatnot, you could say that arrays are the subset of records
> where all the members are of the same type. Objects of composite type can
> be considered records with an additional feature: each member has a name.
>
> It seems to me, then, that:
>
> 1) Switching between these things should be dead easy; and
> 2) One should be able to treat them as similarly as their actual
> differences allow.
>
> On the first point (speaking of arrays and composite types generically),
> there are six possible casts. One of these already works, when members are
> compatible:
>
> record::composite_type
>
> (Mostly, anyway; I did run into a kink with it, which I'll explain when I
> discuss what I've tried.)
>
> These casts would always be valid:
>
> array::record
> composite_type::record
>
> These would be valid where the member sets are compatible:
>
> array::composite_type
> record::array
> composite_type::array
>
> It seems like having all six casts available would be very handy. But
> (here's point 2) to the extent that you don't have to bother switching
> between them at all, so much the better. For instance:
>
> (ARRAY[5,6,7])[1]
> (ROW(5,6,7))[1]
> (ROW(5,6,7)::my_type)[1]
>
> all make perfect sense. It would be lovely to be able to treat these
> types interchangeably where appropriate. It seems to me (having failed to
> imagine a counterexample) that any operation you could apply to an array
> should be applicable to a record, and any operation you could apply to a
> record should be applicable to an instance of a composite type.
>
> While the second point is rather far-reaching and idealistic, the first
> seems well-defined and reasonably easy.
>
> If you've taken the time to read all this, thank you. If you take the
> idea seriously, or have practical suggestions, thank you even more. If you
> correct me on something important... well, I owe much of what I know to
> people like you, so please accept my deepest gratitude.
>
> --
> Yours,
>
> Ray Brinzer
>
--
Ray Brinzer
Re: Records, Types, and Arrays
Sorry, I should have noted this as well:
"One should also realize that when a PL/pgSQL function is declared to
return type record, this is not quite the same concept as a record
variable, even though such a function might use a record variable to hold
its result. In both cases the actual row structure is unknown when the
function is written, but for a function returning record the actual
structure is determined when the calling query is parsed, whereas a record
variable can change its row structure on-the-fly."
I'm guessing that row() isn't really a function, then? And even so,
assuming this is the important difference, how is the ability to change row
structure on the fly making the cast possible? In what way would the query
calling get_row() be critical?
On Fri, May 19, 2023 at 2:48 AM Raymond Brinzer
wrote:
> On a problem which came up while trying to implement a solution, perhaps
> someone could explain this:
>
> scratch=# create type test_type as (a int, b int);
> CREATE TYPE
> scratch=# create function get_row() returns record as $$ select row(2,3);
> $$ language sql;
> CREATE FUNCTION
> scratch=# select get_row();
> get_row
> -
> (2,3)
> (1 row)
>
> scratch=# select pg_typeof( get_row() );
> pg_typeof
> ---
> record
> (1 row)
>
> scratch=# select pg_typeof( row(2,3) );
> pg_typeof
> ---
> record
> (1 row)
>
> scratch=# select row(2,3)::test_type;
> row
> ---
> (2,3)
> (1 row)
>
> scratch=# select get_row()::test_type;
> ERROR: cannot cast type record to test_type
> LINE 1: select get_row()::test_type;
>
> If row(2,3) and get_row() are both of type record, and the records have
> the same values, why can one be cast to test_type, and the other not?
>
> On Fri, May 19, 2023 at 1:07 AM Raymond Brinzer
> wrote:
>
>> Greetings, all.
>>
>> It's been a down-the-rabbit-hole day for me. It all started out with a
>> simple problem. I have defined a composite type. There are functions
>> which return arrays whose values would be suitable to the type I defined.
>> How do I turn arrays into composite typed values?
>>
>> Conceptually, this is straightforward. Any given array can be mapped to
>> a corresponding record with the same elements, so this expression would
>> make sense:
>>
>> ARRAY[1,2,3]::RECORD
>>
>> If the result happens to be a valid instance of my_type, you might say:
>>
>> ARRAY[1,2,3]::RECORD::my_type
>>
>> Or, ideally, just:
>>
>> ARRAY[1,2,3]::my_type
>>
>> It seems to be a rather long way from the idea to the implementation,
>> however. A helpful soul from the IRC channel did manage to make this
>> happen in a single expression:
>>
>> (format('(%s)', array_to_string(the_array, ','))::my_type).*
>>
>> While I'm happy to have it, that's ugly even by SQL's syntactic
>> yardstick. So, I figured I'd see about hiding it behind a function and a
>> custom cast. These efforts have not been successful, for reasons I'll
>> probably share in a subsequent email, as the details would distract from
>> the point of this one.
>>
>> Getting to that point... we have these three kinds of things:
>>
>> * Arrays
>> * Composite Values / Records
>> * Typed Composite Values (instances of composite types)
>>
>> (Note on the second: while section 8.16.2 of the documentation talks
>> about constructing "composite values", pg_typeof() reports these to be of
>> the "record" pseudo-type. To (hopefully) avoid confusion, I'm going to
>> exclusively say "record" here.)
>>
>> Here's the thing about these: in the abstract, they're mostly the same.
>> A record is simply an ordered multiset. If you ignore implementation,
>> syntax, and whatnot, you could say that arrays are the subset of records
>> where all the members are of the same type. Objects of composite type can
>> be considered records with an additional feature: each member has a name.
>>
>> It seems to me, then, that:
>>
>> 1) Switching between these things should be dead easy; and
>> 2) One should be able to treat them as similarly as their actual
>> differences allow.
>>
>> On the first point (speaking of arrays and composite types generically),
>> there are six possible casts. One of these already works, when members are
>> compatible:
>>
>> record::composite_type
>>
>> (Mostly, anyway; I did run into a kink with it, which I'll explain when I
>> discuss what I've tried.)
>>
&g
Re: Records, Types, and Arrays
On Fri, May 19, 2023 at 2:58 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, May 18, 2023, Raymond Brinzer wrote: > >> scratch=# select row(2,3)::test_type; >> > > Unknown typed value, immediately converted to a known concrete instance of > test_type. It is never actually resolved as record. > > All of the others must concretely be resolved to record to escape their > query level, and if you then try to cast the concrete record to some other > concrete type a cast needs to exist. > I see. That suggests, oddly, that pg_typeof() is changing the thing it's observing, because row(2,3) was not a record but an 'unknown typed value' before pg_typeof() was called on it. Good to know. CREATE CAST seems not to like working with pseudo-types. Neither the source nor the target can be 'anyarray' or 'record'. So that seems out. On the other hand, the pseudo-type doc says, 'Functions coded in C (whether built-in or dynamically loaded) can be declared to accept or return any of these pseudo data types.' I'm assuming that an 'unknown typed value" corresponds to the 'unknown' pseudo-type. So it seems like a C function which took an anyarray as its parameter, and returned a value of type 'unknown' would bypass the need for a specifically defined cast. (Emphasis on "seems".) -- Ray Brinzer
Re: Question regarding the new SQL standard
I'd like to express my appreciation for a project with a feature set so extensive that a person might mistakenly assume that its goal was to cover the spec comprehensively. On Thu, Oct 26, 2023 at 10:32 AM Tom Lane wrote: > Anders Kildemand writes: > > What's the situation with Property Graph Queries in Postgres? > > There isn't any. > > > I've heard that it has become part of the SQL standard from this blog: > https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new > > How far in development is this feature? and is there an expected time > frame for when it'll be finished? > > I think you have a fundamental misunderstanding about how Postgres > development works. There is plenty of stuff in the standard that > nobody has any particular interest in implementing for PG. Perhaps > someone will take an interest in implementing this feature, or > perhaps not. But there's no project-wide goal to make it happen, > much less a timetable. Even if someone does take an interest, > they might not succeed in producing a committable patch. > > regards, tom lane > > > -- Ray Brinzer
Re: The tragedy of SQL
Many languages are awesome. I'm always astonished at what great things people have come up with, over the years; it's been a wonderfully fertile field. We would certainly not be better off if we'd just buckled down, and used COBOL and FORTRAN... or even relatively good languages like C, APL, and Lisp. It is certainly possible to change too lightly, for small reasons. That doesn't mean that forever enduring the same problems is a good idea. On Tue, Sep 14, 2021 at 2:18 AM Rob Sargent wrote: > > On 9/13/21 11:51 PM, Guyren Howe wrote: > > They are making a decent decision. SQL is a *fucking terrible* language, > which I don’t blame them for not wanting to learn. > > The whole industry, programming languages, infrastructure, everything would > have developed differently if relations were a natural, pleasurable thing to > use in any programming language. Like an Array, or a Hash. > On Sep 13, 2021, 22:45 -0700, Hemil Ruparel , > wrote: > > SQL is not the problem. Problem are the devs. I love SQL. I hate orms. The > problem with databases is people refuse to treat it as the entity it is and > want to use their beautiful OO system. Problem is databases are not OO. We > need to recognize that and treat databases as databases. > > All languages are fucking terrible. There are thousands of the them because > some people bump into a feature they don't like and run off an make another > fucking terrible language. For the love of God, please don't be one of those > people. The rest of us find languages we can abide and do productive things > with using features we like and avoiding those we don't. I've always felt it > was no small miracle the vendors managed to agree to ODBC/JDBC driver specs > (even though the SQL language definition is "more like guidelines"). Go > scream at the DOM and JavaScript. -- Ray Brinzer
Re: The tragedy of SQL
This is a subject which is important to me, but I find discussing it often goes poorly. Most people (not necessarily those on this list) don't distinguish between SQL and the relational model. When you criticize SQL the language, people tend to defend relational databases; when you praise relational databases, people have a visceral reaction to SQL. There also seems to be a divide between people who use languages to express their thoughts, with the expectation that their thoughts will be implemented, and those who regard a language merely as an interface for manipulating an underlying system. I think there's a lot of good to be said of workmen who get the job done without complaining about their tools. But in the big picture, it seems to me that all the progress we've made with computers has been a matter of improving the toolchain. The CPU is, after all, an underlying system, and there's nothing you couldn't get done with assembler (or just machine code). If you were smart enough, and had enough time. The problem is, tools tend to impose an "IQ tax": thought spent on managing the tool is thought not spent on solving the problem. I tend to be stingy about paying that; I'm not smart enough, and I don't have enough time. Merlin's point about algebraic syntax fits well, here. Once you're used to it, (x ∩ y) imposes less of a cognitive load than SELECT * FROM x INTERSECT SELECT * FROM y. You can see how that scales, as expressions get larger. There's a reason we no longer try to make programming languages look like English, or other natural languages, however reasonable it might have seemed in the 1970s. And then there are very simple things I can't say reasonably, like "SELECT * EXCEPT col_3", or "Tell me how many nulls are in each column." Naturally, I can get these done; but the gap between what was required to explain the goal and what is required to accomplish it is much too large. So, the affection I have for SQL is due to it being a gateway to a great idea; my frustration is that it's a bottleneck in getting to that same idea. On Tue, Sep 14, 2021 at 12:20 PM Rob Sargent wrote: > > On 9/14/21 10:10 AM, Michael Nolan wrote: > > I started programming in 1967, and over the last 50+ years I've programmed in > more languages than I would want to list. I spent a decade writing in > FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited > memory space, so you had to write EFFICIENT code, something that is a bit of > a lost art these days. I also spent a decade writing in COBOL. > > I've not found many tasks that I couldn't find a way to write in whatever > language I had available to write it in. There may be bad (or at least > inefficient) languages, but there are lots of bad programmers. > -- > Mike Nolan > htf...@gmail.com > > OK, I'm maybe responsible for this thread turning into a diatribe. I shouted > at OP 'cause he shouted at us. My mistake, and I apologize. > I'm probably closer to Mike's "bad programmers" than I would care to admit > but fully believe software is a "people problem" more than most of us realize. -- Ray Brinzer
Re: The tragedy of SQL
On Tue, Sep 14, 2021 at 2:41 PM Brian Dunavant wrote: > I have the opposite perspective. As a dev/manager, SQL is much more powerful > at getting data storage from abstract concept, into a usable structure, than > any programming language I've had the (mis)fortune of using. I've long > since lost count of the massive volume of other people's code (especially > ORMs) I've removed and replaced by updating SQL statements to do all the > logic, and return me exactly what I want. And typically this also comes with > a (sometimes massive) performance gain. > > I've managed many a programmer that would complain that SQL is too hard and > they don't want to learn it, but had no problem spending days learning the > ORM of the month that "saves them time" and writing complex inscrutable > monstrosities with them. > > Could SQL be better? Absolutely. But in terms of bang-for-my-buck, I feel > learning SQL has saved me more clock-time, and improved my productivity/value > probably more than any other individual language in my career. Your experience doesn't surprise me at all. Sure; it's better than the alternatives. An ORM can be a net benefit if you're doing simple things, but the more complex the query, the more it starts to feel like you're trying to have a serious conversation through a bad translator. This encourages programmers to keep queries simple, treat the database as a big scratchpad, and do all the processing in code. This easily turns into "reinventing the wheel, badly". I would argue, though, that the programmers aren't completely wrong. A good programmer strives for clarity, expressing ideas simply and naturally, and avoiding repetition; SQL isn't good for that. But papering over the problem on the software side isn't the solution. I'd just emphasize our agreement: SQL (or another query language, sitting in the same niche) could be better. So it should be. -- Ray Brinzer
Re: The tragedy of SQL
On Tue, Sep 14, 2021 at 3:58 PM Guyren Howe wrote: > You’re confusing SQL with the relational model. Datalog and Quel and Tutorial > D and other database languages and systems can and did provide those features > also. By analogy: Arabic and Roman numerals both describe the natural numbers. Hence, they have the same mathematical properties. Spending a little time doing algebra with Roman numerals should convince anyone, however, that how you express a concept matters a lot. -- Ray Brinzer
Re: The tragedy of SQL
On Tue, Sep 14, 2021 at 4:16 PM FWS Neil wrote: > What people hate about SQL is that the programmer has to optimize SQL to get > acceptable performance. Oh, no, that's just one thing. :-) And to be fair, it's a hard problem. We're asking for an optimizer, written for databases generally, to out-perform an intelligent human who knows one particular database well. And we don't collect all the data the optimizer might want, because of the performance or storage costs to the collection (e.g. keeping accurate record totals by performing a write operation on every insert). In my daydreams, I sometimes think that making the query planner more modular, and perhaps writing it in a higher-level language might be good. Usually, optimizing for fast performance will beat optimizing for performance fast. So it's a system you'd want to be able to tune and improve easily. > And the optimization is different for every implementation. I think SQL has > not hit its stride yet. When the common $1000 server has 1024+ CPUs and 1+TB > memory, and SQL implementations have adopted good multithreading architecture > with access to 1024+ CPU dedicated AI engines, etc. a lot of the crap > associated with performant SQL will go away. Yeah... I don't. When a common server is 1000 times faster, people will find reasons to do 1000 times as much with it. > At this point, I think it will be smart to strip out implementation details > that have made it into the SQL syntax. There will no longer be a need for > it. This will make the SQL language simpler and easier to use, understand, > and reason about. A clean division between the query and the performance hints seems like it'd be a big improvement. Rather like moving presentation details from HTML off into CSS. Again, I don't see them going away, though. -- Ray Brinzer
Re: The tragedy of SQL
So, on a practical note: I'd like it if PostgreSQL supported
alternate languages for queries, as it does for stored procedures.
Yes, I know this will strike some of you as an abomination. I think
we can take that part as read. ;-)
I see two ways of going about this. The quick & dirty way would be to
conditionally hand off the incoming code to a pre-processor, which
would return SQL to be passed along to the rest of the parser. You'd
just add a few lines to parser.c, along the lines of:
#ifdef ALTERNATE_QUERY_LANGUAGE
str = preprocess_the_code(str);
#endif
The rest would be defined outside the existing code. I actually
experimented with this a few years ago, embedding some Chicken Scheme
into PostgreSQL, and it seemed to work reasonably well. Basically, I
looked to see if the incoming query started with "(" and if it didn't,
I just returned the string unaltered. If it did, I parsed as
s-expressions.
The "right", and more flexible way, would be to actually generate your
own parse tree, using the same nodes the native parser does. I'm
sorry to say I didn't stick with that to the point of getting anything
working. One encouraging thing, however is the fact that the parser
is mostly isolated from the rest of the system; if it was highly
integrated, it would be much harder. Although gram.y does hedge a bit
on this:
"In general, nothing in this file should initiate database accesses".
Anyway, one way or the other, I think it could be done.
On Tue, Sep 14, 2021 at 1:32 AM Guyren Howe wrote:
>
> A fun philosophical discussion.
>
> I am no fan of “worse is better”, and particularly its poster child, SQL.
>
> The world’s economic output would be substantially higher (5%?) if our
> industry had settled on almost anything other than SQL for relational
> databases.
>
> So much of the design of *almost everything* in our industry is a reaction to
> SQL. ORMs fucking *everywhere* so you don’t have to use SQL. Bad query and
> database design. Inefficient system designs that use ORMs rather than
> relations. NoSQL databases. Countless hours on hours of developer time trying
> to work out how to write something in SQL that would be trivial in, say,
> Datalog.
>
> If I had $5 million to invest in a startup, I would hire as many of the core
> Postgres devs as I could to make a new database with all the sophistication
> of Postgres but based on Datalog (or something similar). (Or maybe add
> Datalog to Postgres). If that could get traction, it would lead in a decade
> to a revolution in productivity in our industry.
--
Ray Brinzer
Re: SQL queries as sets: was The tragedy of SQL
On Wed, Sep 15, 2021 at 12:55 AM Steve Litt wrote:
> Rich, could you please elaborate on SQL queries being based on sets? I
> never thought of it that way, and would like to hear your related
> thoughts.
I'll take a crack at this. Going through the setup will require a
little patience, but I think the underlying idea is neat enough to be
worth it.
A set is an unordered collection of unique elements. This means:
{ 1, 2, 3 } = { 3, 2, 1 }
{ 2, 3, 3 } is not a set.
(The elements don't have to be numbers, but I'm using them for convenience.)
Take two sets, A and B:
A = { 1, 2, 3 }
B = { 4, 5, 6 }
The Cartesian product A x B is the complete set of ordered pairs (a,
b) you can make from them:
(1, 4)
(1, 5)
(1, 6)
(2, 4)
(2, 5)
(2, 6)
(3, 4)
(3, 5)
(3, 6)
These pairs are called tuples. Tuples don't have to be pairs; if you
were using A x B x C, for instance, each one would have three
elements.
A relation is a subset of the Cartesian product of the sets. For instance:
(1, 4)
(2, 5)
(3, 6)
(In math, some relations are functions; that one happens to be a
function which follows the rule f(a) = a + 3. But that's not really a
database matter.)
Anyway, now: for "relation", say "table", and for "tuple", say "row".
This is (in theory) is what a relational database is about: storing
relations.
In math, tuples are ordered, but with databases we give the elements
names, instead:
(a: 1, b: 4)
It doesn't really matter, because the names uniquely identify the
elements, just as ordering does. You can go back and forth between
orders and names, so the different representations have the same
structure.
So, let's say you do this:
CREATE TABLE whatever (a INTEGER, b STRING, UNIQUE(a,b));
What you are saying, in a sense, is: "Consider the set of tuples
which pair every possible integer with every possible string. I'm
going to be storing a subset of that."
What's interesting about this is: because you're working with sets,
all the operations you can perform on sets work here. Tables X and Y
are both sets of tuples, so you can find their cartesian product,
intersection, union, and so forth. Proofs about sets apply, and so
on.
That is, if the tables are actually relations... which means they have
to be sets. This is where things get controversial.
SQL allows tables to have duplicate records. It also has NULL, which
allows duplicate records even in a table like the one above. Although
we declared UNIQUE(a,b), we can still say:
INSERT INTO whatever VALUES (NULL,NULL), (NULL,NULL);
So, arguably SQL isn't actually relational. To some, that's a matter
of it being practical, so that people can actually get work done. To
others, it's a travesty which robs databases of power and clarity.
Anyway, that's my summary.
--
Ray Brinzer
Re: The tragedy of SQL
On Wed, Sep 15, 2021 at 2:46 AM Julien Rouhaud wrote: > I agree, and actually sent a patch some time ago to allow usage of > third-party parser(s). They can coexist with the core one, meaning > that you can (if you write your parser this way) use both languages, > even in a multi-query string. See > https://commitfest.postgresql.org/34/3100/ for more details. Bravo! I look forward to reading the code when I have time; seems like it will be a nice cheat sheet for the relevant internals, particularly as you say that you commented thoroughly. -- Ray Brinzer
Re: The tragedy of SQL
On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure wrote:
> I've long thought that there is more algebraic type syntax sitting
> underneath SQL yearning to get out.
I wanted to come back to this, because I've been looking to take a
single problem (from my perspective) and explain it concisely. Your
intuition is right on the mark.
Shell syntax is a pretty good lingua franca, so let's use it. Say you
were working at the command line, and you said something like:
cat somefile | awk '{print $3 " " $1 " " $5;}' | sort | grep "^Robert"
And the shell responded with something like: ERROR: syntax error at
or near "sort". After a little tinkering, you discover: that's
because the grep has to come before the sort. But why?
The database is not going to evaluate relational operations in order,
passing the output of one into the next as a shell pipe does.
Nevertheless, they are logically independent. Each should take in a
relation and either a second relation or a predicate, and return a
relation. Or, to put it mathily, relations are closed under
relational operations. So:
Operation 1 | Operation 2
and
Operation 2 | Operation 1
should both be valid, whether or not they're semantically equivalent
(though they often are). The operations are inherently atomic, and
can be understood in isolation. That's not the case here:
SELECT col_3, col_1, col_5 FROM sometable WHERE col_3 LIKE 'Robert%'
ORDER BY col_3, col_1, col_5;
Now, if this sort of thing suits the way you think, I say, "Great!"
I'm glad you have a language which suits you. For me, it's too rigid;
it assumes too much about what I might want to say. I wouldn't
program in a language like this, or use a shell like this. I don't
want to write database queries like this. I do, because it's how I
get to talk to the awesome toy in the background, but it always
chafes.
--
Ray Brinzer
Re: The tragedy of SQL
On Fri, Sep 17, 2021 at 7:49 AM Raymond Brinzer wrote: > Now, if this sort of thing suits the way you think, I say, "Great!" > I'm glad you have a language which suits you. Reading this over, I realized I should have been more clear: I mean "you" generally. I liked your comment about algebraic syntax; just giving my take on it, not attributing anything to you. -- Ray Brinzer
Nested Schemata, in a Standard-Compliant Way?
Greetings. For some people the "what?" and "why?" of this will be immediately obvious from the title, but I'm going to spend a little time on those before "whether?" and "how?" We have schemata. They're namespaces; very convenient for organizing things. They let you group tables and other entities together, and, by setting search_path, only see the ones which presently interest you. In fact, they're pretty similar to directories in a filesystem... except that they don't nest. Imagine a filesystem where you could have directories, but the directories could only contain files, not other directories. (Like the first Unix on the PDP-7, or DOS before 2.0.) You could, of course, use your own delimiters. And we do; often along the lines of: schema.category_subcategory_table. You can't really use these to establish context, however. The system doesn't recognize category_subcategory as a "place". So you can't easily deal with a subset of your tables, and the combination of many tables and long names tends to be messy. So, for example, I'd like to be able to say something like this: SELECT * FROM /projects/contacts/people; Or: cd /projects/contacts; SELECT * FROM people; We use / for division, so that probably isn't plausible, but it makes for a familiar example. I'm wondering whether such a feature could be added, without breaking either existing code, or compliance with the SQL standard. For instance, borrowing :: from languages like Ruby and Perl: SELECT * FROM ::projects::contacts::people; -- Absolute path cd ::projects; -- Session-specific SELECT * FROM contacts::people; -- Relative path I'm not necessarily saying this is the best delimiter, but the colon isn't valid in unquoted identifiers, so it's probably a choice which would have minimal impact. Now, you could do a fair job of this just within the client, but my thought is that this would be better if actually supported by the database. For instance, having representation in the system tables. So, then: can it be done? Should it be done? I can say easily that my database life would be better for having this, but there do tend to be those nasty lurking problems which aren't obvious. -- Ray Brinzer
Re: Nested Schemata, in a Standard-Compliant Way?
On Tue, Sep 28, 2021 at 10:13 AM rob stone wrote: > Double colons are used for casting. > E.g., $1::INET or $1::INTEGER where $1 is a string. Quite right; slipped my mind. Thank you. -- Ray Brinzer
Re: Nested Schemata, in a Standard-Compliant Way?
On Tue, Sep 28, 2021 at 9:36 AM Tom Lane wrote: > I don't think it's possible to do it without huge ambiguity > problems, unless you introduce some separator other than dot, as indeed > you suggest here. Heh... the moment I saw you'd replied, I thought, "Uh oh!"... because I think of you as "the reality guy" here. And, sure enough, you came with a bucket of cold water. :-) I haven't explored the matter thoroughly enough to give up all hope in finding a solution which offers a decent ratio. In the end, though, it wouldn't surprise me at all if you were right. Single characters are too dear. Digraphs, maybe. Trigraphs? I know it's getting ugly, but it still might be a net reduction in ugliness for some people, which could be ignored by most. > (The reason why pg_namespace is called > that and not pg_schema is exactly that I > thought it might someday include sub-schemas.) I'd noticed the name; it's encouraging that at least people think it *would be* a good idea. -- Ray Brinzer
Re: Can we go beyond the standard to make Postgres radically better?
On Thu, Feb 10, 2022 at 5:51 PM 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!) > > 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
Re: Can we go beyond the standard to make Postgres radically better?
On Thu, Feb 10, 2022 at 11:56 PM Guyren Howe 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
Re: Can we go beyond the standard to make Postgres radically better?
On Fri, Feb 11, 2022 at 12:26 AM Guyren Howe wrote: > I’m not proposing some crackpot half-baked idea here. There are > well-defined and researched alternatives to SQL. > I didn't suggest that you were. Anything which was written, someone had to actually write. > 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. > And being closed-source, it's not useful here. A concrete spec for what you'd like to see happen at least has potential. A parser that someone has actually written, more so. 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? I've floated a few modest, concrete ideas here, and while the response to them was conservative, I wouldn't call it closed-minded. The message I've gotten from Tom Lane was basically, "here are the problems; show me how this would actually work." I'd have to call that fair; ball's in my court. Being more ambitious, I'd be pleased with a query language which used S-expressions. But I think the road ahead for that would be to say, "Hey, guys, look at this thing I've written. Would you please consider it?" -- Ray Brinzer
Re: Can we go beyond the standard to make Postgres radically better?
On Fri, Feb 11, 2022 at 3:16 AM Ron wrote: > On 2/10/22 10:33 PM, Raymond Brinzer wrote: > > The answer is obvious to every grey beard: SQL was developed from SEQUEL, > Structured *ENGLISH* Query Language at a company that loved English-style > programming languages. > > "SELECT column FROM mytable WHERE condition" is a perfect declarative > English sentence that any middle school grammar teacher would be proud of. > > "FROM mytable SELECT column"... not so much. > They're both perfectly good English; the order just changes the emphasis. That's the particularly annoying bit: we get all the bad things about English grammar, and none of the flexibility or beauty. First thing that came to mind was the beginning of Marcus Aurelius' Meditations: "From my grandfather Verus I learned to relish the beauty of manners, and to restrain all anger." That's a translation of course, but into solid English. Putting what he learned first would not only be dull, it would obscure the fact that he's giving credit. -- Ray Brinzer
