SELECT ... FOR UPDATE OF

On Thu, Dec 17, 2020 at 5:29 AM PG Doc comments form <noreply@postgresql.org>
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.

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