SELECT ... FOR UPDATE OF clause documentation implies use of table_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.
Attachments:
alias.difftext/x-diff; charset=us-asciiDownload+1-1
On Fri, Nov 17, 2023 at 3:13 PM Bruce Momjian <bruce@momjian.us> wrote:
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.The attached patch documents this.
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.
David J.
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.
Attachments:
alias.difftext/x-diff; charset=us-asciiDownload+6-2
On Mon, Nov 20, 2023 at 7:04 PM Bruce Momjian <bruce@momjian.us> wrote:
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 thealias 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 justdefine
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?
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 [...]
David J.
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 matchwhere 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.
Attachments:
alias.difftext/x-diff; charset=us-asciiDownload+7-4
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.
Thanks!
David J.
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.