From: Bruce Momjian <bruce@momjian.us>
To: lists@humanleg.org.uk
To: pgsql-docs@lists.postgresql.org
Subject: Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
Date: Fri, 17 Nov 2023 17:13:21 -0500
Message-ID: <ZVflgb-C2xAmdgBC@momjian.us> (raw)
In-Reply-To: <152483686904.19805.3369061025704720797@wrigleys.postgresql.org>
References: <152483686904.19805.3369061025704720797@wrigleys.postgresql.org>
On Fri, Apr 27, 2018 at 01:47:49PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/9.5/static/sql-select.html
> Description:
>
> In the SELECT statement page the argument type of the (FOR SHARE/UPDATE) OF
> clause is listed to be a table_name. This is not *quite* accurate - it
> should reference the *alias* assigned to the table if one was given. The
> distinction is subtly important, as without this information the
> documentation implies that the choice of rows to lock can only be done
> per-table (i.e. that in a query mentioning the same table twice, *any*
> tuples being pulled from that table would be given the same treatment).
>
> But in fact postgres supports specifying the locking behaviour per-alias,
> which is a really powerful ability. And actually, trying to specify it by
> actual "table name" where an alias has been assigned won't work either.
I can confirm this report from 2018:
CREATE TABLE test ( x INT );
SELECT * FROM test AS t1 JOIN test AS t2 ON (TRUE) FOR UPDATE OF t1;
x | x
---+---
SELECT * FROM test AS t1 JOIN test AS t2 ON (TRUE) FOR UPDATE OF t2;
x | x
---+---
The attached patch documents this.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] alias.diff (1.0K, ../ZVflgb-C2xAmdgBC@momjian.us/2-alias.diff)
download | inline diff:diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgmlindex 227ba1993b..ab3760057c 100644--- a/doc/src/sgml/ref/select.sgml+++ b/doc/src/sgml/ref/select.sgml@@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
- [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]+ [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF { <replaceable class="parameter">table_name</replaceable> | <replaceable class="parameter">alias</replaceable> } [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, lists@humanleg.org.uk, pgsql-docs@lists.postgresql.org
Subject: Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
In-Reply-To: <ZVflgb-C2xAmdgBC@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox