To Postgres Devs : Wouldn't changing the select limit syntax ....
Break the SQL code that has been implemented for prior versions??
Bummer ;((.
Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian writes:
Break the SQL code that has been implemented for prior versions??
Bummer ;((.Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.
Isn't it much worse to not follow PostgreSQL behaviour than to not follow
MySQL behaviour?
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Bruce Momjian writes:
Break the SQL code that has been implemented for prior versions??
Bummer ;((.Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.Isn't it much worse to not follow PostgreSQL behaviour than to not follow
MySQL behaviour?
Well, it was on the TODO list and people complained while porting their
MySQL applications. We clearly made a mistake in the initial
implementation.
The question is do we fix it or continue with a different
implementation. Because we have the separate LIMIT and OFFSET we can
fix it while giving people a solution that will work for all versions.
If we don't fix it, all MySQL queries that are ported will be broken.
I assume it got on the TODO list because fixing it was the accepted
solution. We can, of course, change our minds.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Bruce Momjian writes:
Break the SQL code that has been implemented for prior versions??
Bummer ;((.Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.Isn't it much worse to not follow PostgreSQL behavior than to not follow
MySQL behavior?
Another idea: because our historical Limit #,# differs from MySQL, one
idea is to disable LIMIT #,# completely and instead print an error
stating they have to use LIMIT # OFFSET #. Although that would break
both MySQl and old PostgreSQL queries, it would not generate incorrect
results.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote:
Bruce Momjian writes:
Break the SQL code that has been implemented for prior versions??
Bummer ;((.Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.Isn't it much worse to not follow PostgreSQL behaviour than to not follow
MySQL behaviour?Well, it was on the TODO list and people complained while porting their
MySQL applications. We clearly made a mistake in the initial
implementation.The question is do we fix it or continue with a different
implementation. Because we have the separate LIMIT and OFFSET we can
fix it while giving people a solution that will work for all versions.
If we don't fix it, all MySQL queries that are ported will be broken.
But it seems absurd to trouble existent PG users instead.
regrads,
Hiroshi Inoue
Bruce Momjian wrote:
Bruce Momjian writes:
Break the SQL code that has been implemented for prior versions??
Bummer ;((.Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.Isn't it much worse to not follow PostgreSQL behavior than to not follow
MySQL behavior?Another idea: because our historical Limit #,# differs from MySQL, one
idea is to disable LIMIT #,# completely and instead print an error
stating they have to use LIMIT # OFFSET #. Although that would break
both MySQl and old PostgreSQL queries, it would not generate incorrect
results.
I would say the relevant behaviour is neither the one that MySQL
historically uses nor the one that PostgreSQL historically uses, but the
one that is specified in the relevant standards. Since nobody brought
this up yet I presume these standards leave the implementation of LIMIT
open (I tried to google myself, but I couldn't exactly find it).
Is that correct or does (any of the) the SQL standards specify a behaviour?
Jochem
Greetings, Bruce!
At 18.10.2001, 02:34, you wrote:
Isn't it much worse to not follow PostgreSQL behavior than to not follow
MySQL behavior?
BM> Another idea: because our historical Limit #,# differs from MySQL, one
BM> idea is to disable LIMIT #,# completely and instead print an error
BM> stating they have to use LIMIT # OFFSET #. Although that would break
BM> both MySQl and old PostgreSQL queries, it would not generate incorrect
BM> results.
It doesn't seem like a good idea. The best solution, IMHO, would
be to introduce optional "MySQL-compatibility mode" for LIMIT in 7.2
Later LIMIT #,# can be marked deprecated in favour of LIMIT #,
OFFSET #
But please, don't *break* things; while this change may make life
easier for some people migrating from MySQL far more people would
be pissed off...
--
Yours, Alexey V. Borzov, Webmaster of RDW.ru
Greetings, Bruce!
At 18.10.2001, 02:34, you wrote:
Isn't it much worse to not follow PostgreSQL behavior than to not follow
MySQL behavior?BM> Another idea: because our historical Limit #,# differs from MySQL, one
BM> idea is to disable LIMIT #,# completely and instead print an error
BM> stating they have to use LIMIT # OFFSET #. Although that would break
BM> both MySQl and old PostgreSQL queries, it would not generate incorrect
BM> results.It doesn't seem like a good idea. The best solution, IMHO, would
be to introduce optional "MySQL-compatibility mode" for LIMIT in 7.2
Later LIMIT #,# can be marked deprecated in favour of LIMIT #,
OFFSET #
But please, don't *break* things; while this change may make life
easier for some people migrating from MySQL far more people would
be pissed off...
OK, it seems enough people don't want this change that we have to do
something. What do people suggest? Can we throw an elog(NOTICE)
message in 7.2 stating that LIMIT #,# will disappear in the next release
and to start using LIMIT/OFFSET. That way, people can migrate their
code to LIMIT/OFFSET during 7.2 and it can disappear in 7.3?
I frankly think the LIMIT #,# is way too confusing anyway and would be
glad to have it removed.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Wed, 17 Oct 2001, Bruce Momjian wrote:
Bruce Momjian writes:
Break the SQL code that has been implemented for prior versions??
Bummer ;((.Yes, but we don't follow the MySQL behavior, which we copied when we
added LIMIT. Seems we should agree with their implementation.Isn't it much worse to not follow PostgreSQL behaviour than to not follow
MySQL behaviour?Well, it was on the TODO list and people complained while porting their
MySQL applications. We clearly made a mistake in the initial
implementation.The question is do we fix it or continue with a different
implementation. Because we have the separate LIMIT and OFFSET we can
fix it while giving people a solution that will work for all versions.
If we don't fix it, all MySQL queries that are ported will be broken.I assume it got on the TODO list because fixing it was the accepted
solution. We can, of course, change our minds.
Changing PG to match MySQL may rankle loyalists' feathers a bit,
but if we can relativeless painless make it easy to port from MySQL
to PG, it's a win.
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
I would say the relevant behaviour is neither the one that MySQL
historically uses nor the one that PostgreSQL historically uses, but the
one that is specified in the relevant standards.
There aren't any: SQL92 and SQL99 have no such feature. (Although I
notice that they list LIMIT as a word likely to become reserved in
future versions.)
AFAIK we copied the idea and the syntax from MySQL ... but we got the
order of the parameters wrong.
IMHO "LIMIT n OFFSET n" is far more readable than "LIMIT m,n" anyway.
(Quick: which number is first in the comma version? By what reasoning
could you deduce that if you'd forgotten?) So I think we should
deprecate and eventually eliminate the comma version, if we're not
going to conform to the de facto standard for it.
regards, tom lane
I know the differences between VACUUM and VACUUM ANALYZE have been discussed
before, but I'd like to know how you schedule your cleaning jobs. Right now
I do a
VACUUM
VACUUM ANALYZE
every hour... it takes about 3 minutes to run both. Should I run ANALYZE
less often?
Mark
Epilogue.net
I think that's a grand idea. Mysql does a lot of things in an 'odd' way
and I prefer the unambiguous LIMIT .. OFFSET form, it follows the design
of SQL in general.
-d
Bruce Momjian wrote:
Show quoted text
OK, it seems enough people don't want this change that we have to do
something. What do people suggest? Can we throw an elog(NOTICE)
message in 7.2 stating that LIMIT #,# will disappear in the next release
and to start using LIMIT/OFFSET. That way, people can migrate their
code to LIMIT/OFFSET during 7.2 and it can disappear in 7.3?I frankly think the LIMIT #,# is way too confusing anyway and would be
glad to have it removed.
As a user of both MySQL and PostgreSQL I can say that I would *love* it if
you went with "LIMIT n OFFSET m" instead of "LIMIT m,n". *every* time I
use the offset feature I have to look it up in the manual or some other
code snippet that has it (and where it's clear).
Even it broke some script I'd written it's pretty easy to find and fix
it...
just my 2 cents...
On Thu, 18 Oct 2001, Tom Lane wrote:
Show quoted text
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
I would say the relevant behaviour is neither the one that MySQL
historically uses nor the one that PostgreSQL historically uses, but the
one that is specified in the relevant standards.There aren't any: SQL92 and SQL99 have no such feature. (Although I
notice that they list LIMIT as a word likely to become reserved in
future versions.)AFAIK we copied the idea and the syntax from MySQL ... but we got the
order of the parameters wrong.IMHO "LIMIT n OFFSET n" is far more readable than "LIMIT m,n" anyway.
(Quick: which number is first in the comma version? By what reasoning
could you deduce that if you'd forgotten?) So I think we should
deprecate and eventually eliminate the comma version, if we're not
going to conform to the de facto standard for it.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Tom Lane wrote:
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
I would say the relevant behaviour is neither the one that MySQL
historically uses nor the one that PostgreSQL historically uses, but the
one that is specified in the relevant standards.There aren't any: SQL92 and SQL99 have no such feature. (Although I
notice that they list LIMIT as a word likely to become reserved in
future versions.)
But according to the list in the PostgreSQL docs OFFSET is not a
reserved word. Is it one of the 'likely to become reserved' words?
IMHO "LIMIT n OFFSET n" is far more readable than "LIMIT m,n" anyway.
(Quick: which number is first in the comma version? By what reasoning
could you deduce that if you'd forgotten?) So I think we should
deprecate and eventually eliminate the comma version, if we're not
going to conform to the de facto standard for it.
I agree that LIMIT n OFFSET n is by far the most readable format, and is
therefore the desirable format. But I am not sure about deprecating and
eliminating the other syntax. Above all it should be avoided that it is
now deprecated but is included in the next SQL standard and has to be
added again.
For now, I abstain.
Jochem
OK, I see several votes that say remove LIMIT #,# now, in 7.2 and throw
an error telling them to use LIMIT # OFFSET #.
The only other option is to throw a NOTICE that LIMIT #,# will go away
in 7.3.
Unless I hear otherwise, I will assume people prefer the first option.
---------------------------------------------------------------------------
As a user of both MySQL and PostgreSQL I can say that I would *love* it if
you went with "LIMIT n OFFSET m" instead of "LIMIT m,n". *every* time I
use the offset feature I have to look it up in the manual or some other
code snippet that has it (and where it's clear).Even it broke some script I'd written it's pretty easy to find and fix
it...just my 2 cents...
On Thu, 18 Oct 2001, Tom Lane wrote:
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
I would say the relevant behaviour is neither the one that MySQL
historically uses nor the one that PostgreSQL historically uses, but the
one that is specified in the relevant standards.There aren't any: SQL92 and SQL99 have no such feature. (Although I
notice that they list LIMIT as a word likely to become reserved in
future versions.)AFAIK we copied the idea and the syntax from MySQL ... but we got the
order of the parameters wrong.IMHO "LIMIT n OFFSET n" is far more readable than "LIMIT m,n" anyway.
(Quick: which number is first in the comma version? By what reasoning
could you deduce that if you'd forgotten?) So I think we should
deprecate and eventually eliminate the comma version, if we're not
going to conform to the de facto standard for it.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
LIMIT m OFFSET m *is* there now..
There is a LIMIT m,n syntax too I guess, though it appears that it's
backwards from MySQL..
I don't see much point in having two different ways of doing the same
thing unless you wanted to maintain compatibility with another RDBMS - but
that doesn't appear to be the case here (isn't that reversed from the MySQL
implementation?).. However, removing it now is going to break people's SQL..
I didn't know you could LIMIT m,n until today so I wouldn't have a clue as
to how many people actually use that syntax. Perhaps the idea of tossing a
notice up that that syntax is going away in the next release would be a
better idea than just yanking it out right away - then we can see how many
people complain :-)
-Mitch
Show quoted text
As a user of both MySQL and PostgreSQL I can say that I would *love* it if
you went with "LIMIT n OFFSET m" instead of "LIMIT m,n". *every* time I
use the offset feature I have to look it up in the manual or some other
code snippet that has it (and where it's clear).Even it broke some script I'd written it's pretty easy to find and fix
it...
On Thu, Oct 18, 2001 at 02:33:06PM -0400, Mark Coffman wrote:
I know the differences between VACUUM and VACUUM ANALYZE have been discussed
before, but I'd like to know how you schedule your cleaning jobs. Right now
I do aVACUUM
VACUUM ANALYZE
vacuum analyze does a vacuum anyway, so you don't need both.
every hour... it takes about 3 minutes to run both. Should I run ANALYZE
less often?
Here we do it once per day, though after a major set of updates i run it
manually. We're not under heavy load though.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
"Mark Coffman" <mark@epilogue.net> writes:
I know the differences between VACUUM and VACUUM ANALYZE have been discussed
before, but I'd like to know how you schedule your cleaning jobs. Right now
I do aVACUUM
VACUUM ANALYZEevery hour... it takes about 3 minutes to run both. Should I run ANALYZE
less often?
ANALYZE includes regular VACUUM functionality, so you don't have to do
both. So you're probably down to 2 minutes now. ;)
There's nothing wrong with running every hour--it depends on the
size and activity level of your DB.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Import Notes
Reply to msg id not found: MarkCoffmansmessageofThu18Oct2001143306-0400
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
Tom Lane wrote:
There aren't any: SQL92 and SQL99 have no such feature. (Although I
notice that they list LIMIT as a word likely to become reserved in
future versions.)
But according to the list in the PostgreSQL docs OFFSET is not a
reserved word. Is it one of the 'likely to become reserved' words?
Nope, it's not listed. There's no guarantee that their intended use
is the same as ours, anyway, so I don't put any stock in this as a
reason to make a decision now. It was just an observation in passing.
regards, tom lane
