Re: PostgreSQL vs SQL Standard

On Sun, Jun 10, 2018 at 11:32 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
>> I beat at the grammar a bit to see what it would take to fix it at least
>> to the extent of allowing a_expr ColId in a select list after removing
>> postfix ops. It looked like it was doable by making these keywords more
>> reserved (all of which are already reserved words per spec):
>> DOUBLE, DAY, FILTER, HOUR, MINUTE, MONTH, OVER, PRECISION, SECOND,
>> VARYING, WITHIN, WITHOUT, YEAR
>
> Yeah, a side effect of allowing "a_expr ColId" is that we can expect,
> going forward, that a lot of new keywords are going to have to become
> fully reserved that otherwise wouldn't have to be. This is particularly
> a problem because of the SQL committee's COBOL-hangover tendency to
> invent new syntax that involves sequences of keywords; we usually
> don't have a good way to deal with that short of making the first
> keyword(s) reserved.
>
> It's arguable that going down that path will, in the long run, lead to
> breaking more applications (via their table/column names suddenly becoming
> reserved in some new version) than we rescue from having to quote their
> SELECT aliases. At the very least we need to recognize that this is far
> from cost-free.

It depends on the source of those applications. Applications
originally written for PostgreSQL are going to break if we reserve
those keywords. Applications originally written for other databases
that already reserve those words won't, and migrating to PostgreSQL
will become easier for those people. I think this is to some extent a
question about whether it's more important to have backward
compatibility with our own previous releases or compatibility with
other SQL database systems.

> (wanders away wondering exactly what parsing technology the SQL committee
> thinks implementations use...)

I wonder about this, too, because I've noticed that one other system
in particular seems to do parsing in a quite different manner than we
do (but I have no idea specifically how they do it). bison is a good
tool in many ways and converting to something else would be a huge
amount of work, but there are also a number of things about it that
are not so great:

- The fact that conflicts can only be resolved with precedence
declarations sucks. You can only set the priority of rule A relative
to rule B unless they've both got a precedence assigned, and there's
frequently no non-invasive way to accomplish that. It would be nice
to be able to mark the postfix operator production as "weak" so that
it's only used when no other interpretation is possible. Of course
this kind of thing could be overdone leading to a confusing grammar,
but it's not a bad idea to be able to do things like this to handle
corner cases, at least IMHO.

- It really only handles LR(1), but for certain cases more tokens of
lookahead would be really useful. NULLS_LA and NOT_LA are nasty hacks
to work around the lack of adequate lookahead, and there are other
cases where we could do nicer things if we had it.

- There's no way to modify the parser at runtime, which means our
extension facility can't invent new SQL syntax, not even e.g. CREATE
THINGY and DROP THINGY. (Our lack of support for thingys is really
holding us back.)

- The release cadence is extremely slow. They just released 3.0.5,
but 3.0.4 came out in 2015 and 3.0.3 in 2013. It's hard to imagine
bison going away completely, but it's also pretty hard to imagine it
ever having any new features that would help us. And if we find a
bug, waiting for the next release to fix it is going to be painful.
To all appearances, it's only a little ahead of abandonware.

I'm not sure that there's a better tool than bison out there and I'm
not volunteering to write one in my spare time, but if something turns
up, we might not want to dismiss it out of hand.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company