Thread: SQL conformance related patch
Hello, Simon Riggs, Elein Mustain, and I have worked on adjustments to the information schema and parts of the documentation to reflect upcoming changes in PostgreSQL 8 and changes from SQL:1999 to SQL:2003. Attached is the result of that: A "diff -c" patch for current CVS HEAD. The patch is also available via HTTP[1]. The patch subsumes Simon's patch of November 21 with subject "SQL Conformance introductory section"[2]. Peter's objection to Simon's choice of words has been accommodated. We've had some discussions regarding feature ID F411 (Time zone specification). If we were to be strict, PostgreSQL's support for F411 should be changed to NO, due to a difference in TIMESTAMP literal handling. As it is, the status remains YES, but a comment has been added in the information schema, and the datetime documentation has been expanded a bit. Simon also has ideas for improvement of the conformance documentation page (features.sgml), but that's probably a version 8.1 thing. I think he'd better describe the ideas himself. The list of probably-not-but-just-maybe-supported features in my message as of October 20[3] remains unchanged, but as previously discussed on this list, time is running out. As previously mentioned, I've created a little list of "low hanging fruits" for PostgreSQL's standards-conformance - i.e. features in SQL:2003 which PostgreSQL currently doesn't support but which might actually be relatively easy to implement: http://troels.arvin.dk/db/pgsql/conformance/low-hanging-fruits.txt References: 1: http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance.patch 2: http://article.gmane.org/gmane.comp.db.postgresql.devel.patches/10318 3: http://article.gmane.org/gmane.comp.db.postgresql.devel.documentation/1278 -- Greetings from Troels Arvin, Copenhagen, Denmark
Attachment
Troels Arvin wrote: > Simon Riggs, Elein Mustain, and I have worked on adjustments to the > information schema and parts of the documentation to reflect upcoming > changes in PostgreSQL 8 and changes from SQL:1999 to SQL:2003. Thanks. I will evaluate this patch. Btw., does anyone mind if I change the names of the standards to SQL 1992 SQL 1999 SQL 2003 ? The other styles seem to be rather contrived and are not applied consistently. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Thu, 25 Nov 2004 19:16:47 +0100, Peter Eisentraut wrote: > Btw., does anyone mind if I change the names of the standards to > > SQL 1992 > SQL 1999 > SQL 2003 > > ? The other styles seem to be rather contrived and are not applied > consistently. We have tried to use the official[1] short names: SQL-92 SQL:1999 SQL:2003 - But it's true that those short names are not used consistently throughout the documentation. I see no reason not to use the offical short names; to me, they look OK text-wise. But it boils down to a matter of taste. Note 1: http://books.elsevier.com/mk/default.asp?isbn=1558604561 has a section on this subject. -- Greetings from Troels Arvin, Copenhagen, Denmark
Troels Arvin wrote: > We have tried to use the official[1] short names: > SQL-92 > SQL:1999 > SQL:2003 I could buy "SQL:1999", because then "SQL" would be a macro expanding to "ISO/IEC 9075". But I don't see how SQL-92 fits in there. > Note 1: > http://books.elsevier.com/mk/default.asp?isbn=1558604561 has a > section on this subject. Note that this book is titled "SQL 1999 - Understanding Relational Language Components". Apparently, they are not sure either. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Thu, 25 Nov 2004 22:13:01 +0100, Peter Eisentraut wrote: > I could buy "SQL:1999", because then "SQL" would be a macro expanding to > "ISO/IEC 9075". But I don't see how SQL-92 fits in there. There was a naming change between SQL-92 and SQL:1999, according to the mentioned book. The ":" is because the standard's name has been brought in line with ISO's naming standards. The expansion to four digits was because of the general year-2000 trend. >> Note 1: >> http://books.elsevier.com/mk/default.asp?isbn=1558604561 has a >> section on this subject. > > Note that this book is titled "SQL 1999 - Understanding Relational > Language Components". Apparently, they are not sure either. Yes, that's ironic. But I have the book right here. There is clearly a ":" in the name on the front page, as well as in the colophon. -- Greetings from Troels Arvin, Copenhagen, Denmark
On Thu, 2004-11-25 at 11:24, Troels Arvin wrote: > Simon also has ideas for improvement of the conformance documentation > page (features.sgml), but that's probably a version 8.1 thing. I think > he'd better describe the ideas himself. ...since you mention it, I should explain: The sections Supported Features and Unsupported Features cover both Mandatory (Core) and Optional features in the same section. It would be better to separate these, just as the SQL standard itself does in Annex F - SQL Feature Taxonomy. This seems especially important for the Unsupported Features section, since the length of the list makes it look like 100% support is a long way off, whereas it is only 14 features away, and many of them minor [see Troels' low hanging fruit list on this thread] Many of the optional features are definitely desirable and it is useful information to retain the Unsupported optional features, but would be best as a separate sub-section. Troels tells me the sections are autogenerated, which makes the re-editing slightly more difficult, so worth consultation first. Not sure who knows how to do that...or what techniques are considered portable...XSLT?? other than maintaining two separate lists. -- Best Regards, Simon Riggs
Simon Riggs wrote: > The sections Supported Features and Unsupported Features cover both > Mandatory (Core) and Optional features in the same section. It would > be better to separate these, just as the SQL standard itself does in > Annex F - SQL Feature Taxonomy. > > This seems especially important for the Unsupported Features section, > since the length of the list makes it look like 100% support is a > long way off, whereas it is only 14 features away, and many of them > minor [see Troels' low hanging fruit list on this thread] If the "core" set of features were at all useful in practice then I would think about this, but it is not, so we'd just end up arranging the tables for marketing purposes instead of information purposes. Ten years ago this would have been equivalent to making a separate section for SQL 92 Entry level and rejoicing upon completion, while realizing that a real-life DBMS needs at least Intermediate level. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Troels Arvin wrote: > Simon Riggs, Elein Mustain, and I have worked on adjustments to the > information schema and parts of the documentation to reflect upcoming > changes in PostgreSQL 8 and changes from SQL:1999 to SQL:2003. I have installed your patch and adjusted the names of the standards throughout to the spellings suggested by your book. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Fri, 2004-11-26 at 22:34, Peter Eisentraut wrote: > Simon Riggs wrote: > > The sections Supported Features and Unsupported Features cover both > > Mandatory (Core) and Optional features in the same section. It would > > be better to separate these, just as the SQL standard itself does in > > Annex F - SQL Feature Taxonomy. > > Please note that all that has been suggested is splitting a table into two pieces, so that it matches the SQL:2003 standard's way of presenting this information, as laid out in Annex F - SQL Feature Taxonomy. I found that arrangement useful in understanding the standard and wished to recommend it to the project. > > This seems especially important for the Unsupported Features section, > > since the length of the list makes it look like 100% support is a > > long way off, whereas it is only 14 features away, and many of them > > minor [see Troels' low hanging fruit list on this thread] > > If the "core" set of features were at all useful in practice then I > would think about this, but it is not, so we'd just end up arranging > the tables for marketing purposes instead of information purposes. Ten > years ago this would have been equivalent to making a separate section > for SQL 92 Entry level and rejoicing upon completion, while realizing > that a real-life DBMS needs at least Intermediate level. I agree completely with your assessment of SQL-92 Entry and Intermediate level. Having recently spent an hour or two looking at the SQL:2003 standard, I don't think the analogy that SQL:2003 Core is similar to SQL-92 Entry level is a useful one. I understand why people would think this, because I would definitely have thought exactly the same, before I looked. For example, Microsoft SQL Server claims SQL-92 Entry level. If SQL:2003 were similar then they would simply switch the claim to SQL:2003 without problem. They do not, because they cannot. Please review what the list of SQL:2003 Core features contains: SAVEPOINTS, outer joins, triggers, derived tables, quantified sub-selects, constraints etc.. but not object-relational features, which are only Optional. IMHO these features are useful in practice. Yes, there are also many Optional features that are also desirable. -- Best Regards, Simon Riggs
On Sat, 27 Nov 2004 22:27:13 +0100, Peter Eisentraut wrote: > I have installed your patch and adjusted the names of the standards > throughout to the spellings suggested by your book. Great. A follow-up patch for current CVS HEAD is attached, and available at http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance- followup.patch The patch - includes a core feature ID that had been left out by mistake (C011) - updates the sql_feature_packages.txt table to reflect changes in SQL:2003 which were not covered properly in my last patch -- Greetings from Troels Arvin, Copenhagen, Denmark
Attachment
Patch applied. Thanks. --------------------------------------------------------------------------- Troels Arvin wrote: > On Sat, 27 Nov 2004 22:27:13 +0100, Peter Eisentraut wrote: > > > I have installed your patch and adjusted the names of the standards > > throughout to the spellings suggested by your book. > > Great. > > A follow-up patch for current CVS HEAD is attached, and available at > http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance- > followup.patch > > The patch > - includes a core feature ID that had been left > out by mistake (C011) > - updates the sql_feature_packages.txt table to > reflect changes in SQL:2003 which were not > covered properly in my last patch > > -- > Greetings from Troels Arvin, Copenhagen, Denmark [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073