Thread: SELECT updatability clause defective.

Jerome O\'Neil (joneil@combimatrix.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
SELECT updatability clause defective.

Long Description
The syntax for select updatability clause is non-standard.  Postgres describes select updatability as

SELECT ...
 [FOR UDATE [OF tablename]]

While SQL-92 BNF describes it as

SELECT ...
 [FOR UPDATE [OF <column name list>]]

This breaks portability for third party tools (like BEA Weblogic JMS hint hint...)

Also, your bug tool doesn't like people with apostrophies in their name.

Sample Code
CREATE TABLE foo (column1 int4, column2 int4);
INSERT INTO foo VALUES (1,2);

-- Broken Postgres syntax
SELECT column1 FROM foo where column1 = 1 FOR UPDATE OF foo;

-- Nice and friendly SQL-92 syntax
SELECT column1 FROM foo where column1 = 1 FOR UPDATE OF column1;


No file was uploaded with this report

> Jerome O\'Neil (joneil@combimatrix.com) reports a bug with a severity of 2

> SELECT updatability clause defective.

Nope.

> The syntax for select updatability clause is non-standard.

That's not a bug.

> While SQL-92 BNF describes it as
>
> SELECT ...
>  [FOR UPDATE [OF <column name list>]]

Chapter and verse?

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

On Wed, 9 May 2001, Peter Eisentraut wrote:

> > Jerome O\'Neil (joneil@combimatrix.com) reports a bug with a severity of 2
>
> > SELECT updatability clause defective.
>
> Nope.
>
> > The syntax for select updatability clause is non-standard.
>
> That's not a bug.
>
> > While SQL-92 BNF describes it as
> >
> > SELECT ...
> >  [FOR UPDATE [OF <column name list>]]
>
> Chapter and verse?

I see it as part of declare cursor (13.1)

<updatability clause> ::=
    FOR { READ ONLY | UPDATE [ OF <column name list> ] }

However, I think the updatability clause means something
different than our for update.  If I'm reading it write, I think
it tells you whether or not you may do positioned updates on
the cursor.

> I see it as part of declare cursor (13.1)
>
> <updatability clause> ::=
>     FOR { READ ONLY | UPDATE [ OF <column name list> ] }
>
> However, I think the updatability clause means something
> different than our for update.  If I'm reading it write, I think
> it tells you whether or not you may do positioned updates on
> the cursor.

You're right, it is part of the cursor specification, and not the query
term. My bad for the confusion.

In the cursor context, it's used to declare what columns are
updateable through the cursor.  By adding the syntax to the
raw select statement, you can declare columns updateable
through the result set.  At least that's the intent of other
vendors.

-- SQL-92, and is unsupported by Postgres.
DECLARE  mycursor CURSOR
FOR
 SELECT foo,baz FROM bar
FOR UPDATE OF foo

-- Adheres to the BNF, and is not supported by Postgres
SELECT foo,baz FROM bar FOR UPDATE OF foo

-- Breaks the BNF but is supported by Postgres
SELECT foo,baz FROM bar FOR UPDATE OF bar

Which makes me wonder:  What is the point, if not to
restrict updatability?

What is the difference between:

-- Allow updates to everything.
SELECT * FROM bar FOR UPDATE

and

-- Allow updates to everything.
SELECT * FROM bar FOR UPDATE OF bar

"Jerome O'Neil" <joneil@combimatrix.com> writes:
> Which makes me wonder:  What is the point, if not to
> restrict updatability?

Postgres' SELECT FOR UPDATE marks rows for update, not columns.
Thus nominating columns in it would make no sense.

It was probably an unfortunate decision to use "FOR UPDATE" as
the syntax for this feature, since the spec uses that phrase
to deal with something completely different.  But we're not gonna
change it now, especially since it's not getting in the way of
adding support for the spec's feature.

            regards, tom lane