Re: Fastest option to transfer db?

[Tomas Pospisek]

I'm potentiall facing the same problem and would be interested in the 
solution. Is there any particular howto you followed?


Also at some point I'd like to cut of the link between the two DBs 
promote the copy to be the master and delete the original DB. Have you 
figured out the correct step for the cut-over to happen?

*t

On 13.09.21 23:10, Israel Brewster wrote:
Ok, I have logical replication up-and-running (I guess - seemed to 
simple to be working. Shouldn’t it be complicated, requiring many steps 
and configuration changes?), maxing out one CPU core on each machine 
(more or less), and showing network throughput of around 15M. If DU 
changes are to be believed, it’s transferring data at about 
0.8GB/minute, implying something like a 8 hour transfer time.


Of course, since it is replication, it has the benefit that any data 
that comes in during that 8 hour window should also be replicated, after 
which the two systems should remain in sync allowing for zero (or nearly 
so) downtime cutover. Which is nice.


Any gotchas I need to be aware of during this initial transfer window, 
such as WAL files building up on the source machine?


---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

On Sep 13, 2021, at 10:10 AM, Michael Lewis > wrote:


What version of Postgres is the source? Can you make use of logical 
replication?

Re: The tragedy of SQL

[Wim Bertels]

Is it possible that this is mainly an emotional discussion?

Raymond Brinzer schreef op di 14-09-2021 om 02:39 [-0400]:
> 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 <
> > hemilruparel2...@gmail.com>, 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.
> 
> 

Re: The tragedy of SQL

[Rich Shepard]

On Mon, 13 Sep 2021, 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.



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.


Guyren/Hemil,

As a non-SQL expert who's used postgres since 1997 I've come to believe the
basic issue is that SQL is based on sets, neither procedural or object
oriented. Few people think in sets so they try to fit SQL into what they
know rather than understand the how sets work.

Rich

Re: The tragedy of SQL

[Bèrto ëd Sèra]

>
> As a non-SQL expert who's used postgres since 1997 I've come to believe the
> basic issue is that SQL is based on sets, neither procedural or object
> oriented. Few people think in sets so they try to fit SQL into what they
> know rather than understand the how sets work.
>

Yes, that's 100% correct. As per the general discussion, it's not like WE
decide what language/tech will be used, or not. If a sufficient number of
customers begin to offer well paid jobs for , we will see a huge
run to learn , and that's about it. In the end we all work for
the money, and language X may be the eighth wonder of the entire galaxy,
but if it doesn't deliver well paid jobs, nobody will bother learning it.

my 5p.

Berto

Re: The tragedy of SQL

[Merlin Moncure]

On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe  wrote:
> 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.

I've long thought that there is more algebraic type syntax sitting
underneath SQL yearning to get out.  If you wanted to try something
like that today, a language pre-compiler or translator which converted
the code to SQL is likely the only realistic approach if you wanted to
get traction.  History is not very kind to these approaches though and
SQL is evolving and has huge investments behind it...much more than 5
million bucks.

ORMs a function of poor development culture and vendor advocacy, not
the fault of SQL. If developers don't understand or are unwilling to
use joins in language A, they won't in language B either.

merlin

Re: The tragedy of SQL

[David Goodenough]

On Tuesday, 14 September 2021 14:06:13 BST Merlin Moncure wrote:
> On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe  wrote:
> > 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.
> I've long thought that there is more algebraic type syntax sitting
> underneath SQL yearning to get out.  If you wanted to try something
> like that today, a language pre-compiler or translator which converted
> the code to SQL is likely the only realistic approach if you wanted to
> get traction.  History is not very kind to these approaches though and
> SQL is evolving and has huge investments behind it...much more than 5
> million bucks.
> 
> ORMs a function of poor development culture and vendor advocacy, not
> the fault of SQL. If developers don't understand or are unwilling to
> use joins in language A, they won't in language B either.
> 
> merlin
Back in the day, within IBM there were two separate relational databases.  
System-R 
(which came from San Hose) and PRTV (the Peterlee Relational Test vehicle).  As 
I 
understand it SQL came from System-R and the optimizer (amongst other things) 
came 
from PRTV.

PRTV 
(https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)[1]) 
did 
not use SQL, and was never a released product, except with a graphical add-on 
which was 
sold to two UK local authorities for urban planning.

So there are (and always have been) different ways to send requests to a 
relational DB, it is 
just that SQL won the day.



[1] https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)

Re: The tragedy of SQL

[Rob Sargent]

> ORMs a function of poor development culture and vendor advocacy, not

> the fault of SQL. If developers don't understand or are unwilling to

> use joins in language A, they won't in language B either.

>

> merlin

Back in the day, within IBM there were two separate relational 
databases.  System-R (which came from San Hose) and PRTV (the Peterlee 
Relational Test vehicle).  As I understand it SQL came from System-R 
and the optimizer (amongst other things) came from PRTV.



PRTV 
(https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV) 
) 
did not use SQL, and was never a released product, except with a 
graphical add-on which was sold to two UK local authorities for urban 
planning.



So there are (and always have been) different ways to send requests to 
a relational DB, it is just that SQL won the day.




Ah, lets not forget Mr Lane's favourite: quel

Re: The tragedy of SQL

[DAVID ROTH]

There was also QUEL.  The original language for Ingress out of UCB.

> On 09/14/2021 9:51 AM David Goodenough 
>  wrote:
>  
>  
> On Tuesday, 14 September 2021 14:06:13 BST Merlin Moncure wrote:
> > On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe  wrote:
> > > 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.
> > I've long thought that there is more algebraic type syntax sitting
> > underneath SQL yearning to get out.  If you wanted to try something
> > like that today, a language pre-compiler or translator which converted
> > the code to SQL is likely the only realistic approach if you wanted to
> > get traction.  History is not very kind to these approaches though and
> > SQL is evolving and has huge investments behind it...much more than 5
> > million bucks.
> >
> > ORMs a function of poor development culture and vendor advocacy, not
> > the fault of SQL. If developers don't understand or are unwilling to
> > use joins in language A, they won't in language B either.
> >
> > merlin
> Back in the day, within IBM there were two separate relational databases. 
>  System-R (which came from San Hose) and PRTV (the Peterlee Relational Test 
> vehicle).  As I understand it SQL came from System-R and the optimizer 
> (amongst other things) came from PRTV.
> 
> PRTV 
> (https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)) 
> did not use SQL, and was never a released product, except with a graphical 
> add-on which was sold to two UK local authorities for urban planning.
> 
> So there are (and always have been) different ways to send requests to a 
> relational DB, it is just that SQL won the day.
> 

Re: The tragedy of SQL

[Merlin Moncure]

On Tue, Sep 14, 2021 at 9:01 AM Rob Sargent  wrote:
> > ORMs a function of poor development culture and vendor advocacy, not
> > the fault of SQL. If developers don't understand or are unwilling to
> > use joins in language A, they won't in language B either.
>
> Back in the day, within IBM there were two separate relational databases.  
> System-R (which came from San Hose) and PRTV (the Peterlee Relational Test 
> vehicle).  As I understand it SQL came from System-R and the optimizer 
> (amongst other things) came from PRTV.
>
> PRTV 
> (https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)) 
> did not use SQL, and was never a released product, except with a graphical 
> add-on which was sold to two UK local authorities for urban planning.
>
> So there are (and always have been) different ways to send requests to a 
> relational DB, it is just that SQL won the day.
>
> Ah, lets not forget Mr Lane's favourite: quel

Sure, I quite like, er, liked quel also, being more mathematical and
formal.  It's a shame it didn't make the cut. This is however a
telling example that standardization trumps purity once languages hit
a certain spot.  There are many languages with dumb things that will
never get fixed :-).  As they say, 'the devil you know'.

QUEL also uses idiomatic english for most operations, which I guess is
probably a contributing factor for developer resistance to SQL, since
native speakers are a minority of the earth's population. Oh well.


merlin

Re: The tragedy of SQL

[Michael Nolan]

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

Re: The tragedy of SQL

[Rob Sargent]

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.

Re: The tragedy of SQL

[Bret Stern]

I didn't start in 1967, but 1984, I'm in agreement with the bad 
programmers premise. Since the beginning there have always been


lots of languages. It is my opinion, the more languages and concepts you 
know the better your success on the project.


Heck I didn't use triggers till late 90's, funny thing I have a PICK 
project right now.. too much fun




On 9/14/2021 9: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 

Re: Fastest option to transfer db?

[Israel Brewster]

On Sep 14, 2021, at 1:42 AM, Tomas Pospisek  wrote:
> 
> I'm potentiall facing the same problem and would be interested in the 
> solution. Is there any particular howto you followed?

I used https://hevodata.com/learn/postgresql-logical-replication/ 
, but I rather 
suspect there are better options, as I had a number of issues with that one. 
Essentially, it seems to boil down to the following steps:

1) Set up a new “slave” database. I used a pg_dump -s command piped through to 
psql to transfer schema only to the new DB
2) Configure both master and slave postgresql servers with wal_level=logical in 
the postgresql.conf file. Will require a server restart, which stinks if you 
need absolutely 0 downtime, but ideally should only take a few seconds.
3) On the master server, run the SQL command 

CREATE PUBLICATION publication_name FOR ALL TABLES; 

Presumably this should be issued while in the database you wish to replicate, 
and you can specify specific tables only if desired (though I haven’t looked 
into how). There is a note in that how-to about the published table needing a 
REPLICA IDENTITY to replicate DELETE and UPDATE operations, I still need to 
look into that more. For my case, however, I have an insert-only workflow, so 
it wasn’t an issue.

4) Finally, on the slave server, run the command 

CREATE SUBSCRIPTION subscription_name CONNECTION '... ...' 
PUBLICATION my_publication;

where connection string is something like:

‘host=my.remote.host port=5432 dbname=my_big_db'

...And that’s it, replication should kick off by syncing up the data from 
master to slave, and after that changes should be sent in real time.

One gotcha that I ran into: my table used the postgis extension, which was set 
up on the slave DB when I ran the pg_dump commands. Creating that extension 
creates *and populates* a table named “spatial_ref_sys”. Since I had created 
the publication as “FOR ALL TABLES”, the replication tried to include this 
table. Which failed due to UNIQUE constraint violations. At which point 
replication stopped AS DID ALL INSERTS INTO THE MASTER DB!

I deleted all records from said table on the slave DB, replication kicked in 
and re-populated it, and life went on, but I lost something like 14 hours of 
data thanks to this mistake. Guess that’s what I get for not keeping an eye on 
the process :-( Lesson learned: make sure any tables to be replicated are empty 
in the slave before issuing the CREATE SUBSCRIPTION command.

For cutover, I plan the following, we’ll see how it goes:

1) move all read-only processes over to the new server. That can be done at any 
point after the above, with little or no downtime depending on your processes.
2) Reconfigure writing process to point to the slave, and stop them
3) Issue a DROP SUBSCRIPTION subscription_name on the slave, thereby 
“promoting” it to master
4) Restart writing processes, now pointed to the new master

Steps 2-4 should happen in quick succession, resulting in only seconds of 
downtime. At least, that’s the theory. We’ll see how it goes (or if anyone else 
here can offer a better procedure!)

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145


> 
> Also at some point I'd like to cut of the link between the two DBs promote 
> the copy to be the master and delete the original DB. Have you figured out 
> the correct step for the cut-over to happen?
> *t
> 
> On 13.09.21 23:10, Israel Brewster wrote:
>> Ok, I have logical replication up-and-running (I guess - seemed to simple to 
>> be working. Shouldn’t it be complicated, requiring many steps and 
>> configuration changes?), maxing out one CPU core on each machine (more or 
>> less), and showing network throughput of around 15M. If DU changes are to be 
>> believed, it’s transferring data at about 0.8GB/minute, implying something 
>> like a 8 hour transfer time.
>> Of course, since it is replication, it has the benefit that any data that 
>> comes in during that 8 hour window should also be replicated, after which 
>> the two systems should remain in sync allowing for zero (or nearly so) 
>> downtime cutover. Which is nice.
>> Any gotchas I need to be aware of during this initial transfer window, such 
>> as WAL files building up on the source machine?
>> ---
>> Israel Brewster
>> Software Engineer
>> Alaska Volcano Observatory
>> Geophysical Institute - UAF
>> 2156 Koyukuk Drive
>> Fairbanks AK 99775-7320
>> Work: 907-474-5172
>> cell:  907-328-9145
>>> On Sep 13, 2021, at 10:10 AM, Michael Lewis >>  >> >> wrote:
>>> 
>>> What version of Postgres is the source? Can you make use of logical 
>>> replication?

Re: The tragedy of SQL

[Raymond Brinzer]

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

[Brian Dunavant]

On Tue, Sep 14, 2021 at 1:54 PM Raymond Brinzer 
wrote:

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

Re: The tragedy of SQL

[Raymond Brinzer]

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

[Mladen Gogala]

Replies in-line

On 9/14/21 01:51, 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.


Based on what criteria?




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.


Thee is nothing natural about either relations or arrays and 
hashes/dictionaries. Relations are pretty literal implementation of the 
basic set theory. Having a decent understanding of the basic set theory 
is a condition  for understanding SQL. Now, we can discuss whether a 
language implementing a mathematical theory is "good" or "bad", whatever 
the meaning of "good" or "bad" in the given context. Historically, SQL 
is a good fit for the banking business and accounting and that is why it 
is still around.


Another misconception about SQL is treating it as a general purpose 
programming language. SQL is data description language, nothing more, 
nothing less. It doesn't need loops, arrays, hashes or subroutines, its 
principal purpose is to describe a subset of data. Without SQL you would 
have to read all the data and filter the unnecessary stuff yourself. 
Furthermore, part of SQL are so called "ACID requirements". Transaction 
can only see the data that was committed before the transaction has 
begun. Implementing ACID takes a lot of code, that's what MVCC is all 
about. However, that too has its origins in accounting. You cannot pay 
the money you don't have. And the last thing about SQL is transaction 
management. Without relational databases and SQL, you would need a 
proprietary transaction manager, just like MongoDB. And MongoDB has a 
complex proprietary transaction manager and is losing market share. So, 
to recapitulate:


 * Declarative subset definition
 * ACID consistency
 * Transaction management
 * Ideal fit for accounting.

That is why SQL is still around. And that is why we all live in a yellow 
subroutine (this reference is not for the millennials or younger).


--
I'll speak the key, the whole key and nothing but the key, so help me Codd.
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: The tragedy of SQL

[Mladen Gogala]

On 9/14/21 02:18, Rob Sargent wrote:

All languages are fucking terrible.


I like English. It's not very complex and it allows me to express myself 
very well. You should see my native tongue, Croatian language, from the 
group of Slavic languages. It's fucking terrible.



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

Re: The tragedy of SQL

[Guyren Howe]

On Sep 14, 2021, 12:51 -0700, Mladen Gogala , wrote:
> Replies in-line
> On 9/14/21 01:51, 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.
> Based on what criteria?
Verbosity. Redundancy. Lack of orthogonality. Resemblance to English. The 
standard effectively mandates a particular storage strategy, with heavyweight 
tables that must provide certain CAP theorem guarantees instead of others. 
Rigid storage limitations: less in Postgres than in others, but why can’t I, 
say, nest schemas? Hell, why can’t I nest relations?

https://www.edgedb.com/blog/we-can-do-better-than-sql
> >
> > 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.
> > Thee is nothing natural about either relations or arrays and 
> > hashes/dictionaries. Relations are pretty literal implementation of the 
> > basic set theory. Having a decent understanding of the basic set theory is 
> > a condition for understanding SQL. Now, we can discuss whether a language 
> > implementing a mathematical theory is "good" or "bad", whatever the meaning 
> > of "good" or "bad" in the given context. Historically, SQL is a good fit 
> > for the banking business and accounting and that is why it is still around.
> > Another misconception about SQL is treating it as a general purpose 
> > programming language. SQL is data description language, nothing more, 
> > nothing less. It doesn't need loops, arrays, hashes or subroutines, its 
> > principal purpose is to describe a subset of data. Without SQL you would 
> > have to read all the data and filter the unnecessary stuff yourself. 
> > Furthermore, part of SQL are so called "ACID requirements". Transaction can 
> > only see the data that was committed before the transaction has begun. 
> > Implementing ACID takes a lot of code, that's what MVCC is all about. 
> > However, that too has its origins in accounting. You cannot pay the money 
> > you don't have. And the last thing about SQL is transaction management. 
> > Without relational databases and SQL, you would need a proprietary 
> > transaction manager, just like MongoDB. And MongoDB has a complex 
> > proprietary transaction manager and is losing market share. So, to 
> > recapitulate:
> >  • Declarative subset definition
> >  • ACID consistency
> >  • Transaction management
> >  • Ideal fit for accounting.
> > That is why SQL is still around. And that is why we all live in a yellow 
> > subroutine (this reference is not for the millennials or younger)
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.

Re: The tragedy of SQL

[Rob Sargent]

On 9/14/21 1:53 PM, Mladen Gogala wrote:



On 9/14/21 02:18, Rob Sargent wrote:

All languages are fucking terrible.


I like English. It's not very complex and it allows me to express 
myself very well. You should see my native tongue, Croatian language, 
from the group of Slavic languages. It's fucking terrible.



I actually burst out laughing at this.  I don't believe I've ever heard 
any prefer English over there mother tougue.  That shocked me.  I was, 
of course referring to programming languages.  I speak but one 
(incredibly complicated) human language.

Re: The tragedy of SQL

[Martin Ritchie]

The big advantage for SQL is that it has remained relatively constant and
universal for ~40 years. There is effectively one major relational database
language that you need to learn and that is that. Once you learn it you can
transport your knowledge to nearly every other relational database
environment. The big disadvantage is that it was developed 40 years ago and
there have been huge changes in how we use databases and how we design
language syntax. If we could redesign the standard with a clean sheet now
it would be much better. But things could be a lot worse, at least it is
not javascript.. gross!

Martin Ritchie
*Geotab*
Senior DBA
Direct +1 (519) 741-7660
Toll-free +1 (877) 436-8221
Visit www.geotab.com
Twitter  | Facebook
 | YouTube
 | LinkedIn



On Tue, Sep 14, 2021 at 3:53 PM Mladen Gogala 
wrote:

>
> On 9/14/21 02:18, Rob Sargent wrote:
>
> All languages are fucking terrible.
>
> I like English. It's not very complex and it allows me to express myself
> very well. You should see my native tongue, Croatian language, from the
> group of Slavic languages. It's fucking terrible.
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

Re: The tragedy of SQL

[Raymond Brinzer]

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

[Guyren Howe]

Exactly. SQL is the roman numerals of relational databases.
On Sep 14, 2021, 13:08 -0700, Raymond Brinzer , wrote:
> 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

Roles

[Gambhir Singh]

Hi,

I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to
create roles which are specific to each database, because when I'm creating
roles in one Database, they are visible in other Databases.

Thanks & Regards
Gambhir Singh

Re: The tragedy of SQL

[FWS Neil]

> On Sep 14, 2021, at 11: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.  

Yep, me too.  I would say that SQL has not achieved its design goals yet.  The 
original concept was to write what you want to achieve and have the server 
figure out the best way to get at it.  

What people hate about SQL is that the programmer has to optimize SQL to get 
acceptable performance.  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.

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.  

Of course, that might not happen until my grandchildren are retired and in a 
nursing home.  But who knows, stranger things have happened.

Neil
www.fairwindsoft.com

Re: The tragedy of SQL

[Raymond Brinzer]

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

[Mladen Gogala]

On 9/14/21 16:07, Raymond Brinzer wrote:

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.


Analogy is not an accepted logical method. I do agree that the style of 
expression matters. That is why we have literature. Saying "hey there" 
and "friends, Romans, countrymen, lend me your ears" can have the same 
meaning in the given context but the latter expression is much more 
poetic. As software engineers, we are very much opposed to poetry, 
especially those among us using Perl.


However, the stated purpose of the SQL language is to describe sets and 
subsets. It's supposed to be verbose. Storage extensions are database 
specific and are here for performance reasons.



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

Re: The tragedy of SQL

[Mladen Gogala]

On 9/14/21 15:57, Guyren Howe wrote:

Verbosity. Redundancy. Lack of orthogonality. Resemblance to English.


Verbosity is a feature, as well as the resemblance to English. The 
language is meant to be understood by accountants. Once upon a time 
people were using something called "COmmon Business Oriented Language" 
which was also very verbose, for the same reason: it had to be 
understandable to the business people. SQL is written by the people with 
the background in mathematics and is thus more understandable for the 
people with background in mathematics. I have no problem with SQL.



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

Re: The tragedy of SQL

[Peter J. Holzer]

On 2021-09-14 17:01:40 -0400, Mladen Gogala wrote:
> On 9/14/21 15:57, Guyren Howe wrote:
> > Verbosity. Redundancy. Lack of orthogonality. Resemblance to English.
> 
> Verbosity is a feature, as well as the resemblance to English. The language
> is meant to be understood by accountants. Once upon a time people were using
> something called "COmmon Business Oriented Language" which was also very
> verbose, for the same reason: it had to be understandable to the business
> people.

Let's rephrase that: Back in the 1960s people thought programming would
be easier (programs easier to understand and to write) if the syntax
looked similar to English prose.

That belief was mistaken. While a very simple COBOL program may be
readable for a layperson (while even a very simple C program is not, and
a Haskell or APL program looks like complete gibberish), they still
can't write it, and even the readability advantage quickly vanishes for
more complex programs, because while COBOL may look like English, it
isn't.

SQL is significantly younger than COBOL but its design was led by the
same belief that making the language look like ordinary English would
make it easy to learn.

(It also wasn't the last. A few years back I saw a language for
specifying test cases designed for "ordinary people". Again, it looked
like English, but wasn't).

Superficial syntax is in my experience the smallest hurdle. It's
semantics that people struggle with.

hp

PS: COBOL is still in use.

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


signature.asc
Description: PGP signature

Re: The tragedy of SQL

[Peter J. Holzer]

On 2021-09-14 16:51:39 -0400, Mladen Gogala wrote:
> As software engineers, we are very much opposed to poetry, especially
> those among us using Perl.

When I learned Perl, Perl poetry was a real thing!

hp

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


signature.asc
Description: PGP signature

Re: The tragedy of SQL

[Gavin Flower]

On 15/09/21 04:10, 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


I remember programming in FORTRAN IV on an IBM 1130 at Auckland 
University.  My first attempt to explore Pythagorean triples was written 
in FORTRAN on that machine.  Finally had a useful program written in 
Java about 30 years later.  There are 4 triples starting with 60 that 
satisfy A*2 + B^2 + C^2 where A < B < C and the numbers are mutually 
prime. I was able to handle values of A up to the size of long, so I got 
some pretty big numbers for B & C.  Java's BigInteger class has its uses!


On the IBM 1130 it was faster to use X * X to find the square of a value 
than to use the power notation (of which I've forgotten the syntax).


And for my many sins, I spent years programming in COBOL.

I've written code in over 30 languages.  Probably had most fun writing a 
couple of trivial programs in ARM2/3 assembler -- all instructions 
except one are conditional.


There is no one perfect language, despite what some people might insist!


Cheers,
Gavin

Re: The tragedy of SQL

[Scottix]

It is kind of a purists fallacy. Point being if you could just write ASM
code it would be the best.

When in reality, a database is used not because it is the best technical
database, but is used by many people. Something that other developers can
pickup and use without reading a 200 page manual and study for a year on
end. Although maybe stuff would be better if everyone did that, on the
other hand might just be wasted effort.

You complain about no-SQL database but actually then advocate for it, by
saying SQL is sad. I find Postgres as a traditional RDB and has specific
use cases. If you compare that to Clickhouse which has a very different use
case. Don't compare timeseriesdb, because even that has limitations that
clickhouse surpasses at scale. Just an example of a no-SQL database.

If you do start a new database, let me know. I would like to see that in
action.

On Tue, Sep 14, 2021 at 9:20 AM 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.
>


-- 
scot...@gmail.com

Re: The tragedy of SQL

[Gavin Flower]

On 15/09/21 10:30, Peter J. Holzer wrote:

On 2021-09-14 16:51:39 -0400, Mladen Gogala wrote:

As software engineers, we are very much opposed to poetry, especially
those among us using Perl.

When I learned Perl, Perl poetry was a real thing!

 hp


Perl is too verbose, use APL!  See: https://tryapl.org/

To be honest, I've looked at APL but never programmed in it.


Cheers,
Gavin

Re: Roles

[Adrian Klaver]

On 9/14/21 1:13 PM, Gambhir Singh wrote:

Hi,

I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to 
create roles which are specific to each database, because when I'm 
creating roles in one Database, they are visible in other Databases.


You can't, roles are global objects in a cluster.



Thanks & Regards
Gambhir Singh




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: The tragedy of SQL

[Michael Nolan]

Of all the languages I wrote in, I think SNOBOL was the most fun to write
in, and LISP the least fun.  Control Data assembler language
programming was probably the most precise, because you could crash the OS
with a single mis-placed character, something I did more than once.

In a graduate-level course, we studied ALGOL-68, which had so many things
in it that I'm not sure anybody ever actually implemented the full
language.  (But then again, has anybody implemented EVERYTHING in the SQL
standard?)

COBOL has strange verbs like 'move corresponding' that could accomplish
complicated tasks in a few lines but you have to be careful that you knew
what you were asking for!

And I'd take the SQL standard over the CODASYL standard any time!
--
Mike Nolan

Re: The tragedy of SQL

[Raymond Brinzer]

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: The tragedy of SQL

[Adrian Klaver]

On 9/14/21 12:51 PM, Mladen Gogala wrote:

Replies in-line

On 9/14/21 01:51, 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.


Based on what criteria?




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.


Thee is nothing natural about either relations or arrays and 
hashes/dictionaries. Relations are pretty literal implementation of the 
basic set theory. Having a decent understanding of the basic set theory 
is a condition  for understanding SQL. Now, we can discuss whether a 
language implementing a mathematical theory is "good" or "bad", whatever 
the meaning of "good" or "bad" in the given context. Historically, SQL 
is a good fit for the banking business and accounting and that is why it 
is still around.




I can see what you are saying. Still as someone that comes from a 
biology background and a basic understanding of math I will say SQL has 
a broader appeal. All those years of memorizing organism classifications 
inadvertently led me to set theory; 'all dogs are animals, not all 
animals are dogs'. Also, time spent identifying plants/critters via 
dichotomous keys led me to boolean logic. The upshot is that once I got 
involved with SQL databases the basics made sense. The details I am 
still learning.




--
I'll speak the key, the whole key and nothing but the key, so help me Codd.
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: To JIT (or not!) in Ubuntu packages

[Thomas Munro]

On Tue, Sep 14, 2021 at 10:11 AM Ben Chobot  wrote:
> We've noticed that the Ubuntu postgresql-12 package has --with-llvm
> enabled on x86_64, but not on aarch64. Does anybody know if this was
> intentional, or just an oversight?
>
> For what it's worth, it seems the beta postgresql-14 package for Ubuntu
> still doesn't have --with-llvm.
>
> I'm not sure if this explains why our aarch64 DBs are missing a
> jit_provider GUC variable, but I expect it does explain why
> pg_jit_available() tells me no.

Hmm.  No Ubuntu here and I don't know the answer (CC Christoph).  Can
you show exactly where the package is coming from, what pg_config
outputs.  For what it's worth, it does look like it's enabled in the
Ubuntu aarch64 packages from apt.postgresql.org, according to a quick
look using caveman techniques:

$ curl -sO 
http://apt.postgresql.org/pub/repos/apt/pool/14/p/postgresql-14/postgresql-14_14~beta3-1.pgdg+1_arm64.deb
$ ar x postgresql-14_14~beta3-1.pgdg+1_arm64.deb
$ tar xf data.tar.xz
$ strings usr/lib/postgresql/14/bin/postgres | grep 'with-llvm'
 '--build=aarch64-linux-gnu' '--prefix=/usr'
'--includedir=${prefix}/include' '--mandir=${prefix}/share/man'
'--infodir=${prefix}/share/info' '--sysconfdir=/etc'
'--localstatedir=/var' '--disable-option-checking'
'--disable-silent-rules' '--libdir=${prefix}/lib/aarch64-linux-gnu'
'--runstatedir=/run' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-icu' '--with-tcl'
'--with-perl' '--with-python' '--with-pam' '--with-openssl'
'--with-libxml' '--with-libxslt' 'PYTHON=/usr/bin/python3'
'--mandir=/usr/share/postgresql/14/man'
'--docdir=/usr/share/doc/postgresql-doc-14'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/14'
'--bindir=/usr/lib/postgresql/14/bin'
'--libdir=/usr/lib/aarch64-linux-gnu/'
'--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian
14~beta3-1.pgdg+1)' '--enable-nls' '--enable-thread-safety'
'--enable-tap-tests' '--enable-debug' '--enable-dtrace'
'--enable-cassert' '--disable-rpath' '--with-uuid=e2fs'
'--with-gnu-ld' '--with-lz4' '--with-pgport=5432'
'--with-system-tzdata=/usr/share/zoneinfo' '--with-llvm'
'LLVM_CONFIG=/usr/bin/llvm-config-11' 'CLANG=/usr/bin/clang-11'
'--with-systemd' '--with-selinux' 'MKDIR_P=/bin/mkdir -p'
'PROVE=/usr/bin/prove' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet'
'CFLAGS=-g -O2 -fstack-protector-strong -Wformat
-Werror=format-security' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now'
'--with-gssapi' '--with-ldap' 'build_alias=aarch64-linux-gnu'
'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2
-fstack-protector-strong -Wformat -Werror=format-security'
$ find . -name '*jit*.so'
./usr/lib/postgresql/14/lib/llvmjit.so

It's certainly expected to work on this arch, and we have snakefly and
eelpout testing it in the build farm.

Re: The tragedy of SQL

[Gavin Flower]

Hi Michael,

Appropriate comments interspersed below.

I'm happy writing SQL and moderately competent using it.  But like all 
the languages I've used, without exception, it has its pain points.



Cheers,
Gavin

On 15/09/21 11:25, Michael Nolan wrote:
Of all the languages I wrote in, I think SNOBOL was the most fun to 
write in, and LISP the least fun.  Control Data


I once read the first 40 pages of a SNOBOL manuel, but unfortunately 
never got the opportunity to try it out.




assembler language
programming was probably the most precise, because you could crash the 
OS with a single mis-placed character, something I did more than once.


I knew a senior tech programmer who inadvertently tried to rewind a disc 
back to BT and switch to 800 BPI, fortunately only his program crashed.


Another time the memory protection was disabled (ICL no longer had the 
capacity to fix it) on our ICL 4/50 MainFrame and someone wrote a 
program to write 'J' into an area of memory.  That was legitimate, but 
it had a bug which caused it to write into more memory than it should 
have...  The machine crashed.  Our ICL 4/50 was the last surviving 
operational machine of its type in the world.


Our main machines were two ICL 4/72's each having a single fast 2MHz 
processor and a massive 1 MB of core memory with a battery of big 
exchangeable disks each with a whopping 60 MB of capacity & tape drives 
for 12" reels.





In a graduate-level course, we studied ALGOL-68, which had so many 
things in it that I'm not sure anybody ever actually implemented the 
full language.  (But then again, has anybody implemented EVERYTHING in 
the SQL standard?)


I learnt ALGOL-68 from a manual written in Backus-Naur notation on my 
own initiative.  Tried to write a simple statistics program, never 
finished it.  That was before I really understood the value of rigorous 
indenting standards.





COBOL has strange verbs like 'move corresponding' that could 
accomplish complicated tasks in a few lines but you have to be careful 
that you knew what you were asking for!


In our site that was banned as being too dangerous.

And how about the 'lovely' ALTER GOTO construct???

Children don't try to use these constructs at home, as even experienced 
adults get burnt using them!





And I'd take the SQL standard over the CODASYL standard any time!
--


Agreed!



Mike Nolan

Re: Roles

[Gambhir Singh]

Thank you for the clarification.

On Tue, 14 Sep 2021, 18:11 Adrian Klaver,  wrote:

> On 9/14/21 1:13 PM, Gambhir Singh wrote:
> > Hi,
> >
> > I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to
> > create roles which are specific to each database, because when I'm
> > creating roles in one Database, they are visible in other Databases.
>
> You can't, roles are global objects in a cluster.
>
> >
> > Thanks & Regards
> > Gambhir Singh
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: To JIT (or not!) in Ubuntu packages

[Ben Chobot]

Thomas Munro wrote on 9/14/21 5:50 PM:

On Tue, Sep 14, 2021 at 10:11 AM Ben Chobot  wrote:

We've noticed that the Ubuntu postgresql-12 package has --with-llvm
enabled on x86_64, but not on aarch64. Does anybody know if this was
intentional, or just an oversight?

For what it's worth, it seems the beta postgresql-14 package for Ubuntu
still doesn't have --with-llvm.

I'm not sure if this explains why our aarch64 DBs are missing a
jit_provider GUC variable, but I expect it does explain why
pg_jit_available() tells me no.

Hmm.  No Ubuntu here and I don't know the answer (CC Christoph).  Can
you show exactly where the package is coming from, what pg_config
outputs.  For what it's worth, it does look like it's enabled in the
Ubuntu aarch64 packages from apt.postgresql.org, according to a quick
look using caveman techniques:

$ curl -sO 
http://apt.postgresql.org/pub/repos/apt/pool/14/p/postgresql-14/postgresql-14_14~beta3-1.pgdg+1_arm64.deb
$ ar x postgresql-14_14~beta3-1.pgdg+1_arm64.deb
$ tar xf data.tar.xz
$ strings usr/lib/postgresql/14/bin/postgres | grep 'with-llvm'
  '--build=aarch64-linux-gnu' '--prefix=/usr'
'--includedir=${prefix}/include' '--mandir=${prefix}/share/man'
'--infodir=${prefix}/share/info' '--sysconfdir=/etc'
'--localstatedir=/var' '--disable-option-checking'
'--disable-silent-rules' '--libdir=${prefix}/lib/aarch64-linux-gnu'
'--runstatedir=/run' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-icu' '--with-tcl'
'--with-perl' '--with-python' '--with-pam' '--with-openssl'
'--with-libxml' '--with-libxslt' 'PYTHON=/usr/bin/python3'
'--mandir=/usr/share/postgresql/14/man'
'--docdir=/usr/share/doc/postgresql-doc-14'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/14'
'--bindir=/usr/lib/postgresql/14/bin'
'--libdir=/usr/lib/aarch64-linux-gnu/'
'--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian
14~beta3-1.pgdg+1)' '--enable-nls' '--enable-thread-safety'
'--enable-tap-tests' '--enable-debug' '--enable-dtrace'
'--enable-cassert' '--disable-rpath' '--with-uuid=e2fs'
'--with-gnu-ld' '--with-lz4' '--with-pgport=5432'
'--with-system-tzdata=/usr/share/zoneinfo' '--with-llvm'
'LLVM_CONFIG=/usr/bin/llvm-config-11' 'CLANG=/usr/bin/clang-11'
'--with-systemd' '--with-selinux' 'MKDIR_P=/bin/mkdir -p'
'PROVE=/usr/bin/prove' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet'
'CFLAGS=-g -O2 -fstack-protector-strong -Wformat
-Werror=format-security' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now'
'--with-gssapi' '--with-ldap' 'build_alias=aarch64-linux-gnu'
'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2
-fstack-protector-strong -Wformat -Werror=format-security'
$ find . -name '*jit*.so'
./usr/lib/postgresql/14/lib/llvmjit.so

Ohh, interesting, I don't have that .so file.

So I've installed 
http://apt.postgresql.org/pub/repos/apt/pool/14/p/postgresql-14/postgresql-14_14~beta3-1.pgdg18.04+1_arm64.deb, 
after which I see:


$ /usr/lib/postgresql/14/bin/pg_config
BINDIR = /usr/lib/postgresql/14/bin
DOCDIR = /usr/share/doc/postgresql-doc-14
HTMLDIR = /usr/share/doc/postgresql-doc-14
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/14/server
LIBDIR = /usr/lib/aarch64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/14/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/14/man
SHAREDIR = /usr/share/postgresql/14
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/14/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--build=aarch64-linux-gnu' '--prefix=/usr' 
'--includedir=${prefix}/include' '--mandir=${prefix}/share/man' 
'--infodir=${prefix}/share/info' '--sysconfdir=/etc' 
'--localstatedir=/var' '--disable-silent-rules' 
'--libdir=${prefix}/lib/aarch64-linux-gnu' 
'--libexecdir=${prefix}/lib/aarch64-linux-gnu' '--runstatedir=/run' 
'--disable-maintainer-mode' '--disable-dependency-tracking' '--with-icu' 
'--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' 
'--with-libxml' '--with-libxslt' 'PYTHON=/usr/bin/python3' 
'--mandir=/usr/share/postgresql/14/man' 
'--docdir=/usr/share/doc/postgresql-doc-14' 
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' 
'--datadir=/usr/share/postgresql/14' 
'--bindir=/usr/lib/postgresql/14/bin' 
'--libdir=/usr/lib/aarch64-linux-gnu/' 
'--libexecdir=/usr/lib/postgresql/' 
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu 
14~beta2-1.pgdg18.04+1)' '--enable-nls' '--enable-thread-safety' 
'--enable-tap-tests' '--enable-debug' '--enable-dtrace' 
'--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-lz4' 
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 
'--with-systemd' '--with-selinux' 'MKDIR_P=/bin/mkdir -p' 
'PROVE=/usr/bin/prove' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 
'CFLAGS=-g -O2 -fstack-protector-strong -Wformat 
-Werror=format-security' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro 
-Wl,-z,now'

Re: To JIT (or not!) in Ubuntu packages

[Thomas Munro]

On Wed, Sep 15, 2021 at 3:30 PM Ben Chobot  wrote:
> So I've installed 
> http://apt.postgresql.org/pub/repos/apt/pool/14/p/postgresql-14/postgresql-14_14~beta3-1.pgdg18.04+1_arm64.deb,
>  after which I see:

Ahhh, so you're on 18.04, an old LTS.  I remember now, there was this
issue in LLVM 3.9 on that aarch64 (which I saw on Debian 9 during
testing of the JIT stuff), resolved by later LLVM versions (maybe 6?):

https://www.postgresql.org/message-id/flat/CAEepm%3D0HqkxWk2w8N2nXQXC_43Mucn-v%3D8QdY8vOG5ojo9kJRA%40mail.gmail.com#5d656391e0b1d49d3e577b7a41e69b7c

I don't know Ubuntu versioning policies etc, but maybe something to do
with which LLVM versions it's allowed to depend on?

SQL queries as sets: was The tragedy of SQL

[Steve Litt]

Rich Shepard said on Tue, 14 Sep 2021 05:49:07 -0700 (PDT)

>On Mon, 13 Sep 2021, 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.  
>
>>> 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.  
>
>Guyren/Hemil,
>
>As a non-SQL expert who's used postgres since 1997 I've come to
>believe the basic issue is that SQL is based on sets, neither
>procedural or object oriented. Few people think in sets so they try to
>fit SQL into what they know rather than understand the how sets work.

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.

SteveT

Steve Litt 
Spring 2021 featured book: Troubleshooting Techniques of the Successful
Technologist http://www.troubleshooters.com/techniques

Basic Questions about PostgreSQL.

[A Z]

-In PostgreSQL, what is the difference between Roles, Groups, and Policies 
exactly? What are they each, how do they relate to one another, and which 
one(s) can be assigned to another?

-Is there a simple, immediate, straightforward way to temporarily disable 
PostgreSQL database
use of all other users, except development users, or in fact just one user (one 
developer, yourself)
so that maintenance on the data or the data model may be performed?

The way things are, after you revoke connection priviledges for all the other 
general users,
cancel all the other connections (except for yourself, or developer user 
access),
and then perform required maintenance on the database. Thereafter, connection 
priviledges
have to be restored to all the other users, so that they can choose to connect 
again.

All of this strikes me as being very elaborate. Is there a much more simple
way to disable all and subsequent connections to a database, temporarily, and
then allowing them all again, within the PostgreSQL DBMS?

Is there a public Extension, for example, that installs and works well, which 
can be used to do this?
Is there a simple, one hit way to turn a set of user connections all off, and 
then all on
(regarding their permissions and connections, I presume), again?

Re: Basic Questions about PostgreSQL.

[David G. Johnston]

On Wednesday, September 15, 2021, A Z  wrote:

> -In PostgreSQL, what is the difference between Roles, Groups
>
>
There is none, aside from convention.  Roles are generic, groups don’t get
login rights while users do.

You might want to clarify what you mean by policy since referencing the
“create policy” command seems out of place here.



> -Is there a simple, immediate, straightforward way to temporarily disable
> PostgreSQL database
> use of all other users, except development users, or in fact just one user
> (one developer, yourself)
> so that maintenance on the data or the data model may be performed?
>
>
Manipulating the pg_hba.comf file is usually the most efficient method.

Though since connect privileges are usually left as strictly inherited from
public by default revoking connect from public will most likely prevent
non-superusers from connecting to databases.

Though it seems unusual that maintenance would require such extreme
measures to be taken.

David J.

Re: SQL queries as sets: was The tragedy of SQL

[Guyren Howe]

Oh, yeah, wow. Big topic.

My original post in the series is in significant part about how SQL hides this 
sort of thing from you.

A table is a set:  a set of true facts, all having the same structure, so you 
can operate on all of them with any operation on the individual rows.

Multiple tables, multiples facts. Sometimes about the same things, which is 
what a join does: the join key is the identifier of the things a bunch of 
statements are all about. A user has a name, and a login, and a creation date. 
When the identifier is the primary key, there is only such statement. When it’s 
a foreign key, there are multiple statements.

As an aside: it would be perfectly reasonable to have multiple tables with the 
same primary key. Or with the same foreign key. You could have only “foreign 
keys” on a bunch of different tables.

When you do a join, you’re finding all the “x and y” statements that the system 
knows to be true. That’s why a 1:many joins produces duplicate 1-side values in 
its results.

When you do a where, you’re reducing the number of rows by only finding the 
rows with a certain property. The joins and wheres together give you a very 
expressive logic engine.

There are two equivalent ways of looking at what the database does: it stores 
logical statements and acts as a logic engine; or it stores sets of tuples and 
gives you set operations on them.

Go read up on the basics of Datalog: it makes all of this beautifully obvious.

My original post was about how different the computer industry would be if only 
we’d made relations as easy as Datalog does. The entire industry would look 
different, just as garbage collection made the entire industry different.
On Sep 14, 2021, 21:55 -0700, Steve Litt , wrote:
> Rich Shepard said on Tue, 14 Sep 2021 05:49:07 -0700 (PDT)
>
> > On Mon, 13 Sep 2021, 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.
> >
> > > > 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.
> >
> > Guyren/Hemil,
> >
> > As a non-SQL expert who's used postgres since 1997 I've come to
> > believe the basic issue is that SQL is based on sets, neither
> > procedural or object oriented. Few people think in sets so they try to
> > fit SQL into what they know rather than understand the how sets work.
>
> 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.
>
> SteveT
>
> Steve Litt
> Spring 2021 featured book: Troubleshooting Techniques of the Successful
> Technologist http://www.troubleshooters.com/techniques
>
>

Re: The tragedy of SQL

[Julien Rouhaud]

On Wed, Sep 15, 2021 at 8:31 AM Raymond Brinzer  wrote:
>
> So, on a practical note:  I'd like it if PostgreSQL supported
> alternate languages for queries, as it does for stored procedures.

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.

That's arguably not enough to efficiently handle things like oracle
style sql-89 outer joins as there's still no way to hook into the
various transform* functions, but that's a start.