Thread: Wish: remove ancient constructs from Postgres

Wish: remove ancient constructs from Postgres

From
"Andrus Moor"
Date:
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.




Re: Wish: remove ancient constructs from Postgres

From
"Uwe C. Schroeder"
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Tom Lane
Date:
"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

Re: Wish: remove ancient constructs from Postgres

From
Karsten Hilbert
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Neil Conway
Date:
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



Re: Wish: remove ancient constructs from Postgres

From
"Nikolay Samokhvalov"
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Chris
Date:
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/

Re: Wish: remove ancient constructs from Postgres

From
Bruno Wolff III
Date:
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.

Re: Wish: remove ancient constructs from Postgres

From
Karsten Hilbert
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Peter Eisentraut
Date:
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/

Re: Wish: remove ancient constructs from Postgres

From
"Nikolay Samokhvalov"
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
"Nikolay Samokhvalov"
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Stephan Szabo
Date:
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.

Re: Wish: remove ancient constructs from Postgres

From
"Nikolay Samokhvalov"
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Martijn van Oosterhout
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
"Nikolay Samokhvalov"
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Martijn van Oosterhout
Date:
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

Re: Wish: remove ancient constructs from Postgres

From
Stephan Szabo
Date:
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.


Re: Wish: remove ancient constructs from Postgres

From
Bruno Wolff III
Date:
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();

Re: Wish: remove ancient constructs from Postgres

From
Christopher Weimann
Date:
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.


Re: Wish: remove ancient constructs from Postgres

From
"Jonathan Gardner"
Date:
> 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.