Thread: SELECT...FOR UPDATE OF class_name

Hi all - ran into this little parser idiosyncrasy today... Workaround was
simple but this should probably go on somebody's list somewhere.

[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]

mail=> create table tbl1 ( id1 int4 );
CREATE

mail=> create table tbl2 ( id2 int4, id1 int4 ) ;
CREATE

mail=> select 1 from tbl2 t2, tbl1 t1 where t1.id1 = t2.id1 andt2.id1 = 7 for update ;
?column?
--------
(0 rows)

mail=> select 1 from tbl2 t2, tbl1 t1 where t1.id1 = t2.id1 andt2.id1 = 7 for update of t2;
?column?
--------
(0 rows)

mail=> select 1 from tbl2 t2, tbl1 t1 where t1.id1 = t2.id1 and t2.id1 = 7 for update of tbl2;

ERROR:  FOR UPDATE: relation tbl2 not found in FROM clause

- K

Kristofer Munn * KMI * 973-509-9414 * AIM KrMunn * http://www.munn.com/

Kristofer Munn <kmunn@munn.com> writes:
> select 1 from tbl2 t2, tbl1 t1 where t1.id1 = t2.id1 and 
>     t2.id1 = 7 for update of tbl2;

> ERROR:  FOR UPDATE: relation tbl2 not found in FROM clause

I believe the error message is correct; you should have written

select 1 from tbl2 t2, tbl1 t1 where t1.id1 = t2.id1 and t2.id1 = 7 for update of t2;

A lot of people do not realize that writing an alias for a table
in FROM means that as far as all the rest of that query is concerned,
that alias *is* the name of the table.  The original table name is
completely masked by the alias.  This must be so, because one of the
main reasons for the alias facility is to resolve ambiguity when you
are doing self-joins.  Consider
select * from person p1, person p2 where p1.spouse = p2.id;

If you wrote instead
select * from person p1, person p2 where p1.spouse = person.id;

which instance of the person table is being referenced?  SQL resolves
this by treating it as an error: there is no table named person
available from that FROM clause.
        regards, tom lane

Tom Lane wrote:
>
> > ERROR:  FOR UPDATE: relation tbl2 not found in FROM clause
> 
> I believe the error message is correct; you should have written
> 
> select 1 from tbl2 t2, tbl1 t1 where t1.id1 = t2.id1 and 
>     t2.id1 = 7 for update of t2;
> 
> A lot of people do not realize that writing an alias for a table
> in FROM means that as far as all the rest of that query is concerned,
> that alias *is* the name of the table.  
>
> [ additional comments and self-join example clipped ]

Ok, that sounds like a fine rule except for non-self-joins:

mail=> select 1 from tbl2 t2, tbl1 t1 where tbl1.id1 = t2.id1 and t2.id1 = 7 ;                             ^^^^^^^
^^^^
 
Does not give any error.  I had expected that behavior to be consistent
which is why I ran into the error.  However, I have no problem with that
explanation.

- K

Kristofer Munn * KMI * 973-509-9414 * AIM KrMunn * http://www.munn.com/

Kristofer Munn <kmunn@munn.com> writes:
> select 1 from tbl2 t2, tbl1 t1 where tbl1.id1 = t2.id1 and t2.id1 = 7 ;
>                        ^^^^^^^       ^^^^
> Does not give any error.

What that's doing is giving you a *three way* join --- Postgres silently
adds an implicit FROM clause for the unaliased tbl1, as if you'd writtenFROM tbl2 t2, tbl1 t1, tbl1

This behavior has confused a lot of people; moreover it's not SQL
standard (I think it's a leftover from Berkeley's old POSTQUEL
language).  There's been a good deal of talk about removing it,
or at least giving a NOTICE when an implicit FROM clause is added.

FOR UPDATE seems to be set up to not allow implicit FROM clause
addition, which is probably a good thing --- it wouldn't make much
sense to say FOR UPDATE on a table not appearing anywhere else in
the query...
        regards, tom lane