LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?

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_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
nth(0, $4), nth(1, $4));
$$ = $1;
}
| select_clause for_update_clause opt_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, Pennsylvania 19026

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

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, Pennsylvania 19073

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

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, Pennsylvania 19073

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, Pennsylvania 19073

Bruce Momjian dijo:

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?

Is it possible to support both ways for a couple of releases? Mention
the backwards one as "deprecated" in release notes, and drop it in 7.4.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)

Alvaro Herrera wrote:

Bruce Momjian dijo:

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?

Is it possible to support both ways for a couple of releases? Mention
the backwards one as "deprecated" in release notes, and drop it in 7.4.

Yes, it is possible, but the grammar will have to be a little tricky, if
that's OK with everyone.

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

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, Pennsylvania 19073

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

OK, patch attached. It was actually easier than I thought. We have to
decide if we are going to remove the old syntax in 7.4.

Regression tests pass. No doc updates yet.

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

Christopher Kings-Lynne wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, patch attached. It was actually easier than I thought. We have to
decide if we are going to remove the old syntax in 7.4.

I'd say "no". There's no compelling reason to break backward
compatibility here --- certainly a couple more productions in gram.y
isn't enough reason.

But I think it'd be sufficient to document only the new syntax.

regards, tom lane

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?

Sounds good to me.

Andreas

unsubscribe