Monday, April 19, 2010

News from the SQL Standard

Last week, I attended the meeting of DIN NA 043-01-32 AA, which is the German "mirror group" of ISO/IEC JTC1 SC32, whereof WG3 produces ISO/IEC 9075, which is titled "Database languages - SQL".  Once you dig through all these numbers and letters and find out who is responsible for what, it's actually quite simple to get involved there and review or contribute things.

For the benefit of everyone who is interested but hasn't had the chance to get involved in that process, here is what is currently going on:
  • A new standard is currently in the "Final Committee Draft" (FCD) phase, which basically means "beta".  The final release is expected in 2011, so you will begin to see mentions of "SQL:2011".
  • The new standard will only contain parts 1/Framework, 2/Foundation, 4/PSM, 11/Schemata, 14/XML. The other parts are currently not being developed, which doesn't mean they are dead or withdrawn, but that no one bothers to add things to them at the moment.
  • All new features in SQL:2011 will be in the form of optional features.  So the level of core conformance is not impacted by the release of a new standard.
There isn't actually that much new in SQL:2011, besides countless fixes and clarifications.  I counted only a dozen or so new features.  Here are some things that might be of interest to the PostgreSQL community:
  • The syntax ALTER TABLE ... ALTER COLUMN ... SET/DROP NOT NULL has been taken into the standard.  PostgreSQL has supported that since version 7.3.  Coincidence?  Not sure.
  • Constraints can optionally be set to NO ENFORCE.  That means the database system won't enforce them but still assumes they are valid, for example for optimization.
  • System-versioned tables: Perhaps the largest new feature, this is a way to make data (rows) visible only during certain times. Some pundits might recall a moral predecessor of this feature labeled SQL/Temporal.  I haven't fully analyzed this feature yet, so I'll post later about the details.
  • Combined data change and retrieval. PostgreSQL does something like this with RETURNING, but this feature is more elaborate and allows the writing of "delta tables".
  • Named arguments in function calls. PostgreSQL 9.0 supports that, but using the syntax foo(3 AS a) instead of what ended up in the standard, foo(a => 3).
  • Default values for function arguments. PostgreSQL 9.0 supports that as well.
This time I attended the meeting as a guest. We are discussing some procedural and financial issues to make this an official membership.  If anyone else is interested in getting involved in the SQL standard development, let me know and I can point you in the right direction.  If we have enough interest, we can set up a discussion group within the PostgreSQL project.


  1. I'm excited to see PostgreSQL 9.0, I didn't know it supported default arguments, but that's very welcome for me; I've had several situations where I thought "man, some default arguments would REALLY HELP here."

    The new standard also sounds pretty cool with the "delta tables." I've loved "RETURNING" but I imagine it could be even more useful. The other details are interesting but haven't jumped out at me as something I've needed, so (while cool) don't hit me as quite as awesome. The system-versioned tables SOUNDS neat, but again, it's not something I'm getting in a tizzy over (yet) cause I just don't know what exactly it means for me.

  2. Funny that foo(a=>3) of all things is being used for the named argument syntax. This is exactly the syntax that my Muldis D language uses for its named arguments, and that was based on Perl's syntax. I say funny because I don't recall => appearing in SQL anywhere before.

  3. It seems strange that the named arguments use "=>" in the standard -- are there any other remotely similar things in the standard?

    If we're 99% sure it will be in the standard that way, is it worth it to try to make postgresql conform before 9.0 is released?

  4. To Stephen: DEFAULTs was implemented in 8.4.

    To Jeff: It is sad, but changes are not possible now. Problem is in compatibility -> hstore uses => operator. So we have to have mark this functionality as deprecated :(

  5. The NO ENFORCE part seems useful. I think having the optimizer consider a non-enforced constraint as valid is not a good idea because it could lead to incorrect or even corrupt output. However, initially creating a constraint as not enforced (meaning that the extant data is not known to be conformant to the new constraint, but newly added data would be forced to conform) could help with reducing the time that the table is exclusively locked.

  6. Pavel - Thanks for the correction, I must have overlooked it by being in the wrong version of the documentation. I typically search Google for what I'm looking for, which dumps me into a seemingly-random version's docs, after which I normally only think to correct if it's <8.0. It's a habit I definitely need to break.

  7. The NO ENFORCE thing does sound good. That was a feature I had been thinking about called Virtual Assertions, to allow the optimizer to act like they were true, though without actually doing press-ups to enforce them. Sounds like we're going in the right direction finally at the SQL Standard level.

  8. There are a bunch of different use cases for the NO ENFORCE stuff, I've certainly been arguing for some of them for quite some time. That Oracle had them was never a persuasive argument in the pg crowd, but with the standard now getting them as well, maybe there is hope.

  9. Named parameters in 9.0 use foo(a := 3), not foo(3 as a). 9.0 also deprecates => as an operator in anticipation of it being used for parameters in a future version.

  10. How about adding the following to the SQL standard: Referential joins (perhaps just called REFJOIN). This would be a safe version of NATURAL JOIN which would (only) take into account foreign key relationships when joining two tables. Like NATURAL JOIN, it would allow for terse join expressions - but in a safe way where changes in table structure wouldn't be dangerous.