3 ms·

The main problem here is that the description you're referring to was not updated. That's not great, of course, but bashing PostgreSQL standards conformance mer

The main problem here is that the description you're referring to was not updated. That's not great, of course, but bashing PostgreSQL standards conformance merely based on a single high-level description is not cool either. Let me quote from the full documentation for PostgreSQL 9.6, released in September 2016, i.e. before SQL-2016 was published: > PostgreSQL supports most of the major features of SQL:2011. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2011. For more details see: https://www.postgresql.org/docs/9.6/static/features.html https://www.postgresql.org/docs/9.6/static/features.html

sbuttgereit 9y ago
Looks like my original response was wrong, too. > PostgreSQL development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time. Though I'll take refuge in the, "where such conformance does not contradict traditional features or common sense", clause ;-) .
- avmich 9y ago
  > "where such conformance does not contradict traditional features or common sense" I'd prefer to have traditional features to move towards standard compliance - and to do have it as a priority - and also to have standards exhibiting the common sense which doesn't need to be questioned too much. Eventually it would be good I think to have DBMSes drop-in replaceable and until that goal isn't reached it's not good enough for some practical purposes. In this sense "sometimes with different syntax" reads surprising and "or function" is even more so. Did we all forget how and why SQL standards were created in the first place? How customers were demanding a certain level or interoperability? Do we think we don't need that anymore and the past reasons aren't there?
  - pgaddict 9y ago
    Your preferences are one thing, your patches or having a say into the development is a very different thing. The standards conformance is not a goal on it's own. The primary goal of the developers is to serve the users, particularly those that support the community in some way (e.g. by paying the developers). That's what makes the project possible. Development of features takes time, and the features are often introduced years before SQL standard eventually codifies them. And Postgres community has pretty much no influence on the SQL Standard, which is driven mainly by large companies. The last thing the users want is having to do significant changes to their applications just before the syntax suddenly changed to match SQL standard. Standards are valuable, but backwards compatibility has a value too. > Did we all forget how and why SQL standards were created in the first place? How customers were demanding a certain level or interoperability? Do we think we don't need that anymore and the past reasons aren't there? The SQL standard was created so that each database did not invent it's own query language. Postgres had one (called QUEL), and abandoned it in favor of SQL in 95 or so. Because people wanted the same general API to databases, not because they wanted the databases to behave the same. I strongly disagree with the idea that databases should be drop-in replaceable, as it entirely ignores the large parts of the fuctionality (architecture, indexing, ...) which is out of scope of a SQL standard.
    - avmich 9y ago
      > Your preferences are one thing, your patches or having a say into the development is a very different thing. That's correct; "saying", as I do, and "having a say" are different. In practice however my options as a user of DBMS are limited - I don't know if I can buy a standard compliant DBMS at all, not to say for market-competitive price, and I'm not sure I'll have an opportunity to replace it with another standard compliant implementation if for some reasons I'll want to have a better match. In other words, I'm market taker, and not market maker. I do suspect there are many DB users in this same position, which would benefit from a possibility to switch from DBMS to DBMS. > The standards conformance is not a goal on it's own. The primary goal of the developers is to serve the users, particularly those that support the community in some way (e.g. by paying the developers). That's what makes the project possible. It would be nice if developers of a leading open source DBMS paid more attention to standards - that's what I'm saying. I don't believe their users would suffer from that, or even that their users are sufficiently indifferent to that - that's neither my own experience nor what I'm reading in articles touching choices of DB. > The last thing the users want is having to do significant changes to their applications just before the syntax suddenly changed to match SQL standard. Standards are valuable, but backwards compatibility has a value too. I understand this as you saying "we have a choice between features and standards, and we choose features". Yes, backwards compatibility has value; in my opinion, value of standard following isn't understood well enough. I think we aren't in the world where performance is uber important for all use cases; those involved in development of PostgreSQL are either not fully aware of the needs of - some, may be non-paying - users, or choose to postpone this work, that's my understanding of the situation. > The SQL standard was created so that each database did not invent it's own query language. Exactly. Same language means that if I have, say, window functions or isolation levels in SQL standard and use them, I can expect to have them and them to be functionally equivalent in standard compliant implementation. It's a sad situation when there are just a few, if any, implementations of the standard; why do we have standards in the first place become then questionable. > I strongly disagree with the idea that databases should be drop-in replaceable, as it entirely ignores the large parts of the fuctionality (architecture, indexing, ...) which is out of scope of a SQL standard. As I understand it you're saying that it's impossible or hard to have large parts of functionality in a standard compliant implementation. I think that you could have extension features on top of standard, allowing users to use them when they choose to have extensions or not to use them when they need to have conformance to standards. I've had discussions regarding DB choice based on features which are in non-implemented standards enough to pay attention to the matter.
- pgaddict 9y ago
  I don't see why would that make the original response wrong?
  - sbuttgereit 9y ago
    It's not entirely wrong to be sure. But the case I was laying out was, in part, predicated on the PostgreSQL devs taking a more conservative approach top standards compliance than they actually are (per that sentence about their goals relative to standards compliance). So in that regard I was wrong. That's not to say the devs are being reckless about it either, which they've made clear in their noting exceptions to that goal.
avmich 9y ago
I think the reason why standards are created is to be able to choose among various implementations on the basis of something other than what is the set of technical features. In this manner one can choose performance, convenience of licensing or something else and not to worry about will his program work the same or work at all with different DB engine. If we don't have standard compliance, this becomes that much harder. Now engineers and whole companies have to choose in advance what to base their systems on, and evolve further often with keeping that constraint for the whole project lifetime. More, different DBMS vendors feel the ability to market some features while paying less attention to standard compliance, which leads to bigger incompatibilities between DBMSes. So the whole point of having standards in the first place gets eroded. In this light both mentioning a long list of supported features and pointing that other databases do no better is a poor substitute to leading the industry in standard compliance and all associated benefits. I think PostgreSQL can do better.

sbuttgereit 9y ago

Looks like my original response was wrong, too. > PostgreSQL development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time. Though I'll take refuge in the, "where such conformance does not contradict traditional features or common sense", clause ;-) .

avmich 9y ago
> "where such conformance does not contradict traditional features or common sense" I'd prefer to have traditional features to move towards standard compliance - and to do have it as a priority - and also to have standards exhibiting the common sense which doesn't need to be questioned too much. Eventually it would be good I think to have DBMSes drop-in replaceable and until that goal isn't reached it's not good enough for some practical purposes. In this sense "sometimes with different syntax" reads surprising and "or function" is even more so. Did we all forget how and why SQL standards were created in the first place? How customers were demanding a certain level or interoperability? Do we think we don't need that anymore and the past reasons aren't there?
- pgaddict 9y ago
  Your preferences are one thing, your patches or having a say into the development is a very different thing. The standards conformance is not a goal on it's own. The primary goal of the developers is to serve the users, particularly those that support the community in some way (e.g. by paying the developers). That's what makes the project possible. Development of features takes time, and the features are often introduced years before SQL standard eventually codifies them. And Postgres community has pretty much no influence on the SQL Standard, which is driven mainly by large companies. The last thing the users want is having to do significant changes to their applications just before the syntax suddenly changed to match SQL standard. Standards are valuable, but backwards compatibility has a value too. > Did we all forget how and why SQL standards were created in the first place? How customers were demanding a certain level or interoperability? Do we think we don't need that anymore and the past reasons aren't there? The SQL standard was created so that each database did not invent it's own query language. Postgres had one (called QUEL), and abandoned it in favor of SQL in 95 or so. Because people wanted the same general API to databases, not because they wanted the databases to behave the same. I strongly disagree with the idea that databases should be drop-in replaceable, as it entirely ignores the large parts of the fuctionality (architecture, indexing, ...) which is out of scope of a SQL standard.
  - avmich 9y ago
    > Your preferences are one thing, your patches or having a say into the development is a very different thing. That's correct; "saying", as I do, and "having a say" are different. In practice however my options as a user of DBMS are limited - I don't know if I can buy a standard compliant DBMS at all, not to say for market-competitive price, and I'm not sure I'll have an opportunity to replace it with another standard compliant implementation if for some reasons I'll want to have a better match. In other words, I'm market taker, and not market maker. I do suspect there are many DB users in this same position, which would benefit from a possibility to switch from DBMS to DBMS. > The standards conformance is not a goal on it's own. The primary goal of the developers is to serve the users, particularly those that support the community in some way (e.g. by paying the developers). That's what makes the project possible. It would be nice if developers of a leading open source DBMS paid more attention to standards - that's what I'm saying. I don't believe their users would suffer from that, or even that their users are sufficiently indifferent to that - that's neither my own experience nor what I'm reading in articles touching choices of DB. > The last thing the users want is having to do significant changes to their applications just before the syntax suddenly changed to match SQL standard. Standards are valuable, but backwards compatibility has a value too. I understand this as you saying "we have a choice between features and standards, and we choose features". Yes, backwards compatibility has value; in my opinion, value of standard following isn't understood well enough. I think we aren't in the world where performance is uber important for all use cases; those involved in development of PostgreSQL are either not fully aware of the needs of - some, may be non-paying - users, or choose to postpone this work, that's my understanding of the situation. > The SQL standard was created so that each database did not invent it's own query language. Exactly. Same language means that if I have, say, window functions or isolation levels in SQL standard and use them, I can expect to have them and them to be functionally equivalent in standard compliant implementation. It's a sad situation when there are just a few, if any, implementations of the standard; why do we have standards in the first place become then questionable. > I strongly disagree with the idea that databases should be drop-in replaceable, as it entirely ignores the large parts of the fuctionality (architecture, indexing, ...) which is out of scope of a SQL standard. As I understand it you're saying that it's impossible or hard to have large parts of functionality in a standard compliant implementation. I think that you could have extension features on top of standard, allowing users to use them when they choose to have extensions or not to use them when they need to have conformance to standards. I've had discussions regarding DB choice based on features which are in non-implemented standards enough to pay attention to the matter.
pgaddict 9y ago
I don't see why would that make the original response wrong?
- sbuttgereit 9y ago
  It's not entirely wrong to be sure. But the case I was laying out was, in part, predicated on the PostgreSQL devs taking a more conservative approach top standards compliance than they actually are (per that sentence about their goals relative to standards compliance). So in that regard I was wrong. That's not to say the devs are being reckless about it either, which they've made clear in their noting exceptions to that goal.

avmich 9y ago

I think the reason why standards are created is to be able to choose among various implementations on the basis of something other than what is the set of technical features. In this manner one can choose performance, convenience of licensing or something else and not to worry about will his program work the same or work at all with different DB engine. If we don't have standard compliance, this becomes that much harder. Now engineers and whole companies have to choose in advance what to base their systems on, and evolve further often with keeping that constraint for the whole project lifetime. More, different DBMS vendors feel the ability to market some features while paying less attention to standard compliance, which leads to bigger incompatibilities between DBMSes. So the whole point of having standards in the first place gets eroded. In this light both mentioning a long list of supported features and pointing that other databases do no better is a poor substitute to leading the industry in standard compliance and all associated benefits. I think PostgreSQL can do better.