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

[FWS Neil]

> On Sep 15, 2021, at 2:44 PM, Merlin Moncure  wrote:
> 
> On Tue, Sep 14, 2021 at 3:16 PM FWS Neil  <mailto:n...@fairwindsoft.com>> wrote:
>> 
>>> 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.
> 
> I think you ought to recognize that many people on this list make
> money directly from managing that complexity :-).
> 

I did not intend to disparage anyone.  People, including myself, make money 
when they provide value and there is certainly value here.

But, I am not sure I understand your inference.  Are you saying (and I am not 
implying you are) that PostgreSQL does not progress in line with the original 
SQL goals of simplifying data access because people are making money off of the 
current complexity?

My only point was that SQL was originally conceived to free the programmer from 
implementation details.  Today, after 47 years, it still has not achieved that 
goal.  As computers get more powerful, of course, they will process more data, 
but they also have the option of moving more of the implementation decision 
making away from the programmer to let the machine figure out the best way to 
handle the request.  Therefore, I do not think SQL has hit its stride yet.

Programmers create a dozens of new languages every 10 years or so.  Only a few 
have stood the test of time.  SQL is one of those.  For all its faults, it 
still is amazingly powerful.

Neil
www.fairwindsoft.com

Re: Timestamp with vs without time zone.

[FWS Neil]

> On Sep 21, 2021, at 12:34 PM, Dave Cramer  wrote:
> On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer  > wrote:
> On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
> > That's all true and I won't argue about the madness that is timezones
> > in the world. I am simply thinking it would be some sort of a struct
> > like thing which would store the numerical value of the time stamp and
> > also the time zone that time was recorded in.  Presumably everything
> > else is an insane calculation from there. What was the offset on that
> > day? I guess it depends on the daylight savings time. What would the
> > conversion to another time zone be? That would depend on the DST
> > settings on that day in both places.
> 
> Yes, but HOW IS THAT TIME ZONE STORED? 
> 
> As a user you can say "I don't care, just make it work somehow".
> 
> But as a developer you have to decide on a specific way. And as a
> database developer in particular you would have to choose a way which
> works for almost everybody.
> 
> And that's the problem because ...
> 
> > Mankind can't agree on what side of the road to drive on, what the
> > electrical voltage should be at the wall, what those plugs should be,
> > how you should charge your phone or anything else for that matter
> 
> ... people have different needs and it would be difficult to satisfy
> them all.
> 
> Simply storing an offset from UTC is simple, fast, doesn't take much
> space - but it would be almost as misleading as the current state. A
> simple offset is not a time zone.
> 
> Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
> identifier for what most people think of as a time zone - but that takes
> a lot of space, it needs a lookup for almost any operation and worst of
> all, you couldn't index such a column (at least not with a btree index)
> because the comparison functions aren't stable.
> 
> You could use a numeric indentifier instead of the name, that would take
> less space but wouldn't solve the other problems (and add the problem
> that now you have just added another mapping which you need to maintain).
> 
> There are other ways, but I'm sure they all have some pros and some
> cons. None will be perfect.
> 
> So I don't think there is an obvious (or even non-obvious, but clearly
> good) way for the PostgreSQL developers to add a real "timestamp with
> timezone" type.
> 
> As an application developer however, you can define a compound type (or
> just use two or three columns together) which satisfies the needs of
> your specific application.
> 
> > It's just that the phrase "timestamp with time zone" would seem to
> > indicate the time zone is stored somewhere in there.
> 
> I absolutely agree. Calling a type which doesn't include a timezone
> "timestamp with timezone" is - how do I put this? - more than just
> weird. "timestamp without timezone" should be called "local timestamp
> with unspecified timezone" and "timestamp with timezone" should be
> called "global timestamp without timezone". However, those aren't SQL
> names.
> 
> 
> I would say this is a perspective thing. It's a timestamp with a time zone 
> from the client's perspective.

A timestamp cannot have a time zone and be a valid timestamp.

Let me explain.

A timestamp is a single time that exists in the world.  For example March 1, 
2021, 4:15 am is a timestamp.

If you add a time zone (other than UTC) then a time stamp is not always a 
single time that exists in the world.

For example in the spring using time zone American/Chicago, on April 14, 2021 
the time zone time changes at 2am to become 3am.  The time April 14, 2021, 2:30 
am simply does not exists.  And therefore cannot be a timestamp.  Apple’s APIs 
will by default automatically change 2:30am to 3:00am.  Is that correct?  Or 
should it change to 3:30am?  Apple has the option for the latter, but the APIs 
don’t work.

In the fall it is even worse.  Using time zone America/Chicago, on November 7, 
2021, 1:30 am occurs twice.  That does not work as a timestamp.  Which one do 
you use, the early one or the late one.  Apple’s APIs give you a choice.

The point being that people do expect to see times in local time, but the only 
real timestamp is UTC and I can’t ever imagine a need to store time zone 
information related to a timestamp.  If you need to store the location that 
data originated from, then store the location or the Time Zone, but it should 
not be connected to the timestamp.  Location data is completely different than 
time data.

Neil
www.fairwindsoft.com