LWN: Comments on "PostgreSQL and the SQL standards process"

PostgreSQL and the SQL standards process

Jannes — Wed, 05 Oct 2011 10:52:25 +0000

Not sure what you're getting at actually. Why use a function at all, does the GETDATE have any use?

Why not use constants .... AND 0 < 78238923123 (your random tag) ?

And while you're at it, why not just make it a comment? I've used that before to make queries easily identifiable in SHOW PROCESSLIST: (in MySQL)

/* YourTag Function Name blabla */ SELECT * FROM blabla;

The comment has the extra advantage of not affecting the query cache at all (at least in MySQL).

PostgreSQL and the SQL standards process

JanC_ — Sun, 02 Oct 2011 19:52:05 +0000

And "A4" was defined by DIN (the German standards body) before ISO copied it.

PostgreSQL and the SQL standards process

Baylink — Fri, 30 Sep 2011 19:06:52 +0000

Well, I thought it was the American Standards Association (now ANSI) that said what made "film" "400 speed", but maybe that's just me. So many things are just me.

PostgreSQL and the SQL standards process

Baylink — Fri, 30 Sep 2011 19:04:53 +0000

Very specifically: I'd like to see something that makes it easy to trace queries in, say, "mtop" back to the source code whence they came.

In that vein, and since the proper collection of eyeballs are probably reading the commments to this particular posting, I'd like to propose a device which might or might not work in that context, and get the input of people who know which:

Select $FIELDS from $TABLE where $CONDITIONS *AND NOT GETDATE() equals 1011-03-01* (where the date is some date earlier than today, and specific to each query; it serves as a tag).

Kevin Falcone, who I think is one of the SQL gurus on RT, suggested this would break the query cache, but I'm not sure that's true a) because it would be the same on any given query, and b) because it depends on a function, which can be evaluated away as part of the optimizer (assuming that keeps it from messing up the cache, which I don't know; hence, you folks. :-)

Anyone have an opinion on this, or the larger problem it's aimed at?

PostgreSQL and the SQL standards process

Baylink — Fri, 30 Sep 2011 18:43:01 +0000

You're effectively asking: "should queries be one string of text, instead of a whole sequence of API calls". (Unless I've misunderstood you badly, in which case, I apologize.)

The answer is "you've never been 400ms away from your database engine, have you"?

Yes, the architecture of SQL is the way it is for a reason. You young whippersnappers (:-) have never run a database engine with an API interface across even a LAN, much less a WAN, so you forget both halves of the latency term of the equation, much less the data reduction of having *all* the intermediate work happen inside the server.

Old fashions

eru — Tue, 27 Sep 2011 07:59:14 +0000

Maybe people who used to be COBOL programmers? Actually, syntax that tries to mimic natural language used to be a very common style in old domain-specific languages, the theory being that it makes them self-documenting.

PostgreSQL and the SQL standards process

raven667 — Mon, 26 Sep 2011 02:48:23 +0000

I think we largely agree on facts and are saying some of the same things from different perspectives. Right now ORMs and applications can support multiple databases but if the db/orm/language become more tightly coupled then I could see that no longer being the case. Applications built against newer databases like Couch and Mongo seem to be much less portable than traditional SQL applications.

As you say, if each database engine wrote their own custom ORM then developers could write better queries and get better results more safely but it would increase lock-in which is not an unmitigated positive.

PostgreSQL and the SQL standards process

jberkus — Sun, 25 Sep 2011 23:06:16 +0000

"You could end up with different query languages for each programming language, for each database server and being restricted on which language you can implement a program on based on how complex the queries are allowed to be, not on the database but on your interface to the database."

The thing is, we *already* have that. 80% of applications out there which interface with a SQL database use an ORM or similar high-level interface, and few ORMs support more than a couple of DBMSes. Developers are not using SQL to push data today.

Frankly, if database geeks took control of the ORMs and gave developers a user-friendly interface in their own language, it would be both better for developers and better for the databases. We could make sure that these interfaces *do the right thing* as far as database interfacing is concerned. The whole path of Call ORM --> Generate SQL String --> Parse SQL String --> Communicate Binary Protocol has at least one too many steps in it.

Couch and Mongo have gone partway there with JSON, but they're still essentially using intermediate query languages. And worse, BSON isn't even a standard anywhere, so it's completely non-portable.

Of course, we want to still support SQL as a direct interface for database geeks to do "advanced" work. But that's only about 10% of the database interaction out there. And I say this as a SQL expert.

PostgreSQL and the SQL standards process

njs — Sat, 24 Sep 2011 21:41:01 +0000

Many many industries care about ISO: https://secure.wikimedia.org/wikipedia/en/wiki/List_of_In...

For example, it's ISO standards that say what makes paper "A4", makes film "400 speed", says what the standard freight container sizes are, describe ISBN and ISSN barcodes, etc., etc.

I'm sure there's plenty of nonsense in plenty of ISO standards, but we do need *some* standard definitions of these things so that different people and businesses can talk to each other about complicated technical matters without having to check every detail all the time.

ISO does seem particularly ill-suited to handling software-related standards, though.

PostgreSQL and the SQL standards process

butlerm — Sat, 24 Sep 2011 21:16:30 +0000

What I want to know is how did the SQL standards process come to specify requirements that suggest extraordinarily bad taste. And by bad taste I mean inventing new commonplace functions that require special grammar rules to parse correctly. Such as the following:

SUBSTRING(s FROM x FOR y)
TRIM(LEADING ' ' FROM s)
POSITION('-' IN s)
EXTRACT(MONTH FROM d)

PostgreSQL and the SQL standards process

butlerm — Sat, 24 Sep 2011 20:38:47 +0000

>Oracle still considered that '' IS NULL

Oracle still does, and it probably won't ever change because it would break far too much software if they did. In Oracle's defense, most languages have extremely poor or non-existent support for nullable character strings, and so this decision tends to make working with Oracle easier in some respects.

In particular, you really don't want to index the "no relationship" values of an optional foreign key. Like an empty email address, for example. To accomplish this in most databases, you have to convert email addresses that are empty strings to nulls when inserting data into the database, and then convert nulls back to empty strings when you are done.

Personally, I wouldn't even like to use a database that distinguished between nulls and empty strings unless there was a convenient facility to do just that.

PostgreSQL and the SQL standards process

butlerm — Sat, 24 Sep 2011 20:25:12 +0000

If the ISO disappeared tomorrow, would anyone notice? It seems to me like a pointless government bureaucracy that has a net negative effect on nearly everything it touches, with a tendency to produce standards and meta-standards that no one actually uses.

PostgreSQL and the SQL standards process

Tobu — Fri, 23 Sep 2011 22:06:40 +0000

That looks a lot like academic publishing. Set up a lot of little fiefdoms where people and their employers compete for status, giving away senior roles to the first to join. Participants are the ones hashing out the work, reviewing it, getting it publication ready, and marketing it. The standard body/academic publisher owns exclusive rights to the publication though. Sell the work back to participants, libraries, and any interested parties that can afford it. Print money (Elsevier operates at a grander scale than ISO: 1.5 billion £ of annual revenue, mostly from subscriptions).

Better standards bodies are those with a clear royalty-free, patent-free mandate for both access and implementation.

listening to feedback

Simetrical — Fri, 23 Sep 2011 17:40:48 +0000

Yep, nothing is perfect by any means. It's a far cry better than what's described in the article, though. For what it's worth, some of the more recent specifications are far more detailed, and hopefully do contain enough information to pass their test suites. CSS 2.1 is ancient, although it only just made REC.

But all the CSS specs are less precise than the newer HTML/DOM specs. At some point we're going to have to rewrite CSS from scratch with the same level of precision as HTML and DOM have been rewritten. We've learned a lot over the last decade about how to write standards.

listening to feedback

pjm — Fri, 23 Sep 2011 13:29:57 +0000

What does "required to listen to feedback" mean? Sure, anyone can post to the mailing list, but my experience with the CSS mailing list (www-style) is that it can take months to get a reply, and the eventual reply can still be "we've decided not to address this" without giving any reason. There's a w3c policy document that says that this "should" not happen; yet happen it does.

While it's true that the CSS 2.1 specification is published on the web, it's also true that the published specification contains contradictions and does not contain enough information to pass the test suite.

Yes, it's like an open-source project: no-one's under any obligation to address the bug reports you file.

PostgreSQL and the SQL standards process

petereisentraut — Fri, 23 Sep 2011 13:26:41 +0000

I think you are implying a lot of implications. I'm just stating the facts and asking some questions. Now we have to find the answers.

PostgreSQL and the SQL standards process

petereisentraut — Fri, 23 Sep 2011 13:24:58 +0000

Well, sure, some people get paid to work on PostgreSQL. But participating usefully in the community, especially when it comes to extra activities like the one discussed here, goes beyond merely being told by some boss to work on some code base. Participating in the community is a volunteer effort, even if it's ultimately part of your job.

PostgreSQL and the SQL standards process

pflugstad — Fri, 23 Sep 2011 12:30:42 +0000

One other thing to remember is that often the representatives to this standards bodies are entirely non-technical. They are professional standards body attendees, and they don't really understand the material they're looking at. They have zero concept of how easy or hard or literally impossible something is to actually implement. So you get silly stuff like this.

PostgreSQL and the SQL standards process

nix — Fri, 23 Sep 2011 10:55:37 +0000

You could use the same reasoning to argue that nobody in the free software community would sit on the C++ committee, or that nobody in the free software community would get involved with POSIX. But the former has substantial free software representation and the latter is by now pretty much entirely driven by the needs of the Linux community.

So your reasoning appears somewhat flawed. (Or, rather, contains an erroneous implication: that it requires quasi-gurus, sure. That there is something preventing these quasi-gurus from ever participating unless they are backed by a non-'volunteer organization', possibly. Your implication that non-volunteer organizations are necessarily proprietary software companies is plainly false. If this implication is dropped, your last paragraph becomes a non sequitur.)

PostgreSQL and the SQL standards process

cmccabe — Fri, 23 Sep 2011 09:22:06 +0000

I'm sure that people at SQL standards committee meetings are friendly. But flying halfway across the world at great expense and doing things behind closed doors is a 19th century way of doing things. Modern standards like HTML5 are developed in the open and discussed on mailing lists and IRC. This just makes things easier and cheaper for everyone-- especially users and developers of related software, who actually get a chance to see what developers are discussing.

Also, not all people who work on open source databases are volunteers.

PostgreSQL and the SQL standards process

ncm — Fri, 23 Sep 2011 05:35:45 +0000

That's a recent change. Curiously, it hasn't been a result of changes in ISO rules, but just interpretations of them. Committee Drafts are still secret, but Working Papers, effectively identical, are not. Likewise, published Standards. Committee mailing lists and issues lists exist entirely outside ISO's domain. Even voting rights make much less difference than one might think; actually doing the work carries more weight, because when in doubt, voters vote with whoever seems to know more, and most voters are always in doubt.

PostgreSQL and the SQL standards process

flewellyn — Fri, 23 Sep 2011 02:30:36 +0000

A Lisplike domain-specific language could do what you specify, although the ordering in point 1 wouldn't hold. It'd be prefix notation. If that's acceptable though, Lisp is already known to be easy to parse, transform, compose, and work with functionally.

PostgreSQL and the SQL standards process

Simetrical — Thu, 22 Sep 2011 22:17:06 +0000

Yep, definitely. But all the W3C standards I can think of that are undergoing active development, and that are targeted at web browsers, work entirely in the open these days. There may be exceptions. But even the worst WG in the W3C publishes its standards for free on the web, and is required to listen to feedback from the general public, so it's still way better than the SQL situation described in this article.

PostgreSQL and the SQL standards process

justincormack — Thu, 22 Sep 2011 22:06:50 +0000

A lot of the rest of the w3c doesnt work like this though, and a lot of this appened because of html5. Before tht there was a mess of unused xml standards...

PostgreSQL and the SQL standards process

dlang — Thu, 22 Sep 2011 20:14:09 +0000

you would not eliminate the conversion step, you would just replace the conversion between an text string and the internal data structure with the conversion between the binary sequence and the internal data structure.

different languages (and for that matter, different libraries for a single language) will want to represent the data in different ways.

it would be good to have a standard format that could eliminate the SQL injection type of vulnerability, but it's hard to think of anything that can take an arbitrary byte stream and use it in an arbitrary place in the statement, while still allowing multiple statements in one blob that would eliminate the problem

PostgreSQL and the SQL standards process

Cyberax — Thu, 22 Sep 2011 20:08:46 +0000

Doesn't really matter much for me.

Explicit query-construction API should express roughly the same ideas as the textual query representation.

PostgreSQL and the SQL standards process

iabervon — Thu, 22 Sep 2011 19:43:30 +0000

You do need some representation of the complete query you are performing. The question is actually: is there any benefit to having database operations go through a representation which is a standardized character sequence? Generally, both applications and databases have some sort of data structure representing a query, and the application flattens this into a character sequence which the database has to read in order to construct approximately the same data structure in order to analyze it. If the query API were specified in terms of a data structure instead of a character sequence, it would eliminate an awkward and error-prone step on each side.

PostgreSQL and the SQL standards process

petereisentraut — Thu, 22 Sep 2011 18:59:12 +0000

I have been to SQL standards committee meetings. Actually, the process is quite relaxed and open, and there is friendly cooperation across vendor lines, once you find your way in. So I don't believe the conspiracy theories. And there are no requirements about secrecy of the proceedings. You can read up on everything if you become a member of the working group or even just a temporary guest.

The main problems are (1) ISO politics and (2) the effort required to do the actual work.

First, the participants don't like the ISO politics either, but they're there, so what are you going to do? ISO has existed for 50+ years. Who else are you going to charge with maintaining your standard that is 20+ years old and should perhaps have another 20 years or so to go?

Second, writing actual technical standards requires experts and a lot of time. Who is going to do it? The work is currently done by a dozen people or so who have quasi-guru status within their sponsoring companies. Who is going to put in the time like this in a volunteer organization?

PostgreSQL and the SQL standards process

jwakely — Thu, 22 Sep 2011 18:42:19 +0000

Different parts of ISO do things differently too: the ISO C++ committee may not be perfect but by the sound of things it's a lot more open than the SQL one.

PostgreSQL and the SQL standards process

BenHutchings — Thu, 22 Sep 2011 15:33:01 +0000

I don't understand the secrecy of drafts or minutes, though. It's not an ISO rule; some committees do publish them.

PostgreSQL and the SQL standards process

spaetz — Thu, 22 Sep 2011 12:17:43 +0000

> seems pretty excessive, and so does the yearly expenses at USD 257.056 per employee.

Not if you ever needed to rent a flat or buy food in Geneva :-)

PostgreSQL and the SQL standards process

gerv — Thu, 22 Sep 2011 11:57:12 +0000

Taking back the SQL standard would be entirely possible, but you would need your own clone of Ian Hickson (editor of HTML5).

Gerv

PostgreSQL and the SQL standards process

Kwi — Thu, 22 Sep 2011 11:49:48 +0000

Considering that all standardization work is performed not by ISO itself, but by national standard bodies (often funded by tax payers), I don't buy this argument.

It is true that sales of publications is a major source of income - 30% according to the ISO website - but what is that money spent on?

ISO is in itself just the ISO Central Secretariat in Geneva. The secretariat:
- has 154 full-time employees
- costs USD 40 million a year to run

For a purely coordinating role, 154 employees seems pretty excessive, and so does the yearly expenses at USD 257.056 per employee. USD 40 million a year, and then you haven't even produced any standards yet!

To get any work done, you need to add an estimated USD 140 million a year spent by the national standard bodies on ISO work, and an unknown (but probably much higher) amount spent by the participating companies. And none of this is paid for by selling ISO standards!

ISO is a money sink.

Open Source and Standards

ingwa — Thu, 22 Sep 2011 10:08:36 +0000

I don't have much to offer on the subject of SQL or PostgreSQL. But I have been working with standard bodies around the Open Document Format. I work 0n the Calligra Suite (http://www.calligra-suite.org/) and we are actively involved in the development of that document standard.

I must say that this has meant a lot to us both because we have most of our concerns listened to and actually fixed and also because it has meant increased visibility for us. And being involved in the work to bring out a new standard gives you a credibility that is difficult to get in other ways.

I think that more open source projects, especially the larger ones, should get involved in the standard bodies that regulate the area they are active in. This can only bring good to those projects. True, you may have to deal with some politics, but you will have to do that anyway if you don't participate -- but without having any influence at all.

PostgreSQL and the SQL standards process

ekj — Thu, 22 Sep 2011 06:12:45 +0000

I didn't get that part either.

If you've got a database that already efficiently support

select * from [foo] limit 100 offset 100

Then surely making changes to the syntax-tree so that it instead (or additionally) supports:

select * from [foo] make a effing slice from 100 to 200

That's a horrible example, because *renaming* identical functionality is just about the simplest change anyone could possibly make.

PostgreSQL and the SQL standards process

smurf — Thu, 22 Sep 2011 05:55:53 +0000

You already have the standard operations anyway. They're called "functions" and "operators" and such. And you do need a common representation for that between client and server.

Right now, that common representation is text and SQL, plus (sometimes) a server-specific method to prepare statements to prevent having to transcode and quote data.

Replace that with XML or whatever all you want. Doesn't buy you anything. You still have to assemble a textual string on one end and analyze it on the other side.

Three choices here:

* stay with SQL. It mostly works, people are used to it, you can do interesting stuff with it in any programming language whatsoever. Downside: you still need server-specific code for more complicated queries and for schema.

* agree on a common high-level API for queries, and possibly for schema queries/changes. Downside: who'd do the work and who'd agree on the result? The commercial vendors? Hardly.

* agree on a common binary data stream between client and server. Same downside, plus doesn't work for embedded servers.

PostgreSQL and the SQL standards process

dps — Thu, 22 Sep 2011 04:16:59 +0000

Actually I know why ISO standards are not public. Some of the *funding* that pays for the ISO is raised by the sale of standards. I understand that the prices are significant, probably because expecting volume sales is unrealistic and some copying/sharing is inevitable.

Keeping the standards expensive may be in the interest of the large commercial outfits but it is not the only reason. Anything that undermines the sale of standards is obviously is the interests of neither the ISO nor the people that pay people to participate in the process.

PostgreSQL and the SQL standards process

BenHutchings — Thu, 22 Sep 2011 04:00:46 +0000

The irony of Microsoft and Oracle using SQL standardisation as a weapon is that neither of them are seem to be in much of a hurry to follow the standards themselves. When I last had the misfortune to work on a data access layer (about 2005), SQL Server was still using SQL-89 semantics in some cases where SQL-92 differed, and Oracle still considered that '' IS NULL. PostgreSQL seemed to be the most standard-conformant of the lot. Has this changed?

PostgreSQL and the SQL standards process

ringerc — Thu, 22 Sep 2011 03:56:37 +0000

For what it's worth, I've seen strong interest in following the standards on the Pg lists - even in cases where the standards are of dubious sense or downright stupid. That applies both for formal ISO standard features and where compatibility with major-vendor language extensions like to_char is in question. The documentation reflects that, with clear coverage of which Pg features are part of the standard and which are extensions.

There's certainly a lot of frustration with the standard process's exclusion of some of the key groups who use it, like open source database developers and JDBC driver developers. The difficulty of accessing the released standards documentation is another store point.

For users without corporate backing to buy $lots copies of the standards it is vary frustrating to have to rely on circulating old drafts and other vendors' documentation. This devalues the standard, because it's so hard to know what is and isn't standard that it's hard to code to the standard - so people just pick a vendor dialect and stop caring.

Personally, I think the best thing that can happen to the SQL standards process at this point is to put it quietly out of its misery and reboot the whole thing. We - end users, database implementers, middleware/ORM implementers, JDBC/ODBC/ADO/etc driver implementers, etc - need a language with the same core capabilities but a sane, machine-readable syntax. SQL's god-awful pseudo-natural-language monstrosity of a grammar is a nightmare to work with. Parsing it is hard enough, but transforming it and proving things about it is horrifying. I'd be delighted to see adoption of a language that could express all the concepts, but:

- Is ordered sanely, with subjects before predicates before outcomes;

- Is easily represented as an easy-to-work-with in-memory tree (like a DOM or similar, but hopefully not XML) for easy transformation and composition;

- Is designed to be composed from smaller pieces, so it's easy to shuffle around subqueries, predicates, etc without messing with the stupidities of "AND" and commas;

- Is capable of expressing the same queries and plans (for easy porting) so it still has inner, left outer, right outer, and full/cross joins, is still relational, still supports grouping, aggregation, windowing, etc;

- Gets rid of all pseudo-function language syntax in favour of using true functions or a generic and extensible expression structure that doesn't require syntax changes for every new feature like CTEs, window functions, etc;

- Is designed WITH THE ACTIVE INVOLVEMENT OF PEOPLE WHO IMPLEMENT JDBC/ODBC DRIVERS, and PEOPLE WHO HAVE IMPLEMENTED LANGUAGE INTEGRATED QUERYING OR CRITERIA APIs. This is a big one. The amount of suffering that SQL causes for JDBC driver developers cannot be understated, and they get off easy compared to the poor individuals who try to produce programmatic query builders/criteria APIs/language integrated querying like JPA 2.0 criteria, LINQ, etc.

- ... and most importantly, because it's a saner way of writing the same queries as SQL, can be transformed into SQL to be sent to backends that don't support it yet, so the chicken-and-egg problem of adoption vs support can be resolved.

Most current efforts like Muldis-D seem to miss the last point - that if you want adoption, you're going to need to be able to transform to SQL in the medium term so people can use it on platforms that don't yet support the new, saner language.

PostgreSQL and the SQL standards process

Cyberax — Thu, 22 Sep 2011 01:24:13 +0000

Yes, you can use LINQ in Mono. And there are alternative LINQ providers, like http://bltoolkit.net/Doc.Linq.ashx for various databases.

LINQ is just a mechanism which can be used to create DSLs to query data.