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.
- 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.
Posts
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."
ReplyDeleteThe 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.
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.
ReplyDeleteIt seems strange that the named arguments use "=>" in the standard -- are there any other remotely similar things in the standard?
ReplyDeleteIf 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?
To Stephen: DEFAULTs was implemented in 8.4.
ReplyDeleteTo 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 :(
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.
ReplyDeletePavel - 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.
ReplyDeleteThe 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.
ReplyDeleteThere 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.
ReplyDeleteNamed 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.
ReplyDeleteHow 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.
ReplyDelete