Re: [SQL] 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_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(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 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(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org)
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)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(at)Yahoo(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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

unsubscribe

On Tue, Aug 27, 2002 at 10:45:24PM -0400, Bruce Momjian wrote:
>
> 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.

Thanks alot! I don't mind keeping the old syntax.

-- M

On Tue, 2002-08-27 at 23:29, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.
I agree here. Why intentionally break something that doesn't violate
standards, and would cause people to have to look at all their queries.
I personally hope y'all do *NOT* remove the old syntax.
>
> But I think it'd be sufficient to document only the new syntax.
Why? If both old and new are acceptable, why not document it?
(Just curious, I'm not wedded to it).

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry Rosenman wrote:
> On Tue, 2002-08-27 at 23:29, Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.
> I agree here. Why intentionally break something that doesn't violate
> standards, and would cause people to have to look at all their queries.
> I personally hope y'all do *NOT* remove the old syntax.
> >
> > But I think it'd be sufficient to document only the new syntax.

> Why? If both old and new are acceptable, why not document it?
> (Just curious, I'm not wedded to it).

Well, showing both versions adds confusion for no good reason, it
doesn't promote one over the other, and if we decide to get rid of the
old syntax someday, we can't do it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 2002-08-28 at 08:52, Bruce Momjian wrote:
> Larry Rosenman wrote:
> > On Tue, 2002-08-27 at 23:29, Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.
> > I agree here. Why intentionally break something that doesn't violate
> > standards, and would cause people to have to look at all their queries.
> > I personally hope y'all do *NOT* remove the old syntax.
> > >
> > > But I think it'd be sufficient to document only the new syntax.
>
> > Why? If both old and new are acceptable, why not document it?
> > (Just curious, I'm not wedded to it).
>
> Well, showing both versions adds confusion for no good reason, it
> doesn't promote one over the other, and if we decide to get rid of the
> old syntax someday, we can't do it.
Why the h*ll are you insistent on REMOVING the old syntax?

I see no good reason to remove it, and per TGL, the addition of the
couple(few?) rules in the grammar is negligible.

I sort of understand not documenting it, but please **DO NOT** remove
the old syntax without a damn good reason.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Larry Rosenman wrote:
>> Why? If both old and new are acceptable, why not document it?
>> (Just curious, I'm not wedded to it).

> Well, showing both versions adds confusion for no good reason,

Yes, particularly considering that LIMIT ... FOR UPDATE corresponds
to the implementation behavior (LIMIT acts before FOR UPDATE) while
FOR UPDATE ... LIMIT does not.

I concur with documenting only the preferred form (though there should
be a note in gram.y explaining that we're supporting the old syntax
for backward compatibility).

regards, tom lane

On Wed, 2002-08-28 at 10:11, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Larry Rosenman wrote:
> >> Why? If both old and new are acceptable, why not document it?
> >> (Just curious, I'm not wedded to it).
>
> > Well, showing both versions adds confusion for no good reason,
>
> Yes, particularly considering that LIMIT ... FOR UPDATE corresponds
> to the implementation behavior (LIMIT acts before FOR UPDATE) while
> FOR UPDATE ... LIMIT does not.
>
> I concur with documenting only the preferred form (though there should
> be a note in gram.y explaining that we're supporting the old syntax
> for backward compatibility).
>

Doesn't the need for a note explaining that we're supporting the old
syntax say to you that the documentation also needs to say we support
the old syntax? I can see the bug reports now saying "this is clearly
not what it says in the docs"...

Robert Treat

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Larry Rosenman wrote:
> >> Why? If both old and new are acceptable, why not document it?
> >> (Just curious, I'm not wedded to it).
>
> > Well, showing both versions adds confusion for no good reason,
>
> Yes, particularly considering that LIMIT ... FOR UPDATE corresponds
> to the implementation behavior (LIMIT acts before FOR UPDATE) while
> FOR UPDATE ... LIMIT does not.
>
> I concur with documenting only the preferred form (though there should
> be a note in gram.y explaining that we're supporting the old syntax
> for backward compatibility).

I originally thought the grammar would be ugly to support both, but in
fact it has almost the same number of actions as before, so we can keep
it around for a while if not forever.

I will update the gram.y comments to indicate it will live beyond 7.3.X.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Robert Treat wrote:
> On Wed, 2002-08-28 at 10:11, Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Larry Rosenman wrote:
> > >> Why? If both old and new are acceptable, why not document it?
> > >> (Just curious, I'm not wedded to it).
> >
> > > Well, showing both versions adds confusion for no good reason,
> >
> > Yes, particularly considering that LIMIT ... FOR UPDATE corresponds
> > to the implementation behavior (LIMIT acts before FOR UPDATE) while
> > FOR UPDATE ... LIMIT does not.
> >
> > I concur with documenting only the preferred form (though there should
> > be a note in gram.y explaining that we're supporting the old syntax
> > for backward compatibility).
> >
>
> Doesn't the need for a note explaining that we're supporting the old
> syntax say to you that the documentation also needs to say we support
> the old syntax? I can see the bug reports now saying "this is clearly
> not what it says in the docs"...

Well, people would be using the docs only to learn the suggested syntax,
not every syntax. COPY supports the old syntax, but has a new one for
7.3.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 2002-08-28 at 10:24, Robert Treat wrote:
> On Wed, 2002-08-28 at 10:11, Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Larry Rosenman wrote:
> > >> Why? If both old and new are acceptable, why not document it?
> > >> (Just curious, I'm not wedded to it).
> >
> > > Well, showing both versions adds confusion for no good reason,
> >
> > Yes, particularly considering that LIMIT ... FOR UPDATE corresponds
> > to the implementation behavior (LIMIT acts before FOR UPDATE) while
> > FOR UPDATE ... LIMIT does not.
> >
> > I concur with documenting only the preferred form (though there should
> > be a note in gram.y explaining that we're supporting the old syntax
> > for backward compatibility).
> >
>
> Doesn't the need for a note explaining that we're supporting the old
> syntax say to you that the documentation also needs to say we support
> the old syntax? I can see the bug reports now saying "this is clearly
> not what it says in the docs"...

Yes, both should be documented. But mark the non-preferred version as
depreciated and disappearing soon (whether it does or not is another
story) but discourage people from using it.

Rod Taylor wrote:
> > Doesn't the need for a note explaining that we're supporting the old
> > syntax say to you that the documentation also needs to say we support
> > the old syntax? I can see the bug reports now saying "this is clearly
> > not what it says in the docs"...
>
>
> Yes, both should be documented. But mark the non-preferred version as
> depreciated and disappearing soon (whether it does or not is another
> story) but discourage people from using it.

That SELECT syntax is already too confusing. I don't want to add an
additional documentation specification that provides no value to users.
One of the PostgreSQL goals is to not throw every single option at users
but to make logical decisions on tuning values and features to limit the
complexity shown to the user.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

OK, applied with documenation updates showing only the new syntax.

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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Larry Rosenman wrote:
> >> Why? If both old and new are acceptable, why not document it?
> >> (Just curious, I'm not wedded to it).
>
> > Well, showing both versions adds confusion for no good reason,
>
> Yes, particularly considering that LIMIT ... FOR UPDATE corresponds
> to the implementation behavior (LIMIT acts before FOR UPDATE) while
> FOR UPDATE ... LIMIT does not.
>
> I concur with documenting only the preferred form (though there should
> be a note in gram.y explaining that we're supporting the old syntax
> for backward compatibility).
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

>>>>> "BM" == Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

Bruce Momjian writes:

> That SELECT syntax is already too confusing. I don't want to add an
> additional documentation specification that provides no value to users.

The value of the documentation, especially the reference manual, is that
it provides an authorative source of what works and what doesn't. It is
not the place to hide transitional phases. Moreover, the least possible
value you could provide to users is to gratuitously[*] change the syntax
and not tell anyone about it.

[*] It's not like this will magically gain us MySQL or Oracle
compatibility.

In fact, the recent trend in the SQL commands has been to accept most
options in any order, so it would only be logical to accept the LIMIT and
FOR UDPATE options in any order and document that fact. There is a
separate section in each reference page for information about which format
is compatible with what.

But please remember that our foremost goal is to be compatible, both in
actuality and in mindset, with PostgreSQL, not with any other product that
happened to use a slightly different syntax at their whim.

Therefore I request that both forms be accepted and documented as equally
valid.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > That SELECT syntax is already too confusing. I don't want to add an
> > additional documentation specification that provides no value to users.
>
> The value of the documentation, especially the reference manual, is that
> it provides an authorative source of what works and what doesn't. It is
> not the place to hide transitional phases. Moreover, the least possible
> value you could provide to users is to gratuitously[*] change the syntax
> and not tell anyone about it.
>
> [*] It's not like this will magically gain us MySQL or Oracle
> compatibility.
>
> In fact, the recent trend in the SQL commands has been to accept most
> options in any order, so it would only be logical to accept the LIMIT and
> FOR UDPATE options in any order and document that fact. There is a
> separate section in each reference page for information about which format
> is compatible with what.
>
> But please remember that our foremost goal is to be compatible, both in
> actuality and in mindset, with PostgreSQL, not with any other product that
> happened to use a slightly different syntax at their whim.
>
> Therefore I request that both forms be accepted and documented as equally
> valid.

You made the same argument for the COPY syntax, that we publish both the
old and new syntaxes, and I resisted because I felt most people would
rather just see the best syntax. I don't see the documentation as
showing every possible syntax because that really doesn't benefit users,
or should I say confused more than it helps.

If you would like a vote, we can do that, but as I remember we had the
same issue with COPY and we got most votes to just show the best syntax.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wednesday 28 August 2002 05:06 pm, Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> > > That SELECT syntax is already too confusing. I don't want to add an
> > > additional documentation specification that provides no value to users.
> >
> > The value of the documentation, especially the reference manual, is that
> > it provides an authorative source of what works and what doesn't. It is
> > not the place to hide transitional phases. Moreover, the least possible
> > value you could provide to users is to gratuitously[*] change the syntax
> > and not tell anyone about it.
> >
> > In fact, the recent trend in the SQL commands has been to accept most
> > options in any order, so it would only be logical to accept the LIMIT and
> > FOR UDPATE options in any order and document that fact. There is a
> > separate section in each reference page for information about which
> > format is compatible with what.
> >
> > But please remember that our foremost goal is to be compatible, both in
> > actuality and in mindset, with PostgreSQL, not with any other product
> > that happened to use a slightly different syntax at their whim.
> >
> > Therefore I request that both forms be accepted and documented as equally
> > valid.
>
> You made the same argument for the COPY syntax, that we publish both the
> old and new syntaxes, and I resisted because I felt most people would
> rather just see the best syntax. I don't see the documentation as
> showing every possible syntax because that really doesn't benefit users,
> or should I say confused more than it helps.
>
> If you would like a vote, we can do that, but as I remember we had the
> same issue with COPY and we got most votes to just show the best syntax.

There are two cases where I see potential problems; first would be someone
trying to migrate from pre 7.3 who sees the "new" documentation and thinks
that part of his upgrade path is going to require rewriting X number of
queries/scripts/whatever... Second will be someone that comes in and needs to
work on some legacy code and what he looks at is directly in contrast to what
is in the documentation, but it appears to run fine. Both of these cases are
going to cause people confusion and when they post to the mailing list we'll
be denied an opportunity to just blurt out in all caps RTFM! :-)

I can assure you that both of these issues will be entered into the
interactive documents at some point, I'd just feel better if it we're in
there by default.

Robert Treat

>> If you would like a vote, we can do that, but as I remember we had the
>> same issue with COPY and we got most votes to just show the best syntax.

Perhaps we could compromise on showing only the new syntax in the <synopsis>
part of the man page, and then mentioning somewhere in the body of the
page that the other order is deprecated but accepted for backwards
compatibility. This same approach would work well for documenting
COPY's old syntax.

regards, tom lane

Tom Lane wrote:
> >> If you would like a vote, we can do that, but as I remember we had the
> >> same issue with COPY and we got most votes to just show the best syntax.
>
> Perhaps we could compromise on showing only the new syntax in the <synopsis>
> part of the man page, and then mentioning somewhere in the body of the
> page that the other order is deprecated but accepted for backwards
> compatibility. This same approach would work well for documenting
> COPY's old syntax.

Yes, I thought about that. People want to show both SELECT syntaxes,
but how would you do that --- show the SELECT syntax twice with just
those last two clauses reversed --- yuck.

We could easily mention that we allow both clause orderings in the text
somewhere.

For COPY, we could just put the old syntax at the bottom of the manual
page and mention it is depricated.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 2002-08-28 at 21:29, Robert Treat wrote:

>
> I think after the LIMIT and FOR UPDATE explanations (but before the note about
> SELECT privilege) you could add a note that "for backwards compatibility
> reasons the LIMIT and FOR UPDATE clauses are interchangeable" though maybe
> interchangeable isn't the best word...
How about "for backwards compatibility reasons the LIMIT and FOR UPDATE
clauses can appear in either order, I.E. LIMIT 1 FOR UPDATE and FOR
UPDATE LIMIT 1 are equivalent".

>
> > For COPY, we could just put the old syntax at the bottom of the manual
> > page and mention it is depricated.
>
> In both cases I don't know that a detailed explination is needed, but a
> mention of the different possibility and perhaps a suggestion to look at an
> old version of the docs for complete details should go a long way.
I suspect that Bruce's suggestion is best, modulo a spell check :-).
>
> Robert Treat
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

> Yes, I thought about that. People want to show both SELECT syntaxes,
> but how would you do that --- show the SELECT syntax twice with just
> those last two clauses reversed --- yuck.

select .... [ <stmt group>, ... ]

<stmt group> :
[ FOR UPDATE | LIMIT ]

The above, or something along those lines, would show order
independence.

> We could easily mention that we allow both clause orderings in the text
> somewhere.

On Wednesday 28 August 2002 09:14 pm, Bruce Momjian wrote:
> Tom Lane wrote:
> > Perhaps we could compromise on showing only the new syntax in the
> > <synopsis> part of the man page, and then mentioning somewhere in the
> > body of the page that the other order is deprecated but accepted for
> > backwards compatibility. This same approach would work well for
> > documenting COPY's old syntax.
>
> Yes, I thought about that. People want to show both SELECT syntaxes,
> but how would you do that --- show the SELECT syntax twice with just
> those last two clauses reversed --- yuck.
>
> We could easily mention that we allow both clause orderings in the text
> somewhere.
>

I think after the LIMIT and FOR UPDATE explanations (but before the note about
SELECT privilege) you could add a note that "for backwards compatibility
reasons the LIMIT and FOR UPDATE clauses are interchangeable" though maybe
interchangeable isn't the best word...

> For COPY, we could just put the old syntax at the bottom of the manual
> page and mention it is depricated.

In both cases I don't know that a detailed explination is needed, but a
mention of the different possibility and perhaps a suggestion to look at an
old version of the docs for complete details should go a long way.

Robert Treat

Rod Taylor wrote:
>
> > Yes, I thought about that. People want to show both SELECT syntaxes,
> > but how would you do that --- show the SELECT syntax twice with just
> > those last two clauses reversed --- yuck.
>
> select .... [ <stmt group>, ... ]
>
> <stmt group> :
> [ FOR UPDATE | LIMIT ]
>
>
> The above, or something along those lines, would show order
> independence.

It is this kind of added abstraction that I definitely want to avoid.
SELECT has enough complexity without adding to it. If this change was
required, I would suggest just backing out the entire patch and leaving
it alone.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us> writes:
> Rod Taylor wrote:
>> The above, or something along those lines, would show order
>> independence.

> It is this kind of added abstraction that I definitely want to avoid.

I agree. We want to promote the LIMIT/FOR UPDATE ordering, not treat
them on an even footing. I think it's quite reasonable to show only
the preferred ordering in the synopsis, and mention the other somewhere
in the body of the man page.

BTW, I'd like to see the old COPY syntax still documented, but in the
same way --- it need not be in the synopsis, just somewhere where people
can see it without having to refer back to old manuals.

regards, tom lane

Tom Lane wrote:
> BTW, I'd like to see the old COPY syntax still documented, but in the
> same way --- it need not be in the synopsis, just somewhere where people
> can see it without having to refer back to old manuals.

Agreed.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Rod Taylor wrote:
> >> The above, or something along those lines, would show order
> >> independence.
>
> > It is this kind of added abstraction that I definitely want to avoid.
>
> I agree. We want to promote the LIMIT/FOR UPDATE ordering, not treat
> them on an even footing. I think it's quite reasonable to show only
> the preferred ordering in the synopsis, and mention the other somewhere
> in the body of the man page.
>
> BTW, I'd like to see the old COPY syntax still documented, but in the
> same way --- it need not be in the synopsis, just somewhere where people
> can see it without having to refer back to old manuals.

Both done.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073