Thread: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Magnus Enbom
Date:
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


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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
 


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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

Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Jan Wieck
Date:
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 #


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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
 


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

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


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Magnus Enbom
Date:
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


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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
 


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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
 


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

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


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Larry Rosenman
Date:
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



Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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
 


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
"Christopher Kings-Lynne"
Date:
> 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

From
"Gaetano Mendola"
Date:
unsubscribe



Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Vivek Khera
Date:
>>>>> "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/


Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

From
Bruce Momjian
Date:
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