Skip to content

Commit b853f33

Browse files
tglsfdctglsfdc
committed
Provide adequate documentation of the "table_name *" notation.
Somewhere along the line, somebody decided to remove all trace of this notation from the documentation text. It was still in the command syntax synopses, or at least some of them, but with no indication what it meant. This will not do, as evidenced by the confusion apparent in bug #7543; even if the notation is now unnecessary, people will find it in legacy SQL code and need to know what it does.
1 parent 17ddb7d commit b853f33

File tree

9 files changed

+104
-73
lines changed

9 files changed

+104
-73
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5236,11 +5236,23 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
52365236
<indexterm><primary>inheritance</></>
52375237
<listitem>
52385238
<para>
5239-
This controls the inheritance semantics. If turned <literal>off</>,
5240-
subtables are not accessed by various commands by default; basically
5241-
an implied <literal>ONLY</literal> key word. This was added for
5242-
compatibility with releases prior to 7.1. See
5243-
<xref linkend="ddl-inherit"> for more information.
5239+
This setting controls whether undecorated table references are
5240+
considered to include inheritance child tables. The default is
5241+
<literal>on</>, which means child tables are included (thus,
5242+
a <literal>*</> suffix is assumed by default). If turned
5243+
<literal>off</>, child tables are not included (thus, an
5244+
<literal>ONLY</literal> prefix is assumed). The SQL standard
5245+
requires child tables to be included, so the <literal>off</> setting
5246+
is not spec-compliant, but it is provided for compatibility with
5247+
<productname>PostgreSQL</> releases prior to 7.1.
5248+
See <xref linkend="ddl-inherit"> for more information.
5249+
</para>
5250+
5251+
<para>
5252+
Turning <varname>sql_inheritance</> off is deprecated, because that
5253+
behavior has been found to be error-prone as well as contrary to SQL
5254+
standard. Discussions of inheritance behavior elsewhere in this
5255+
manual generally assume that it is <literal>on</>.
52445256
</para>
52455257
</listitem>
52465258
</varlistentry>

‎doc/src/sgml/ddl.sgml

Lines changed: 21 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2050,6 +2050,23 @@ SELECT name, altitude
20502050
<literal>ONLY</literal> keyword.
20512051
</para>
20522052

2053+
<para>
2054+
You can also write the table name with a trailing <literal>*</>
2055+
to explicitly specify that descendant tables are included:
2056+
2057+
<programlisting>
2058+
SELECT name, altitude
2059+
FROM cities*
2060+
WHERE altitude &gt; 500;
2061+
</programlisting>
2062+
2063+
Writing <literal>*</> is not necessary, since this behavior is
2064+
the default (unless you have changed the setting of the
2065+
<xref linkend="guc-sql-inheritance"> configuration option).
2066+
However writing <literal>*</> might be useful to emphasize that
2067+
additional tables will be searched.
2068+
</para>
2069+
20532070
<para>
20542071
In some cases you might wish to know which table a particular row
20552072
originated from. There is a system column called
@@ -2197,15 +2214,15 @@ VALUES ('New York', NULL, NULL, 'NY');
21972214
data modification, or schema modification
21982215
(e.g., <literal>SELECT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
21992216
most variants of <literal>ALTER TABLE</literal>, but
2200-
not <literal>INSERT</literal> and <literal>ALTER TABLE ...
2217+
not <literal>INSERT</literal> or <literal>ALTER TABLE ...
22012218
RENAME</literal>) typically default to including child tables and
22022219
support the <literal>ONLY</literal> notation to exclude them.
22032220
Commands that do database maintenance and tuning
22042221
(e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
2205-
typically only work on individual, physical tables and do no
2222+
typically only work on individual, physical tables and do not
22062223
support recursing over inheritance hierarchies. The respective
2207-
behavior of each individual command is documented in the reference
2208-
part (<xref linkend="sql-commands">).
2224+
behavior of each individual command is documented in its reference
2225+
page (<xref linkend="sql-commands">).
22092226
</para>
22102227

22112228
<para>
@@ -2255,18 +2272,6 @@ VALUES ('New York', NULL, NULL, 'NY');
22552272
inheritance is useful for your application.
22562273
</para>
22572274

2258-
<note>
2259-
<title>Deprecated</title>
2260-
<para>
2261-
In releases of <productname>PostgreSQL</productname> prior to 7.1, the
2262-
default behavior was not to include child tables in queries. This was
2263-
found to be error prone and also in violation of the SQL
2264-
standard. You can get the pre-7.1 behavior by turning off the
2265-
<xref linkend="guc-sql-inheritance"> configuration
2266-
option.
2267-
</para>
2268-
</note>
2269-
22702275
</sect2>
22712276
</sect1>
22722277

‎doc/src/sgml/queries.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,16 @@ FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
140140
&mdash; any columns added in subtables are ignored.
141141
</para>
142142

143+
<para>
144+
Instead of writing <literal>ONLY</> before the table name, you can write
145+
<literal>*</> after the table name to explicitly specify that descendant
146+
tables are included. Writing <literal>*</> is not necessary since that
147+
behavior is the default (unless you have changed the setting of the <xref
148+
linkend="guc-sql-inheritance"> configuration option). However writing
149+
<literal>*</> might be useful to emphasize that additional tables will be
150+
searched.
151+
</para>
152+
143153
<sect3 id="queries-join">
144154
<title>Joined Tables</title>
145155

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -490,10 +490,12 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
490490
<term><replaceable class="PARAMETER">name</replaceable></term>
491491
<listitem>
492492
<para>
493-
The name (possibly schema-qualified) of an existing table to
494-
alter. If <literal>ONLY</> is specified, only that table is
495-
altered. If <literal>ONLY</> is not specified, the table and any
496-
descendant tables are altered.
493+
The name (optionally schema-qualified) of an existing table to
494+
alter. If <literal>ONLY</> is specified before the table name, only
495+
that table is altered. If <literal>ONLY</> is not specified, the table
496+
and all its descendant tables (if any) are altered. Optionally,
497+
<literal>*</> can be specified after the table name to explicitly
498+
indicate that descendant tables are included.
497499
</para>
498500
</listitem>
499501
</varlistentry>
@@ -877,7 +879,7 @@ ALTER TABLE distributors DROP CONSTRAINT zipchk;
877879
</para>
878880

879881
<para>
880-
To remove a check constraint from a table only:
882+
To remove a check constraint from one table only:
881883
<programlisting>
882884
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
883885
</programlisting>

‎doc/src/sgml/ref/delete.sgml

Lines changed: 8 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
24+
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2525
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
2626
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
2727
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -46,13 +46,6 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
4646
</para>
4747
</tip>
4848

49-
<para>
50-
By default, <command>DELETE</command> will delete rows in the
51-
specified table and all its child tables. If you wish to delete only
52-
from the specific table mentioned, you must use the
53-
<literal>ONLY</literal> clause.
54-
</para>
55-
5649
<para>
5750
There are two ways to delete rows in a table using information
5851
contained in other tables in the database: using sub-selects, or
@@ -83,21 +76,17 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
8376
<title>Parameters</title>
8477

8578
<variablelist>
86-
<varlistentry>
87-
<term><literal>ONLY</></term>
88-
<listitem>
89-
<para>
90-
If specified, delete rows from the named table only. When not
91-
specified, any tables inheriting from the named table are also processed.
92-
</para>
93-
</listitem>
94-
</varlistentry>
95-
9679
<varlistentry>
9780
<term><replaceable class="parameter">table</replaceable></term>
9881
<listitem>
9982
<para>
100-
The name (optionally schema-qualified) of an existing table.
83+
The name (optionally schema-qualified) of the table to delete rows
84+
from. If <literal>ONLY</> is specified before the table name,
85+
matching rows are deleted from the named table only. If
86+
<literal>ONLY</> is not specified, matching rows are also deleted
87+
from any tables inheriting from the named table. Optionally,
88+
<literal>*</> can be specified after the table name to explicitly
89+
indicate that descendant tables are included.
10190
</para>
10291
</listitem>
10392
</varlistentry>

‎doc/src/sgml/ref/lock.sgml

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]
24+
LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]
2525

2626
<phrase>where <replaceable class="PARAMETER">lockmode</replaceable> is one of:</phrase>
2727

@@ -109,9 +109,11 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ...
109109
<listitem>
110110
<para>
111111
The name (optionally schema-qualified) of an existing table to
112-
lock. If <literal>ONLY</> is specified, only that table is
113-
locked. If <literal>ONLY</> is not specified, the table and all
114-
its descendant tables (if any) are locked.
112+
lock. If <literal>ONLY</> is specified before the table name, only that
113+
table is locked. If <literal>ONLY</> is not specified, the table and all
114+
its descendant tables (if any) are locked. Optionally, <literal>*</>
115+
can be specified after the table name to explicitly indicate that
116+
descendant tables are included.
115117
</para>
116118

117119
<para>

‎doc/src/sgml/ref/select.sgml

Lines changed: 22 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -268,10 +268,12 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
268268
<term><replaceable class="parameter">table_name</replaceable></term>
269269
<listitem>
270270
<para>
271-
The name (optionally schema-qualified) of an existing table or
272-
view. If <literal>ONLY</> is specified, only that table is
273-
scanned. If <literal>ONLY</> is not specified, the table and
274-
any descendant tables are scanned.
271+
The name (optionally schema-qualified) of an existing table or view.
272+
If <literal>ONLY</> is specified before the table name, only that
273+
table is scanned. If <literal>ONLY</> is not specified, the table
274+
and all its descendant tables (if any) are scanned. Optionally,
275+
<literal>*</> can be specified after the table name to explicitly
276+
indicate that descendant tables are included.
275277
</para>
276278
</listitem>
277279
</varlistentry>
@@ -1555,15 +1557,24 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
15551557
</refsect2>
15561558

15571559
<refsect2>
1558-
<title><literal>ONLY</literal> and Parentheses</title>
1560+
<title><literal>ONLY</literal> and Inheritance</title>
15591561

15601562
<para>
1561-
The SQL standard requires parentheses around the table name
1562-
after <literal>ONLY</literal>, as in <literal>SELECT * FROM ONLY
1563-
(tab1), ONLY (tab2) WHERE ...</literal>. PostgreSQL supports that
1564-
as well, but the parentheses are optional. (This point applies
1565-
equally to all SQL commands supporting the <literal>ONLY</literal>
1566-
option.)
1563+
The SQL standard requires parentheses around the table name when
1564+
writing <literal>ONLY</literal>, for example <literal>SELECT * FROM ONLY
1565+
(tab1), ONLY (tab2) WHERE ...</literal>. <productname>PostgreSQL</>
1566+
considers these parentheses to be optional.
1567+
</para>
1568+
1569+
<para>
1570+
<productname>PostgreSQL</> allows a trailing <literal>*</> to be written to
1571+
explicitly specify the non-<literal>ONLY</literal> behavior of including
1572+
child tables. The standard does not allow this.
1573+
</para>
1574+
1575+
<para>
1576+
(These points apply equally to all SQL commands supporting the
1577+
<literal>ONLY</literal> option.)
15671578
</para>
15681579
</refsect2>
15691580

‎doc/src/sgml/ref/truncate.sgml

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
24+
TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ... ]
2525
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
2626
</synopsis>
2727
</refsynopsisdiv>
@@ -47,10 +47,12 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
4747
<term><replaceable class="PARAMETER">name</replaceable></term>
4848
<listitem>
4949
<para>
50-
The name (optionally schema-qualified) of a table to be
51-
truncated. If <literal>ONLY</> is specified, only that table is
52-
truncated. If <literal>ONLY</> is not specified, the table and
53-
all its descendant tables (if any) are truncated.
50+
The name (optionally schema-qualified) of a table to truncate.
51+
If <literal>ONLY</> is specified before the table name, only that table
52+
is truncated. If <literal>ONLY</> is not specified, the table and all
53+
its descendant tables (if any) are truncated. Optionally, <literal>*</>
54+
can be specified after the table name to explicitly indicate that
55+
descendant tables are included.
5456
</para>
5557
</listitem>
5658
</varlistentry>

‎doc/src/sgml/ref/update.sgml

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
24+
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2525
SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
2626
( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
2727
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
@@ -40,13 +40,6 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
4040
columns not explicitly modified retain their previous values.
4141
</para>
4242

43-
<para>
44-
By default, <command>UPDATE</command> will update rows in the
45-
specified table and all its subtables. If you wish to only update
46-
the specific table mentioned, you must use the <literal>ONLY</>
47-
clause.
48-
</para>
49-
5043
<para>
5144
There are two ways to modify a table using information contained in
5245
other tables in the database: using sub-selects, or specifying
@@ -84,6 +77,11 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
8477
<listitem>
8578
<para>
8679
The name (optionally schema-qualified) of the table to update.
80+
If <literal>ONLY</> is specified before the table name, matching rows
81+
are updated in the named table only. If <literal>ONLY</> is not
82+
specified, matching rows are also updated in any tables inheriting from
83+
the named table. Optionally, <literal>*</> can be specified after the
84+
table name to explicitly indicate that descendant tables are included.
8785
</para>
8886
</listitem>
8987
</varlistentry>

0 commit comments

Comments
 (0)