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/
Show quoted text
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.
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/
Show quoted text
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.
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 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();
