Thread: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
Hi, I've just been hit by a small but annoying difference between postgres(7.2) and mysql(4.x). In postgresql you do: SELECT * FROM table FOR UPDATE LIMIT 1; and in mysql you do: SELECT * FROM table LIMIT 1 FOR UPDATE; Is it possible for postgres to accept the mysql syntax as well? It's not that many databases that implement LIMIT, so it would be nice if the ones that do have the same syntax(or can accept each others variants). -- Magnus
Magnus Enbom wrote:
> Hi,
>
> I've just been hit by a small but annoying difference between postgres(7.2)
> and mysql(4.x).
> In postgresql you do:
>
> SELECT * FROM table FOR UPDATE LIMIT 1;
>
> and in mysql you do:
>
> SELECT * FROM table LIMIT 1 FOR UPDATE;
>
> Is it possible for postgres to accept the mysql syntax as well?
> It's not that many databases that implement LIMIT, so it would be nice if the
> ones that do have the same syntax(or can accept each others variants).
gram.y has the ordering listed below. We could add extra productions to
allow the ordering to vary. However, that will introduce a shift/reduce
conflict. It is amazing that we got LIMIT/OFFSET backwards, and not
UPDATE/LIMIT is backwards too, at least in relation to MySQL.
Ideas?
---------------------------------------------------------------------------
select_no_parens: simple_select { $$ = $1; } | select_clause sort_clause
opt_for_update_clauseopt_select_limit { insertSelectOptions((SelectStmt *) $1, $2, $3,
nth(0, $4), nth(1, $4)); $$ = $1; } | select_clause
for_update_clauseopt_select_limit { insertSelectOptions((SelectStmt *) $1, NIL, $2,
nth(0, $3), nth(1, $3)); $$ = $1; } | select_clause select_limit
{ insertSelectOptions((SelectStmt *) $1, NIL, NIL, nth(0, $2),
nth(1,$2)); $$ = $1; }
-- 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,
Pennsylvania19026
I found this email from April. It properly points out that our
LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more
correct, specifically that the FOR UPDATE is after the LIMIT. Our
grammar is:
| select_clause sort_clause opt_for_update_clause opt_select_limit
How do we want to deal with this? I tried allowing both orderings with
the attached patch but got:
bison -y -d gram.y
conflicts: 4 shift/reduce, 5 reduce/reduce
---------------------------------------------------------------------------
Magnus Enbom wrote:
> Hi,
>
> I've just been hit by a small but annoying difference between postgres(7.2)
> and mysql(4.x).
> In postgresql you do:
>
> SELECT * FROM table FOR UPDATE LIMIT 1;
>
> and in mysql you do:
>
> SELECT * FROM table LIMIT 1 FOR UPDATE;
>
> Is it possible for postgres to accept the mysql syntax as well?
> It's not that many databases that implement LIMIT, so it would be nice if the
> ones that do have the same syntax(or can accept each others variants).
>
> -- Magnus
>
> ---------------------------(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) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.360
diff -c -r2.360 gram.y
*** gram.y 19 Aug 2002 15:08:47 -0000 2.360
--- gram.y 26 Aug 2002 00:29:24 -0000
***************
*** 4114,4123 ****
--- 4114,4135 ----
nth(0, $4), nth(1, $4));
$$ = $1;
}
+ | select_clause sort_clause opt_select_limit opt_for_update_clause
+ {
+ insertSelectOptions((SelectStmt *) $1, $2, $4,
+ nth(0, $3), nth(1, $3));
+ $$ = $1;
+ }
| select_clause for_update_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $1, NIL, $2,
nth(0, $3), nth(1, $3));
+ $$ = $1;
+ }
+ | select_clause opt_select_limit for_update_clause
+ {
+ insertSelectOptions((SelectStmt *) $1, NIL, $3,
+ nth(0, $2), nth(1, $2));
$$ = $1;
}
| select_clause select_limit
Bruce Momjian wrote: > > I found this email from April. It properly points out that our > LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more > correct, specifically that the FOR UPDATE is after the LIMIT. Our > grammar is: How do you define "correct" for "non-standard" features? And why don't you ask Monty first to change to our "de-facto-standard"? ;-) Jan > > | select_clause sort_clause opt_for_update_clause opt_select_limit > > How do we want to deal with this? I tried allowing both orderings with > the attached patch but got: > > bison -y -d gram.y > conflicts: 4 shift/reduce, 5 reduce/reduce > > --------------------------------------------------------------------------- > > Magnus Enbom wrote: > > Hi, > > > > I've just been hit by a small but annoying difference between postgres(7.2) > > and mysql(4.x). > > In postgresql you do: > > > > SELECT * FROM table FOR UPDATE LIMIT 1; > > > > and in mysql you do: > > > > SELECT * FROM table LIMIT 1 FOR UPDATE; > > > > Is it possible for postgres to accept the mysql syntax as well? > > It's not that many databases that implement LIMIT, so it would be nice if the > > ones that do have the same syntax(or can accept each others variants). > > > > -- Magnus > > > > ---------------------------(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) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ------------------------------------------------------------------------ > Name: /bjm/diff > /bjm/diff Type: Plain Text (text/plain) > Encoding: 7bit > > Part 1.3 Type: Plain Text (text/plain) > Encoding: 8bit -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Bruce Momjian wrote: > > > > I found this email from April. It properly points out that our > > LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more > > correct, specifically that the FOR UPDATE is after the LIMIT. Our > > grammar is: > > How do you define "correct" for "non-standard" features? And why don't > you ask Monty first to change to our "de-facto-standard"? ;-) Well, MySQL created LIMIT, so they have the right to define the standard. I think FOR UPDATE looks more correct at the end because it controls the visibility of the returned result, while LIMIT and the other previous clauses control the result. FOR UPDATE clearly has a different effect than LIMIT, GROUP BY, WHERE, and the other previous clauses, so it makes more sense to me to have it at the end. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think FOR UPDATE looks more correct at the end because it
> controls the visibility of the returned result, while LIMIT and the
> other previous clauses control the result. FOR UPDATE clearly has a
> different effect than LIMIT, GROUP BY, WHERE, and the other previous
> clauses, so it makes more sense to me to have it at the end.
In the current implementation, FOR UPDATE acts after LIMIT does, so
putting it last would make sense --- SQL's optional clauses for SELECT
generally act left-to-right.
regards, tom lane
On Mon, Aug 26, 2002 at 02:42:26PM -0400, Jan Wieck wrote: > Bruce Momjian wrote: > > > > I found this email from April. It properly points out that our > > LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more > > correct, specifically that the FOR UPDATE is after the LIMIT. Our > > grammar is: > > How do you define "correct" for "non-standard" features? And why don't > you ask Monty first to change to our "de-facto-standard"? ;-) Already done that. ;-) He said he would look into it(having MySQL accept both behaviors), but if it would require a big change of their grammar(for a value of big), he'd rather not. He also pointed out(as Bruce and Tom have done) that our(PG) way is kind of backwards. If you look at Oracle, you can see that they also have it last: select :== subquery -> for_update_clause ; OTOH, Oracle doesn't have LIMIT, but that's another story... -- Magnus
Magnus Enbom wrote: > On Mon, Aug 26, 2002 at 02:42:26PM -0400, Jan Wieck wrote: > > Bruce Momjian wrote: > > > > > > I found this email from April. It properly points out that our > > > LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more > > > correct, specifically that the FOR UPDATE is after the LIMIT. Our > > > grammar is: > > > > How do you define "correct" for "non-standard" features? And why don't > > you ask Monty first to change to our "de-facto-standard"? ;-) > > Already done that. ;-) > He said he would look into it(having MySQL accept both behaviors), but if > it would require a big change of their grammar(for a value of big), he'd rather > not. He also pointed out(as Bruce and Tom have done) that our(PG) way is > kind of backwards. > If you look at Oracle, you can see that they also have it last: > > select :== subquery -> for_update_clause ; > > OTOH, Oracle doesn't have LIMIT, but that's another story... > Yep, we clearly have it backwards. Now, how to address it: 1) leave it unchanged2) allow only new ordering3) allow both orderings for one release4) allow both ordering forever -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
OK, no one has commented on this, so I guess I am going to have to guess the group's preference. My guess, seeing as very few probably use LIMIT and FOR UPDATE together, is to swap them and document it in the release notes. Was I correct in my guess? --------------------------------------------------------------------------- Bruce Momjian wrote: > Magnus Enbom wrote: > > On Mon, Aug 26, 2002 at 02:42:26PM -0400, Jan Wieck wrote: > > > Bruce Momjian wrote: > > > > > > > > I found this email from April. It properly points out that our > > > > LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more > > > > correct, specifically that the FOR UPDATE is after the LIMIT. Our > > > > grammar is: > > > > > > How do you define "correct" for "non-standard" features? And why don't > > > you ask Monty first to change to our "de-facto-standard"? ;-) > > > > Already done that. ;-) > > He said he would look into it(having MySQL accept both behaviors), but if > > it would require a big change of their grammar(for a value of big), he'd rather > > not. He also pointed out(as Bruce and Tom have done) that our(PG) way is > > kind of backwards. > > If you look at Oracle, you can see that they also have it last: > > > > select :== subquery -> for_update_clause ; > > > > OTOH, Oracle doesn't have LIMIT, but that's another story... > > > > Yep, we clearly have it backwards. Now, how to address it: > > 1) leave it unchanged > 2) allow only new ordering > 3) allow both orderings for one release > 4) allow both ordering forever > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> My guess, seeing as very few probably use LIMIT and FOR UPDATE together,
> is to swap them and document it in the release notes.
That will surely piss someone off. Can't you try a little harder to
support either order?
regards, tom lane
On Tue, 2002-08-27 at 17:07, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > > is to swap them and document it in the release notes. > > That will surely piss someone off. Can't you try a little harder to > support either order? If you change this you break me. I do this **A LOT** in the IP address allocation system I wrote. PLEASE DO NOT BREAK EXISTING APPS WITHOUT AT LEAST ONE RELEASE CYCLE'S WARNING, and preferably NOT AT ALL. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > > is to swap them and document it in the release notes. > > That will surely piss someone off. Can't you try a little harder to > support either order? Sure. I just needed someone to say they want it before doing the work. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> OK, no one has commented on this, so I guess I am going to have to guess > the group's preference. > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > is to swap them and document it in the release notes. Was I correct in > my guess? I'm sure very few people do it - but are you sure you can't just allow both syntaxes? Chris
unsubscribe
>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes: BM> OK, no one has commented on this, so I guess I am going to have to guess BM> the group's preference. BM> My guess, seeing as very few probably use LIMIT and FOR UPDATE together, BM> is to swap them and document it in the release notes. Was I correct in BM> my guess? My preference is to allow both orders for one release, then only allow the "correct" order in the next. be sure to absolutely make this a big red notice in the changelog. I just scanned my main app and found two instances where I use FOR UPDATE LIMIT 1. These are trivial to change, but difficult to do at the same moment I update the db server. One of these I probably don't even need the LIMIT... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
7.3, due out in a few months, will support both clause orderings. --------------------------------------------------------------------------- Magnus Enbom wrote: > Hi, > > I've just been hit by a small but annoying difference between postgres(7.2) > and mysql(4.x). > In postgresql you do: > > SELECT * FROM table FOR UPDATE LIMIT 1; > > and in mysql you do: > > SELECT * FROM table LIMIT 1 FOR UPDATE; > > Is it possible for postgres to accept the mysql syntax as well? > It's not that many databases that implement LIMIT, so it would be nice if the > ones that do have the same syntax(or can accept each others variants). > > -- Magnus > > ---------------------------(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) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073