SELECT ... FOR UPDATE OF

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SELECT ... FOR UPDATE OF

apt.postgresql.org Repository Update
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-select.html
Description:

The FOR UPDATE clause of the SELECT statement can be followed by an OF
clause, but this is never explained at all in the documentation, as far as I
can tell. What does it do? This needs to be written down.

For example, when attempting to use the OF clause in a particular query, I
get the error "FOR UPDATE must specify unqualified relation names". The
documentation doesn't provide enough information for me to understand what I
have done wrong, so I will have to use trial and error to solve my problem.
Reply | Threaded
Open this post in threaded view
|

Re: SELECT ... FOR UPDATE OF

David G Johnston
On Thu, Dec 17, 2020 at 5:29 AM PG Doc comments form <[hidden email]> wrote:
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-select.html
Description:

The FOR UPDATE clause of the SELECT statement can be followed by an OF
clause, but this is never explained at all in the documentation, as far as I
can tell. What does it do? This needs to be written down.

It is.  "If specific tables are named in a locking clause, then only rows coming from those tables are locked;"


For example, when attempting to use the OF clause in a particular query, I
get the error "FOR UPDATE must specify unqualified relation names". The
documentation doesn't provide enough information for me to understand what I
have done wrong, so I will have to use trial and error to solve my problem.

A relation name is qualified if it has a schema in front of it.  I'll agree that the docs don't indicate that "table_name" has to be unqualified.  But mostly the OF clause points to the relations named within the query itself, not to the original relation as recorded in the catalog.

David J.

Reply | Threaded
Open this post in threaded view
|

RE: SELECT ... FOR UPDATE OF

Alan Thomas

Hi David,

 

Thanks for responding.

 

> It is.  "If specific tables are named in a locking clause, then only rows coming from those tables are locked;"

 

I see – thanks for pointing it out.

 

It would be good if this sentence actually referred to "OF" by name. The section on locking clauses is long and densely written, and a direct mention of the syntax element would help the reader find the relevant information.

 

Even an example using FOR UPDATE OF ... might be a useful addition.

 

> I'll agree that the docs don't indicate that "table_name" has to be unqualified. 

 

We shouldn't need to work things out by guesswork / resolving error messages. This should be in the docs so we can get it right the first time.

 

Thanks,

Alan