Re: To Postgres Devs : Wouldn't changing the select limit

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(at)candle(dot)pha(dot)pa(dot)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(at)gmx(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant

Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)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:

>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:

> Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)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(at)postgresql(dot)org)
>

Tom Lane wrote:

> Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)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(at)oli(dot)tudelft(dot)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(at)postgresql(dot)org)
> >
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)svana(dot)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(at)epilogue(dot)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(at)oli(dot)tudelft(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)epilogue(dot)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

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> 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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us> writes:
>> 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.

Wups, you're right, I was looking at the cvs-tip code not 7.1.
What was that about the order not being easy to remember? :-(

> 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.

Actually, it seems that the message should point out *both* the
old-Postgres and the MySQL translations. One camp or the other
is going to get burnt otherwise. Maybe:

ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
If translating pre-7.2 Postgres: LIMIT m,n => LIMIT m OFFSET n
If translating MySQL: LIMIT m,n => OFFSET m LIMIT n

regards, tom lane

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.

Tom Lane writes:

> ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
> If translating pre-7.2 Postgres: LIMIT m,n => LIMIT m OFFSET n
> If translating MySQL: LIMIT m,n => OFFSET m LIMIT n

I think someone is panicking here for no reason, but I'm not sure who it
is. If we think that LIMIT x,y should be phased out, then let's add that
to the documentation and remove it in a later release, so people have a
chance to prepare. But you're removing a perfectly fine feature that has
received no attention in the last two years on the last day before beta
because of a mysterious crowd of people porting from MySQL. Let me tell
you: People porting from MySQL are going to have a lot of other problems
before they find out that LIMIT works differently.

In addition I want to repeat my object to notices and errors that are
teaching syntax or trying to give smart tips. If a command is not
syntactically correct then it's a syntax error. If the command used to be
correct, might be correct in the future, or is correct in some other life
then it's still a syntax error. If we want to have a "tip mode" then
let's have one, but until that happens the documentation is the place to
explain error messages or give advice how to avoid them.

Now, of course this whole situation is a bit unfortunate because of the
syntax mixup. But let's remember that most people that are going to use
PostgreSQL 7.2 are the people that are using PostgreSQL 7.1 now, and
they're going to be a lot happier the less they're going to be annoyed by
gratuitous breaks in compatibility that had no prior notice at all.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> 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.
>
> Wups, you're right, I was looking at the cvs-tip code not 7.1.
> What was that about the order not being easy to remember? :-(

Confusing syntax proven!

> > 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.
>
> Actually, it seems that the message should point out *both* the
> old-Postgres and the MySQL translations. One camp or the other
> is going to get burnt otherwise. Maybe:
>
> ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
> If translating pre-7.2 Postgres: LIMIT m,n => LIMIT m OFFSET n
> If translating MySQL: LIMIT m,n => OFFSET m LIMIT n

I opted for a more generic message which makes clear the person it is
not a cut-and-past error message:

test=> select * from pg_class LIMIT 1,1;
ERROR: LIMIT #,# syntax no longer supported.
Use separate LIMIT and OFFSET clauses.

That should take care of it in a flexible way.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> Tom Lane writes:
>
> > ERROR: LIMIT #,# syntax no longer supported. Use LIMIT # OFFSET #.
> > If translating pre-7.2 Postgres: LIMIT m,n => LIMIT m OFFSET n
> > If translating MySQL: LIMIT m,n => OFFSET m LIMIT n
>
> I think someone is panicking here for no reason, but I'm not sure who it
> is. If we think that LIMIT x,y should be phased out, then let's add that
> to the documentation and remove it in a later release, so people have a

We took a vote on 'general' and had only one person who wanted it kept
for an additional release and several who wanted it removed right now.

> chance to prepare. But you're removing a perfectly fine feature that has

We are not removing the feature so much as forcing a syntax change on
user queries.

> received no attention in the last two years on the last day before beta

We have been _near_ beta for over one month now. It doesn't seem wise
to let this time just go to waste so I am trying to do what I can to
move PostgreSQL forward during this period.

The LIMIT #,# was actuall changed a almost a month ago in gram.y:

revision 2.253
date: 2001/09/23 03:39:01; author: momjian; state: Exp; lines: +3 -3
Implement TODO item:

* Change LIMIT val,val to offset,limit to match MySQL

This new activity is because someone asked about why the change was made
and the discussion on general led to this solution.

> because of a mysterious crowd of people porting from MySQL. Let me tell
> you: People porting from MySQL are going to have a lot of other problems
> before they find out that LIMIT works differently.

True.

> In addition I want to repeat my object to notices and errors that are
> teaching syntax or trying to give smart tips. If a command is not
> syntactically correct then it's a syntax error. If the command used to be
> correct, might be correct in the future, or is correct in some other life
> then it's still a syntax error. If we want to have a "tip mode" then
> let's have one, but until that happens the documentation is the place to
> explain error messages or give advice how to avoid them.

I disagree. If the 'tip' is localized to a few lines, usually in
gram.y, I don't see a reason not to help people find the right answer.
It helps them and reduces redundant bug repots. I can't imagine a
reason not to do it unless it starts to make our code more complex.

I don't want to jump through hoops to give people tips, but if it is
easy, let's do it.

> Now, of course this whole situation is a bit unfortunate because of the
> syntax mixup. But let's remember that most people that are going to use
> PostgreSQL 7.2 are the people that are using PostgreSQL 7.1 now, and
> they're going to be a lot happier the less they're going to be annoyed by
> gratuitous breaks in compatibility that had no prior notice at all.

Again, we took a vote on general. If there are people who want this
kept around for another release, we can do it. Let's hear from you.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> > > 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 #:

I've cc'd this to the hackers list. I know the discussion started on
general, but if I hadn't been subscribed to *that* list I'd have never
known about any of this. And noone else on hackers would either.

- Thomas

(switched thread to hackers)

> ... If the 'tip' is localized to a few lines, usually in
> gram.y, I don't see a reason not to help people find the right answer.
> It helps them and reduces redundant bug repots. I can't imagine a
> reason not to do it unless it starts to make our code more complex.

I'm with Peter on this one. I'd like to *not* clutter up the code and
error reporting with hints and suggestions which may or may not be to
the point.

We *should* have docs which list error messages and possible solutions,
and throwing that info into code is a poor second choice imho.

- Thomas

> > > > 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 #:
>
> I've cc'd this to the hackers list. I know the discussion started on
> general, but if I hadn't been subscribed to *that* list I'd have never
> known about any of this. And noone else on hackers would either.

The discussion has moved from patches to general so our general users
could comment on this.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> (switched thread to hackers)
>
> > ... If the 'tip' is localized to a few lines, usually in
> > gram.y, I don't see a reason not to help people find the right answer.
> > It helps them and reduces redundant bug repots. I can't imagine a
> > reason not to do it unless it starts to make our code more complex.
>
> I'm with Peter on this one. I'd like to *not* clutter up the code and
> error reporting with hints and suggestions which may or may not be to
> the point.
>
> We *should* have docs which list error messages and possible solutions,
> and throwing that info into code is a poor second choice imho.

Is it really clutter to add a clause and elog(). I am not advocating
adding stuff like crazy, but when we see people having the same problem,
it seems worth adding it. Our docs are pretty big and most people who
have this type of problem are not going to know where to look in the
docs. If the elog pointed them to the proper section in the docs, that
would be even better, but then again, you are doing the elog at that
point.

What do others think? It would be good to have a specific example to
discuss.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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:
>
> > (switched thread to hackers)
> >
> > > ... If the 'tip' is localized to a few lines, usually in
> > > gram.y, I don't see a reason not to help people find the right answer.
> > > It helps them and reduces redundant bug repots. I can't imagine a
> > > reason not to do it unless it starts to make our code more complex.
> >
> > I'm with Peter on this one. I'd like to *not* clutter up the code and
> > error reporting with hints and suggestions which may or may not be to
> > the point.
> >
> > We *should* have docs which list error messages and possible solutions,
> > and throwing that info into code is a poor second choice imho.
>
> Is it really clutter to add a clause and elog(). I am not advocating
> adding stuff like crazy, but when we see people having the same problem,
> it seems worth adding it. Our docs are pretty big and most people who
> have this type of problem are not going to know where to look in the
> docs. If the elog pointed them to the proper section in the docs, that
> would be even better, but then again, you are doing the elog at that
> point.
>
> What do others think? It would be good to have a specific example to
> discuss.

FWIW, Oracle has its "oerr" utility which takes the arguments:

oerr facility error-code

So the RDBMS generates an error code with a single line message less
than or equal to 76 characters in length, prefixed by the facility
and error code:

ORA-01034: ORACLE not available

The user can then get detailed information through the oerr utility.
It would be nice, when we have error codes (are they apart of the
new NLS support?), we have a "pgerr" utility to serve the same
purpose. And of course the message files shipped with Oracle contain
localized messages.

Example output:

$oerr ora 12203

12203, 00000, "TNS:unable to connect to destination"
// *Cause: Invalid TNS address supplied or destination is not
listening.
// This error can also occur because of underlying network transport
// problems.
// *Action: Verify that the service name you entered on the command
line
// was correct. Ensure that the listener is running at the remote
node and
// that the ADDRESS parameters specified in TNSNAMES.ORA are
correct.
// Finally, check that all Interchanges needed to make the
connection are
// up and running.

It would then be nice to have both a command-line version of the
PostgreSQL equivalent and a web-based version on postgresql.org for
users to use.

Just my 2 cents, of course,

Mike Mascari
mascarm(at)mascari(dot)com

Thomas Lockhart <lockhart(at)fourpalms(dot)org> writes:
> I'm with Peter on this one. I'd like to *not* clutter up the code and
> error reporting with hints and suggestions which may or may not be to
> the point.
> We *should* have docs which list error messages and possible solutions,
> and throwing that info into code is a poor second choice imho.

While you have a point in the abstract, a big difficulty is that the
docs never track the code with any accuracy. Look at the "Outputs"
portions of our existing reference pages. To the extent that they
describe possible errors at all, the information is a sad joke: out of
date in most cases, certainly incomplete in every case. Just last week
I was thinking that we should rip all that stuff out, rather than
pretend it is or ever will be accurate.

regards, tom lane

> Thomas Lockhart <lockhart(at)fourpalms(dot)org> writes:
> > I'm with Peter on this one. I'd like to *not* clutter up the code and
> > error reporting with hints and suggestions which may or may not be to
> > the point.
> > We *should* have docs which list error messages and possible solutions,
> > and throwing that info into code is a poor second choice imho.
>
> While you have a point in the abstract, a big difficulty is that the
> docs never track the code with any accuracy. Look at the "Outputs"
> portions of our existing reference pages. To the extent that they
> describe possible errors at all, the information is a sad joke: out of
> date in most cases, certainly incomplete in every case. Just last week
> I was thinking that we should rip all that stuff out, rather than
> pretend it is or ever will be accurate.

I recommend tips when they are one line in length, have a high
probability of being accurate, and are common mistakes. Anything longer
and we should point to a specific section in the docs.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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:
>

[snip]

>
> What do others think?

Please reverse your change and go into beta quickly.

regards,
Hiroshi Inoue

> Bruce Momjian wrote:
> >
>
> [snip]
>
> >
> > What do others think?
>
> Please reverse your change and go into beta quickly.

I need more information. What do you want reversed, and are there
enough votes to reverse those votes already made?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 wrote:
> > >
> >
> > [snip]
> >
> > >
> > > What do others think?
> >
> > Please reverse your change and go into beta quickly.
>
> I need more information. What do you want reversed,

revision 2.253
date: 2001/09/23 03:39:01; author: momjian; state: Exp; lines: +3 -3
Implement TODO item:

* Change LIMIT val,val to offset,limit to match MySQL

and the related description in HISTORY(Migration to 7.2).

> and are there
> enough votes to reverse those votes already made?

I don't think that enough votes are needed to reverse
the change. You broke the discussion fisrt rule.

regards,
Hiroshi Inoue

> > I need more information. What do you want reversed,
>
> revision 2.253
> date: 2001/09/23 03:39:01; author: momjian; state: Exp; lines: +3 -3
> Implement TODO item:
>
> * Change LIMIT val,val to offset,limit to match MySQL
>
> and the related description in HISTORY(Migration to 7.2).

>
> > and are there
> > enough votes to reverse those votes already made?
>
> I don't think that enough votes are needed to reverse
> the change. You broke the discussion fisrt rule.

It was on the TODO list, and I did exactly what was listed there. What
we have now is a discussion that the TODO item was wrong.

I also have very few votes to just put it back to how it was in 7.1. We
have votes for throwing a NOTICE that this syntax is going away, and
votes to remove it completely in 7.2. We also have few votes to merely
reverse the meaning of the numbers.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Good day,

My name is John Worsley, I'm one of the authors of the new O'Reilly
PostgrSQL book. We're wrapping up the PL/pgSQL chapter's technical edit
right now, but there are a couple of concerns that I was hoping someone
might be able to help with.

Mainly, the existing documentation on the RENAME statement seems
inaccurate; it states that you can re-name variables, records, or
rowtypes. However, in practice, our tests show that attempting to RENAME
valid variables with:

RENAME varname TO newname;

...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax
on a non-declared variable, it actually says "there is no variable" with
that name in the current block, so...I think something odd is happening. :)

I believe we have only gotten RENAME to work with either the NEW or OLD
record variables when using PL/pgSQL with triggers, but the documentation
suggests that this should be a general-purpose statement.

Any assistance would be greatly appreciated. :)

The RENAME statement seems kind of odd, since it seems that you could just
as easily declare a general variable with the right name to begin with,
and maybe that's why this isn't apparently documented anywhere else? I
just want to make sure the documentation is both accurate and complete.

Kind Regards,
Jw.
--
John Worsley, Command Prompt, Inc.
jlx(at)commandprompt(dot)com by way of pgsql-hackers(at)commandprompt(dot)com

"Command Prompt, Inc." <pgsql-hackers(at)commandprompt(dot)com> writes:
> Mainly, the existing documentation on the RENAME statement seems
> inaccurate; it states that you can re-name variables, records, or
> rowtypes. However, in practice, our tests show that attempting to RENAME
> valid variables with:
> RENAME varname TO newname;
> ...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax
> on a non-declared variable, it actually says "there is no variable" with
> that name in the current block, so...I think something odd is happening. :)

Yup, this is a bug. The plpgsql grammar expects varname to be a T_WORD,
but in fact the scanner will only return T_WORD for a name that is not
any known variable name. Thus RENAME cannot possibly work, and probably
never has worked.

Looks like it should accept T_VARIABLE, T_RECORD, T_ROW (at least).
T_WORD ought to draw "no such variable". Jan, I think this is your turf...

> The RENAME statement seems kind of odd, since it seems that you could just
> as easily declare a general variable with the right name to begin with,

It seems pretty useless to me too. Perhaps it's there because Oracle
has one?

regards, tom lane

Bruce Momjian writes:

> It was on the TODO list, and I did exactly what was listed there. What
> we have now is a discussion that the TODO item was wrong.

I don't consider the items on the TODO list to be past the "adequately
discussed" stage.

To the topic at hand: I find reversing the argument order is going to
silently break a lot of applications. Removing the syntax altogether
could be a reasonable choice, but since it doesn't hurt anyone right now
I'd prefer an advance notice for one release.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

> I recommend tips when they are one line in length, have a high
> probability of being accurate, and are common mistakes. Anything longer
> and we should point to a specific section in the docs.

I would put "when porting from MySQL" into that category.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

> Bruce Momjian writes:
>
> > I recommend tips when they are one line in length, have a high
> > probability of being accurate, and are common mistakes. Anything longer
> > and we should point to a specific section in the docs.
>
> I would put "when porting from MySQL" into that category.

I would too except when we implement the feature backwards and then
remove it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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:
>
> > It was on the TODO list, and I did exactly what was listed there. What
> > we have now is a discussion that the TODO item was wrong.
>
> I don't consider the items on the TODO list to be past the "adequately
> discussed" stage.
>
> To the topic at hand: I find reversing the argument order is going to
> silently break a lot of applications. Removing the syntax altogether
> could be a reasonable choice, but since it doesn't hurt anyone right now
> I'd prefer an advance notice for one release.
>

Which is what we are doing now.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Has this been addressed?

---------------------------------------------------------------------------

> "Command Prompt, Inc." <pgsql-hackers(at)commandprompt(dot)com> writes:
> > Mainly, the existing documentation on the RENAME statement seems
> > inaccurate; it states that you can re-name variables, records, or
> > rowtypes. However, in practice, our tests show that attempting to RENAME
> > valid variables with:
> > RENAME varname TO newname;
> > ...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax
> > on a non-declared variable, it actually says "there is no variable" with
> > that name in the current block, so...I think something odd is happening. :)
>
> Yup, this is a bug. The plpgsql grammar expects varname to be a T_WORD,
> but in fact the scanner will only return T_WORD for a name that is not
> any known variable name. Thus RENAME cannot possibly work, and probably
> never has worked.
>
> Looks like it should accept T_VARIABLE, T_RECORD, T_ROW (at least).
> T_WORD ought to draw "no such variable". Jan, I think this is your turf...
>
> > The RENAME statement seems kind of odd, since it seems that you could just
> > as easily declare a general variable with the right name to begin with,
>
> It seems pretty useless to me too. Perhaps it's there because Oracle
> has one?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us> writes:
> Has this been addressed?

No ... I punted in Jan's direction ...

regards, tom lane

Is this completed?

---------------------------------------------------------------------------

> "Command Prompt, Inc." <pgsql-hackers(at)commandprompt(dot)com> writes:
> > Mainly, the existing documentation on the RENAME statement seems
> > inaccurate; it states that you can re-name variables, records, or
> > rowtypes. However, in practice, our tests show that attempting to RENAME
> > valid variables with:
> > RENAME varname TO newname;
> > ...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax
> > on a non-declared variable, it actually says "there is no variable" with
> > that name in the current block, so...I think something odd is happening. :)
>
> Yup, this is a bug. The plpgsql grammar expects varname to be a T_WORD,
> but in fact the scanner will only return T_WORD for a name that is not
> any known variable name. Thus RENAME cannot possibly work, and probably
> never has worked.
>
> Looks like it should accept T_VARIABLE, T_RECORD, T_ROW (at least).
> T_WORD ought to draw "no such variable". Jan, I think this is your turf...
>
> > The RENAME statement seems kind of odd, since it seems that you could just
> > as easily declare a general variable with the right name to begin with,
>
> It seems pretty useless to me too. Perhaps it's there because Oracle
> has one?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Tom Lane wrote:
> "Command Prompt, Inc." <pgsql-hackers(at)commandprompt(dot)com> writes:
> > Mainly, the existing documentation on the RENAME statement seems
> > inaccurate; it states that you can re-name variables, records, or
> > rowtypes. However, in practice, our tests show that attempting to RENAME
> > valid variables with:
> > RENAME varname TO newname;
> > ...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax
> > on a non-declared variable, it actually says "there is no variable" with
> > that name in the current block, so...I think something odd is happening. :)
>
> Yup, this is a bug. The plpgsql grammar expects varname to be a T_WORD,
> but in fact the scanner will only return T_WORD for a name that is not
> any known variable name. Thus RENAME cannot possibly work, and probably
> never has worked.
>
> Looks like it should accept T_VARIABLE, T_RECORD, T_ROW (at least).
> T_WORD ought to draw "no such variable". Jan, I think this is your turf...

Sounds pretty much like that. Will take a look.

>
> > The RENAME statement seems kind of odd, since it seems that you could just
> > as easily declare a general variable with the right name to begin with,
>
> It seems pretty useless to me too. Perhaps it's there because Oracle
> has one?

And I don't even remember why I've put it in. Maybe because
it's an Oracle thing. This would be a cool fix, removing the
damned thing completely. I like that solution :-)

Anyone against removal?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com