Thread: To Postgres Devs : Wouldn't changing the select limit syntax ....
To Postgres Devs : Wouldn't changing the select limit syntax ....
From
huongch@bigfoot.com (Flancer)
Date:
Break the SQL code that has been implemented for prior versions?? Bummer ;((.
> 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 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 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
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) >
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
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: >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. >
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
> 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 a > > VACUUM > 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/ > 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 a > > VACUUM > VACUUM ANALYZE > > every 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
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
> 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. I am confused. While LIMIT and OFFSET may are potential SQL standard reserved words, I don't see how LIMIT #,# would ever be a standard specification. Do you see this somewhere I am missing. Again, LIMIT #,# is the only syntax we are removing. -- 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
"Mark Coffman" <mark@epilogue.net> writes:
> I do a
> VACUUM
> VACUUM ANALYZE
> every hour... it takes about 3 minutes to run both. Should I run ANALYZE
> less often?
VACUUM ANALYZE is a superset of VACUUM; there's certainly no reason to
do both one after the other.
As to whether you should do plain VACUUM some hours and VACUUM ANALYZE
others, that depends --- how fast are the statistics of your data
changing? If the stats (such as column minimum and maximum values) are
relatively stable, you could get away with fewer ANALYZEs. Maybe do one
ANALYZE every night at an off-peak time, and just plain VACUUM the rest
of the day.
regards, tom lane
Bruce Momjian wrote: >> >>>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. > > I am confused. While LIMIT and OFFSET may are potential SQL standard > reserved words, I don't see how LIMIT #,# would ever be a standard > specification. Do you see this somewhere I am missing. Again, LIMIT > #,# is the only syntax we are removing. If you are confident that LIMIT #,# would never be an official SQL standard who am I to second guess that ;) I don't see that possibility anywhere either, but I just wanted to make sure. The possibility that it might become an official standard is the only objection I had against deprecating and eventual elimination of that syntax. LIMIT # OFFSET # has my vote. Jochem
> If you are confident that LIMIT #,# would never be an official SQL
> standard who am I to second guess that ;) I don't see that possibility
> anywhere either, but I just wanted to make sure. The possibility that it
> might become an official standard is the only objection I had against
> deprecating and eventual elimination of that syntax.
>
> LIMIT # OFFSET # has my vote.
One more thing. I have added the code to suggest alternate syntax for
LIMIT #,#:
test=> select * from pg_class LIMIT 1,1;
ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
If there are other queries that use syntax that frequently fails, I
would like to hear about it so we can generate a helpful error message
rather than just a generic syntax error.
--
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:
>
> >>
> >>>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.
> >
> > I am confused. While LIMIT and OFFSET may are potential SQL standard
> > reserved words, I don't see how LIMIT #,# would ever be a standard
> > specification. Do you see this somewhere I am missing. Again, LIMIT
> > #,# is the only syntax we are removing.
>
>
> If you are confident that LIMIT #,# would never be an official SQL
> standard who am I to second guess that ;) I don't see that possibility
> anywhere either, but I just wanted to make sure. The possibility that it
> might become an official standard is the only objection I had against
> deprecating and eventual elimination of that syntax.
>
> LIMIT # OFFSET # has my vote.
OK, we have received only one vote to keep LIMIT #,# working for one
more release, and several to remove it so I am committing a patch now to
remove LIMIT #,# and instead have them use LIMIT # OFFSET #:
test=> select * from pg_class LIMIT 1,1;
ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
This message will not be removed in later releases because people
porting from MySQL will need to have it there even after our users have
ported their queries.
--
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 <pgman@candle.pha.pa.us> writes:
> One more thing. I have added the code to suggest alternate syntax for
> LIMIT #,#:
> test=> select * from pg_class LIMIT 1,1;
> ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
If you're going to do that, *please* suggest the *correct* substitution.
AFAICT, our version of LIMIT m,n transposes to OFFSET m LIMIT n; but
your message suggests the opposite.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > One more thing. I have added the code to suggest alternate syntax for
> > LIMIT #,#:
>
> > test=> select * from pg_class LIMIT 1,1;
> > ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
>
> If you're going to do that, *please* suggest the *correct* substitution.
> AFAICT, our version of LIMIT m,n transposes to OFFSET m LIMIT n; but
> your message suggests the opposite.
Remember, the 7.1 code was:
! select_limit: LIMIT select_limit_value ',' select_offset_value
! { $$ = makeList2($4, $2); }
This was changed a few weeks ago to match MySQL, and only today removed.
However, our new message suggests the old PostgreSQL syntax, not the
MySQL syntax. Optimally we should ship with this ordering for 7.2 and
reverse it for 7.3 or 7.4.
--
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
I think it is a better idea to yank it out now then rather later on.. cos either way our SQL codes gonna get broken.. sooner or later.. it won't make much difference now or later.