Обсуждение: SELECT ... FOR UPDATE OF clause documentation implies use oftable_names rather than aliases

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.


robert.

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.

On Fri, Nov 17, 2023 at 03:44:04PM -0700, David G. Johnston wrote:
> I don't like this particular solution to the stated complaint.  When a FROM
> entry has an alias it must be referenced via that alias anywhere it is
> referenced in the query - and indeed it is an error to not write the alias in
> your example.  It is not an improvement to write [ table_name | alias ] in our
> syntax to try and demonstrate this requirement.  If we do want to not say
> "table_name" I suggest we say instead "from_reference" and then just define
> what that means (i.e., an unaliased table name or an alias in the sibling FROM
> clause attached to this level of the query).  I like this better anyway on the
> grounds that the thing being referenced can be a subquery or a view as well as
> a table.

Okay, how is the attached patch?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

On Mon, Nov 20, 2023 at 07:19:39PM -0700, David G. Johnston wrote:
> The placement in the numbered listing section feels wrong, I am OK with
> the wording.  It should be down in the clause details.
> 
> FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ]  --
> need to change this spot to match
> 
> where lock_strength can be one of
> 
> [...]
> 
> + and from_reference must be a table alias or non-hidden table_name referenced
> in the FROM clause.
> 
> For more information on each [...]

Ah, good point.  I was searching for "FOR UPDATE" so I missed that
section;  updated patch attached.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

On Mon, Nov 20, 2023 at 08:20:57PM -0700, David G. Johnston wrote:
> On Mon, Nov 20, 2023 at 8:16 PM Bruce Momjian <bruce@momjian.us> wrote:
> 
>     On Mon, Nov 20, 2023 at 07:19:39PM -0700, David G. Johnston wrote:
>     > The placement in the numbered listing section feels wrong, I am OK with
>     > the wording.  It should be down in the clause details.
>     >
>     > FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] 
>     --
>     > need to change this spot to match
>     >
>     > where lock_strength can be one of
>     >
>     > [...]
>     >
>     > + and from_reference must be a table alias or non-hidden table_name
>     referenced
>     > in the FROM clause.
>     >
>     > For more information on each [...]
> 
>     Ah, good point.  I was searching for "FOR UPDATE" so I missed that
>     section;  updated patch attached.
> 
> 
> 
> WFM.

Patch applied to master.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.