Thread: Wish: remove ancient constructs from Postgres
It is difficult to write standard-compliant code in Postgres. There are a lot of constructs which have SQL equivalents but are still used widely, even in samples in docs! For example, there are suggestions using now()::CHAR!=foo while the correct way is CAST(CURRENT_DATE AS CHAR)<>foo now() function, :: and != operators should be removed from language. I like the Python logic: there is one way Postgres uses C logic: there are multiple ways. Bruce seems to attempt start this process trying implement escape_string_warning in postgresql.conf . However, this is only very minor step. Please clean Postgres. Andrus.
As long as the SQL standard is supported, support for the "ancient" syntax shouldn't be removed - at least not without a very long period of transition. Do you have any idea how many applications the removal of something simple like the cast operator :: will break? It's not difficult to write standard-compliant code in PostgeSQL - just feel free to use the standard. Nobody forces you to use the uncomliant syntax - but don't try to force thousands of people to rewrite tons of code just because you don't like the old syntax. Oracle has stuff like that, DB2 has too. Things evolve over time and unless you give code-maintainers ample time to fix their applications a sudden drop of old constructs will potentially just harm the project's popularity. I agree with you that the docs and examples should be amended to show standard constructs, just to encourage the adoption of standard compliant statements. Feel free to volunteer some time to make this happen - I'm confident everyone in the community will appreciate it. UC On Sunday 26 February 2006 00:36, Andrus Moor wrote: > It is difficult to write standard-compliant code in Postgres. > There are a lot of constructs which have SQL equivalents but are still used > widely, even in samples in docs! > > For example, there are suggestions using > > now()::CHAR!=foo > > while the correct way is > > CAST(CURRENT_DATE AS CHAR)<>foo > > now() function, :: and != operators should be removed from language. > > I like the Python logic: there is one way > Postgres uses C logic: there are multiple ways. > > Bruce seems to attempt start this process trying implement > escape_string_warning in postgresql.conf . However, this is only very minor > step. Please clean Postgres. > > Andrus. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax: +1 707 568 6416
"Andrus Moor" <eetasoft@online.ee> writes:
> For example, there are suggestions using
> now()::CHAR!=foo
> while the correct way is
> CAST(CURRENT_DATE AS CHAR)<>foo
> now() function, :: and != operators should be removed from language.
Your second example requires twice as much typing as your first;
is it so surprising that some people prefer the shorter way?
We'd consider removing these features if they were actually blocking
support of some spec-required behavior ... but since they don't, it's
quite unlikely they'll ever be removed. Feel free not to use them
if you don't like them, but don't try to impose your viewpoint on
everyone else.
regards, tom lane
On Sun, Feb 26, 2006 at 10:36:23AM +0200, Andrus Moor wrote: > It is difficult to write standard-compliant code in Postgres. > There are a lot of constructs which have SQL equivalents but are still used > widely, even in samples in docs! > > For example, there are suggestions using ... > Bruce seems to attempt start this process trying implement > escape_string_warning in postgresql.conf . However, this is only very minor > step. Please clean Postgres. Please help. Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, 2006-02-26 at 12:08 -0500, Tom Lane wrote: > We'd consider removing these features if they were actually blocking > support of some spec-required behavior ... but since they don't, it's > quite unlikely they'll ever be removed. Right; there are plenty of places in which PostgreSQL extends the standard. If you're concerned about writing standard-compliant applications, merely removing the places where we have historical syntax variants is probably going to be of little help. I think a better approach would be to introduce the concept of "SQL dialects", similar to "--std=..." in GCC or SQL modes in MySQL 5. That would help people who want to write standard-compliant applications while not inconveniencing those who don't care. -Neil
On 2/26/06, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > Please help. how? is there any place where postgres' SQL:2003 incompatibilities are being discussed? I really want to have standard-compatible PostgreSQL and some option in postgresql.conf that would allow me to restrict Postgres' SQL syntax to standard. The suggestion 'to feel free and use only standard queries' is the bad thing, because: a. the papers of SQL:2003 are quite hard to understand, even for good specialist (the main part, #2 has more than 1300 pages!) b. what about novices? it's almost impossible to go the right way for them. PostgreSQL has very-very good documentation, but it teaches to go Pg's way, which is not right in that sense, unfortunately... Now we have a lot of incompatibilities. I would classify them: 1. 'Extending' features - things that offer the same abilities that standard constructions. Some of these things allow to use shorter syntax, but I really think that many of them are just 'heritage of the past'. Yes, standard is 'talkative', but I prefer only standard things, because it helps me to understand other databases and 'academical things'. Actually, I hate ':=', '::', 'INT2', etc, and really want to be able to deprecate them (via conf or something) 2. Features that are implemented in non-standard way (ot things that are not yet implemented but could be considered as basic...) The good examples are: ILIKE and lack of ability to set up collation (rules for string comparison); lack of NULLS FIRST / LAST construction and necessity to add additional ordering step to ORDER BY instead of that. 3. 'Ugly' things like DISTINCT ON expression [, ...] (see http://chernowiki.ru/index.php?node=38#A13) Maybe to create a sub-project (or special section in TODO) for improving SQL:2003 compatibility? I've encountered with many 'reefs' during migration from MS SQL to Postgres. Some of them are here: http://chernowiki.ru. I do think that such drawbacks complicate migration for other DBMSs' guys and understanding SQL for newbies. -- Best regards, Nikolay
Nikolay Samokhvalov wrote: > On 2/26/06, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > > >>Please help. > > how? 1. Pick an item on the list. 2. Join the -hackers list and ask for info on where to start and discuss what you want to change. 3. Start coding. -- Postgresql & php tutorials http://www.designmagick.com/
On Mon, Feb 27, 2006 at 00:25:57 +0300, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote: > On 2/26/06, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > > past'. Yes, standard is 'talkative', but I prefer only standard > things, because it helps me to understand other databases and > 'academical things'. Actually, I hate ':=', '::', 'INT2', etc, and > really want to be able to deprecate them (via conf or something) I find :: to be much more readable than cast(). > 3. 'Ugly' things like DISTINCT ON expression [, ...] (see > http://chernowiki.ru/index.php?node=38#A13) The alternatives to distinct on are painful. They are generally both harder to read and run slower.
On Mon, Feb 27, 2006 at 12:25:57AM +0300, Nikolay Samokhvalov wrote: > > Please help. > how? ... > PostgreSQL has very-very good documentation, but it teaches to > go Pg's way, which is not right in that sense, unfortunately... By supplying documentation patches, perhaps ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Am Sonntag, 26. Februar 2006 21:24 schrieb Neil Conway: > I think a better approach would be to introduce the concept of "SQL > dialects", similar to "--std=..." in GCC or SQL modes in MySQL 5. That > would help people who want to write standard-compliant applications > while not inconveniencing those who don't care. Such a thing has been discussed from time to time but in reality you wouldn't get useful results from it because just about any application will violate the standard somewhere. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 2/27/06, Peter Eisentraut <peter_e@gmx.net> wrote: > Am Sonntag, 26. Februar 2006 21:24 schrieb Neil Conway: > > I think a better approach would be to introduce the concept of "SQL > > dialects", similar to "--std=..." in GCC or SQL modes in MySQL 5. That > > would help people who want to write standard-compliant applications > > while not inconveniencing those who don't care. > > Such a thing has been discussed from time to time but in reality you wouldn't > get useful results from it because just about any application will violate > the standard somewhere. > so, maybe it's better to forget about SQL:2003 at all? please, remember that many people use Postgres for educational purposes. Aren't you afraid of that in the future these people will switch to MySQL because of ability to work in standard way?.. -- Best regards, Nikolay
On 2/27/06, Bruno Wolff III <bruno@wolff.to> wrote: > The alternatives to distinct on are painful. They are generally both harder > to read and run slower. > 'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it produses unpredictable result, as 'ORDER BY random()' does. When newbie types 'random()', he understands what he is doing, but it's not the case for 'DISTINCT ON' and can lead to mistakes. -- Best regards, Nikolay
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: > On 2/27/06, Bruno Wolff III <bruno@wolff.to> wrote: > The alternatives to distinct on are painful. They are generally both harder > to read and run slower. > >'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it >produses unpredictable result, as 'ORDER BY random()' does. And so does UNION in the standard under some circumstances (look at anywhere in the spec that a query expression is possibly non-deterministic), so I think that's a weak argument.
On 2/27/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: > > > On 2/27/06, Bruno Wolff III <bruno@wolff.to> wrote: > > The alternatives to distinct on are painful. They are generally both harder > > to read and run slower. > > > > >'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it > >produses unpredictable result, as 'ORDER BY random()' does. > > And so does UNION in the standard under some circumstances (look at > anywhere in the spec that a query expression is possibly > non-deterministic), so I think that's a weak argument. > it's completely different thing. look at the spec and you'll understand the difference. in two words, with 'DISTINCT ON' we lose some values (from some columns), when UNION not (it just removes duplicates, comparing _entire_ rows). -- Best regards, Nikolay
On Mon, Feb 27, 2006 at 06:26:02PM +0300, Nikolay Samokhvalov wrote: > On 2/27/06, Peter Eisentraut <peter_e@gmx.net> wrote: > > Such a thing has been discussed from time to time but in reality you wouldn't > > get useful results from it because just about any application will violate > > the standard somewhere. > > > so, maybe it's better to forget about SQL:2003 at all? > please, remember that many people use Postgres for educational > purposes. Aren't you afraid of that in the future these people will > switch to MySQL because of ability to work in standard way?.. Huh? We should ofcourse try to implement SQL:2003 wherever we can, but to say this means we need to throw out anything not mentioned is silly. For example, CREATE INDEX is not in SQL:2003, are you seriously suggesting we remove it? We implement many extensions to SQL like user-defined operators, aggregates and casts as well as tablespaces. They are all useful and work well and don't prevent us from supporting all of SQL:2003, so why remove them? Also, we are generally more standards compliant than MySQL so I'm not sure using them makes for a good argument. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On 2/27/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> Huh? We should ofcourse try to implement SQL:2003 wherever we can, but
> to say this means we need to throw out anything not mentioned is silly.
> For example, CREATE INDEX is not in SQL:2003, are you seriously
> suggesting we remove it?
i didn't suggest removing such things. I know that there are many
must-have things that standard misses (limit/offset for example), but
(as i wrote...) Postgres has stuff that just duplicate standard
constuctions (such as type casting with ::). It would very great if we
have ability to restrict (not remove) them somehow.
moreover, there are things that are implemented in non-standard way...
as ILIKE. I know, that work on COLLATE support is in progress (right?)
and it's very good, ILIKE is very painful thing for those who migrated
from other DBMS.
>
> We implement many extensions to SQL like user-defined operators,
> aggregates and casts as well as tablespaces. They are all useful and
> work well and don't prevent us from supporting all of SQL:2003, so why
> remove them?
Please, do not incriminate me all deadly sins :-) I know where is the
power of Postgres lies.
>
> Also, we are generally more standards compliant than MySQL so I'm not
> sure using them makes for a good argument.
Surely, MySQL is weak, but people work on it, money are being spent...
Among other things, ability to set up 'SQL mode' is one of
advertising tools which helps to fight with competitors.
--
Best regards,
Nikolay
On Mon, Feb 27, 2006 at 06:59:21PM +0300, Nikolay Samokhvalov wrote: > On 2/27/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > >'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it > > >produses unpredictable result, as 'ORDER BY random()' does. > > > > And so does UNION in the standard under some circumstances (look at > > anywhere in the spec that a query expression is possibly > > non-deterministic), so I think that's a weak argument. > > > it's completely different thing. look at the spec and you'll > understand the difference. in two words, with 'DISTINCT ON' we lose > some values (from some columns), when UNION not (it just removes > duplicates, comparing _entire_ rows). Wait, you're complaining because SQL lets you produce non-deterministic results? There are plenty or way to acheive this in standard SQL too. This statement: select pronargs, first( cast(prolang as integer) ) from pg_proc group by pronargs; Produces non-deterministic results also, just like DISTINCT ON (). Using LIMIT/OFFSET with an underspecified ORDER BY produces "unpredicatble" results. We provide the tools, but if people want aim them at their feet and blow them off, that's not something we can do anything about. If anything, it seems you're arguing for the removal of the random() function because it's non-deterministic. Hey, and sometimes I want a non-deterministic output. It's nice postgresql can give me that too... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: >it's completely different thing. look at the spec and you'll >understand the difference. in two words, with 'DISTINCT ON' we lose >some values (from some columns), when UNION not (it just removes >duplicates, comparing _entire_ rows). No it's not, really. Read the spec. The output of a union on a text field is non-deterministic (due to some collation choices). This means that the output of the query may be determined by an effectively random choice of which value to use. Basically AFAICT something like (modulo simple errors): select foo from ( select foo from tablea union select foo from tableb ) where foo = 'A' collate case_sensitive can give different results in the case of tablea having 'A' and tableb having 'a' if the union is using a case insensitive comparison.
On Mon, Feb 27, 2006 at 18:34:16 +0300, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote: > On 2/27/06, Bruno Wolff III <bruno@wolff.to> wrote: > > The alternatives to distinct on are painful. They are generally both harder > > to read and run slower. > > > > 'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it > produses unpredictable result, as 'ORDER BY random()' does. > > When newbie types 'random()', he understands what he is doing, but > it's not the case for 'DISTINCT ON' and can lead to mistakes. The documentation for DISTINCT ON is clear about this: DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate toequal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "firstrow" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example, I doubt the newbie understands random() as well as DISTINCT ON on the first go around. In some uses random() is evaluated per tuple and in others it isn't. In particular it probably isn't obvious to newbies what the semantics of the following is: SELECT a FROM b WHERE a > random();
On 02/26/2006-10:36AM, Andrus Moor wrote: > > It is difficult to write standard-compliant code in Postgres. > There are a lot of constructs which have SQL equivalents but are still used > widely, even in samples in docs! > > For example, there are suggestions using > > now()::CHAR!=foo > > while the correct way is > > CAST(CURRENT_DATE AS CHAR)<>foo > > now() function, :: and != operators should be removed from language. > > I like the Python logic: there is one way Actually the python logic is "There should be one-- and preferably only one --obvious way to do it." Perhaps the suggestion should be to only use the SQL standard forms in the documentation, making them the 'obvious' way.
> Aren't you afraid of that in the future these people will switch to MySQL because of ability to work in standard way?.. You're joking, right? At least I had a good laugh.