Re: [GENERAL] Wish: remove ancient constructs from Postgres
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On 2/27/06, Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On 2/27/06, Bruno Wolff III [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: On 2/27/06, Bruno Wolff III [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On 2/27/06, Stephan Szabo [EMAIL PROTECTED] wrote: On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: On 2/27/06, Bruno Wolff III [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, Feb 27, 2006 at 06:26:02PM +0300, Nikolay Samokhvalov wrote: On 2/27/06, Peter Eisentraut [EMAIL PROTECTED] 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. signature.asc Description: Digital signature
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, Feb 27, 2006 at 06:59:21PM +0300, Nikolay Samokhvalov wrote: On 2/27/06, Stephan Szabo [EMAIL PROTECTED] 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. signature.asc Description: Digital signature
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, Feb 27, 2006 at 18:34:16 +0300, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: On 2/27/06, Bruno Wolff III [EMAIL PROTECTED] 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 to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the first row 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(); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Wish: remove ancient constructs from Postgres
Andrus Moor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Wish: remove ancient constructs from Postgres
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On 2/26/06, Karsten Hilbert [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Wish: remove ancient constructs from Postgres
Nikolay Samokhvalov wrote: On 2/26/06, Karsten Hilbert [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, Feb 27, 2006 at 00:25:57 +0300, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: On 2/26/06, Karsten Hilbert [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
