pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
8+ messages / 3 participants
[nested] [flat]
* SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2018-04-27 13:47 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: PG Doc comments form @ 2018-04-27 13:47 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: lists@humanleg.org.uk
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-11-17 22:13 Bruce Momjian <bruce@momjian.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2023-11-17 22:13 UTC (permalink / raw)
To: lists@humanleg.org.uk; pgsql-docs@lists.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.sgml
index 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>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-11-17 22:44 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-11-17 22:44 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: lists@humanleg.org.uk; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-11-21 02:04 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2023-11-21 02:04 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: lists@humanleg.org.uk; pgsql-docs@lists.postgresql.org
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:
[text/x-diff] alias.diff (1.6K, ../../ZVwQHCA85qhKPUcG@momjian.us/2-alias.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 227ba1993b..c98ee8f7ee 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">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -202,7 +202,11 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
is specified, the
<command>SELECT</command> statement locks the selected rows
against concurrent updates. (See <xref linkend="sql-for-update-share"/>
- below.)
+ below.) <replaceable
+ class="parameter">from_reference</replaceable> must be a table
+ <replaceable class="parameter">alias</replaceable> or non-hidden
+ <replaceable class="parameter">table_name</replaceable> referenced
+ in the <literal>FROM</literal> clause.
</para>
</listitem>
</orderedlist>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-11-21 02:19 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-11-21 02:19 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: lists@humanleg.org.uk; pgsql-docs@lists.postgresql.org
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 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?
>
>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-11-21 03:16 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2023-11-21 03:16 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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.
Attachments:
[text/x-diff] alias.diff (2.0K, ../../ZVwg9AP4Zvj3RR1J@momjian.us/2-alias.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 227ba1993b..9917df7839 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">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -1577,7 +1577,7 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
The locking clause has the general form
<synopsis>
-FOR <replaceable>lock_strength</replaceable> [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ]
+FOR <replaceable>lock_strength</replaceable> [ OF <replaceable class="parameter">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ]
</synopsis>
where <replaceable>lock_strength</replaceable> can be one of
@@ -1591,8 +1591,11 @@ KEY SHARE
</para>
<para>
- For more information on each row-level lock mode, refer to
- <xref linkend="locking-rows"/>.
+ <replaceable class="parameter">from_reference</replaceable> must be a
+ table <replaceable class="parameter">alias</replaceable> or non-hidden
+ <replaceable class="parameter">table_name</replaceable> referenced
+ in the <literal>FROM</literal> clause. For more information on each
+ row-level lock mode, refer to <xref linkend="locking-rows"/>.
</para>
<para>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-11-21 03:20 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-11-21 03:20 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
@ 2023-12-08 00:43 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2023-12-08 00:43 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-12-08 00:43 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-27 13:47 SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases PG Doc comments form <noreply@postgresql.org>
2023-11-17 22:13 ` Bruce Momjian <bruce@momjian.us>
2023-11-17 22:44 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-21 02:04 ` Bruce Momjian <bruce@momjian.us>
2023-11-21 02:19 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-21 03:16 ` Bruce Momjian <bruce@momjian.us>
2023-11-21 03:20 ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-08 00:43 ` Bruce Momjian <bruce@momjian.us>
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