public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Chao Li <[email protected]>
Cc: Postgres hackers <[email protected]>
Cc: Amit Kapila <[email protected]>
Subject: Re: docs: clarify ALTER TABLE behavior on partitioned tables
Date: Wed, 7 Jan 2026 15:17:39 -0700
Message-ID: <CAKFQuwbWNCX1M1e9_-3P9RVGo10huPjuqdL74FJ+-a5EW791KA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com>
<[email protected]>
On Wed, Jan 7, 2026 at 1:29 AM Chao Li <[email protected]> wrote:
>
> >
> >
> > [1]
> https://postgr.es/m/CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com
> >
> > <v1-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch>
>
> Added to CF: https://commitfest.postgresql.org/patch/6379/
>
>
Fairly easy to review in its current form.
I've included my changes as a patch over your version 1.
The main points of interest:
Saying that "ONLY" is a no-op when the observed behavior is that only
the mentioned tables are affected seems wrong. I've removed those
instances.
I tried to keep the "and 'is implicitly <actioned>" verbiage consistent
throughout. "Implicitly present" just seems off regardless of consistency.
"new partitions created in the future" - this is wordy given that "new"
implies "created in the future". Went with a simple "Newly created
partitions".
I did mentally note at the end of this review session that quite a bit of
text is spent saying how "create table" works in this "alter table"
reference. I didn't try to address it though.
You were using "can be applied independently" when in fact one "must"
specify all desired tables to be acted upon in those sub-commands. And, in
that case in particular, if ONLY is accepted it would just do what the
command already does. I removed the mention of ONLY in these "must" cases.
The majority of additions you made and existing mentions of "individual
partitions" do not include the clarification of "(leaf)". I removed those
that did - it seems like an unnecessary clarification.
If one has dropped a constraint from a partitioned table there would be no
reason to expect that future newly created partitions might somehow have
it. I removed the wording that stated that this was the case.
It didn't seem necessary to point out that the obsolete backward compatible
syntax for OIDS doesn't apply to partition-related tables.
Overall it looks good. The mentions of "newly created ... do [not]
inherit" is my only place of doubt. I'd be inclined to remove them all,
and if they are not covered elsewhere, introduce a section to cover them in
the DDL chapter.
David J.
Attachments:
[text/x-patch] v2-0001-docs-Clarify-ALTER-TABLE-v1.patch (31.8K, 3-v2-0001-docs-Clarify-ALTER-TABLE-v1.patch)
download | inline diff:
From 3fc3b4eb84dbdbe58eb283c3322ff6cf16d9a1d0 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH 1/2] docs Clarify ALTER TABLE
---
doc/src/sgml/ref/alter_table.sgml | 309 +++++++++++++++++++++++++-----
1 file changed, 257 insertions(+), 52 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..b1190fae968 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly present in all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +223,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +230,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and affects all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +249,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ New partitions created afterwards inherit the default from the
+ partitioned table, but individual partitions may define a different
+ default.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +266,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +278,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table, which does not affect the
+ children.
</para>
</listitem>
</varlistentry>
@@ -279,7 +303,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +311,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all existing partitions unless <literal>ONLY</literal>
+ is specified. New partitions created afterwards inherit the
+ expression from the partitioned table, while individual partitions
+ may define a different expression.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +329,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +361,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table definition and applies to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +385,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ definition and affect all partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +415,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ Partitions created afterwards do not inherit the statistics target
+ from the partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +455,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, these forms can be applied independently to
+ the partitioned table and to individual partitions. Changes made to
+ the partitioned table do not propagate to existing partitions, and
+ partitions created afterwards do not inherit the setting.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +496,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ New partitions created afterwards inherit the setting from the
+ partitioned table, while individual partitions may use a different
+ storage setting.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +533,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table, the compression setting is applied
+ to the partitioned table definition and does not propagate to existing
+ partitions. New partitions created afterwards inherit the setting from
+ the partitioned table, while individual partitions may use a different
+ compression method. Specifying <literal>ONLY</literal> is allowed but has
+ no effect.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +553,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +568,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +578,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is automatically applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed. New partitions
+ created afterwards inherit the constraint from the partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +599,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +606,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +613,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +625,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual (leaf) partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +649,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition and the change applies to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +673,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual (leaf) partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +696,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +714,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ New partitions created afterwards do not include the dropped
+ constraint, while individual partitions may drop the constraint
+ independently of the partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +742,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +751,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +762,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers in existing partitions are updated too,
+ unless <literal>ONLY</literal> is specified. Triggers in partitions
+ created afterwards inherit the trigger state from the partitioned
+ table, while individual partitions may configure trigger states
+ independently.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +787,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ For partitioned tables, these forms can be applied independently to
+ the partitioned table and to individual partitions. Changes made to
+ the partitioned table do not propagate to existing partitions, and
+ partitions created afterwards do not inherit the setting.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +814,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ For partitioned tables, these forms can be applied independently to
+ the partitioned table and to individual partitions. Changes made to
+ the partitioned table do not propagate to existing partitions, and
+ partitions created afterwards do not inherit the setting.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +836,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ For partitioned tables, these forms can be applied independently to
+ the partitioned table and to individual partitions. Changes made to
+ the partitioned table do not propagate to existing partitions, and
+ partitions created afterwards do not inherit the setting.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +857,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual (leaf) partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +876,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual (leaf) partitions.
+ </para>
</listitem>
</varlistentry>
@@ -770,6 +891,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
system column. As <literal>oid</literal> system columns cannot be
added anymore, this never has an effect.
</para>
+ <para>
+ For partitioned tables, this form behaves the same as for regular
+ tables; it has no effect on either the partitioned table or its
+ partitions. Specifying <literal>ONLY</literal> is allowed but has no
+ effect.
+ </para>
</listitem>
</varlistentry>
@@ -785,12 +912,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="tableam"/> for more information.
</para>
<para>
- When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ When applied to a partitioned table, there is no data to rewrite.
+ Partitions created afterwards will use the access method specified
+ on the partitioned table, if any, unless overridden by a
+ <literal>USING</literal> clause; otherwise they default to
+ <varname>default_table_access_method</varname>. Specifying
+ <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use
+ <varname>default_table_access_method</varname>. Specifying
+ <literal>ONLY</literal> is allowed but has no effect.
</para>
</listitem>
</varlistentry>
@@ -803,12 +933,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved. Existing
+ partitions are not affected, but partitions created afterwards with
+ <command>CREATE TABLE PARTITION OF</command> will use the specified
+ tablespace unless overridden by a <literal>TABLESPACE</literal> clause.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +965,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual (leaf) partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +994,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual (leaf) partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +1014,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ effect. It can be used on individual (leaf) partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +1032,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1043,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1059,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1077,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table, this form affects only the
+ partitioned table itself. Specifying <literal>ONLY</literal> is
+ allowed but has no effect, and this form cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1092,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table, this form affects only the
+ partitioned table itself. Specifying <literal>ONLY</literal> is
+ allowed but has no effect, and this form cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1108,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ For partitioned tables, this form can be applied independently to the
+ partitioned table and to individual partitions. Changing the owner of
+ the partitioned table does not affect existing partitions, and
+ partitions created afterwards do not inherit the new owner.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1171,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ For partitioned tables, this form can be applied independently to the
+ partitioned table and to individual partitions. Changing the replica
+ identity of the partitioned table does not affect existing partitions,
+ and partitions created afterwards do not inherit the setting.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1193,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ For partitioned tables, this form behaves the same as for regular
+ tables. Renaming a partitioned table, column, or constraint does not
+ propagate to partitions, and <literal>ONLY</literal> can be specified
+ but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1209,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ For partitioned tables, this form can be applied independently to the
+ partitioned table and to individual partitions. Moving the partitioned
+ table to another schema does not affect existing partitions, and
+ partitions created afterwards do not inherit the new schema.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1594,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
--
2.34.1
[text/x-patch] v2-0002-docs-Clarify-ALTER-TABLE-v1-edits.patch (16.8K, 4-v2-0002-docs-Clarify-ALTER-TABLE-v1-edits.patch)
download | inline diff:
From 19f0a7ec08bf6a95dd3af6742f7be7bf000acde1 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
Date: Wed, 7 Jan 2026 14:53:50 -0700
Subject: [PATCH 2/2] doc Clarify ALTER TABLE edits
---
doc/src/sgml/ref/alter_table.sgml | 144 +++++++++++++-----------------
1 file changed, 62 insertions(+), 82 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index b1190fae968..75a26087eb6 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -173,7 +173,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the column is added to the
- partitioned table definition and is implicitly present in all
+ partitioned table definition and is implicitly added to all
partitions. Specifying <literal>ONLY</literal> is not allowed, and
this command cannot be used on individual partitions.
</para>
@@ -232,9 +232,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the data type change is applied
- to the partitioned table definition and affects all partitions.
- Specifying <literal>ONLY</literal> is not allowed, and this command
- cannot be used on individual partitions.
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
</para>
</listitem>
</varlistentry>
@@ -251,10 +251,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the default value is propagated
- to all existing partitions unless <literal>ONLY</literal> is specified.
- New partitions created afterwards inherit the default from the
- partitioned table, but individual partitions may define a different
- default.
+ to all partitions unless <literal>ONLY</literal> is specified.
+ Newly created partitions inherit the default from the partitioned table
+ unless they define a different default.
</para>
</listitem>
</varlistentry>
@@ -289,8 +288,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
children can disallow nulls even if the parent allows them, but not
the other way around. It is also possible to apply
<literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
- <literal>ONLY</literal> the parent table, which does not affect the
- children.
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -313,10 +311,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the generation expression is
- propagated to all existing partitions unless <literal>ONLY</literal>
- is specified. New partitions created afterwards inherit the
- expression from the partitioned table, while individual partitions
- may define a different expression.
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified. Newly created partitions inherit the expression from the
+ partitioned table unless they define a different expression.
</para>
</listitem>
</varlistentry>
@@ -368,7 +365,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the identity property is defined
- on the partitioned table definition and applies to all partitions.
+ on the partitioned table and is applied to all partitions.
Specifying <literal>ONLY</literal> is not allowed, and these forms
cannot be used on individual partitions.
</para>
@@ -388,7 +385,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
When applied to a partitioned table, these forms alter the sequence
associated with the identity column on the partitioned table
- definition and affect all partitions. Specifying <literal>ONLY</literal>
+ and all individual partitions. Specifying <literal>ONLY</literal>
is not allowed, and these forms cannot be used on individual
partitions.
</para>
@@ -456,11 +453,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
<para>
- For partitioned tables, these forms can be applied independently to
- the partitioned table and to individual partitions. Changes made to
- the partitioned table do not propagate to existing partitions, and
- partitions created afterwards do not inherit the setting.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, these forms must be applied separately to
+ the partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit their attribute options from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -499,9 +495,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
When applied to a partitioned table, the storage setting is propagated
to all existing partitions unless <literal>ONLY</literal> is specified.
- New partitions created afterwards inherit the setting from the
- partitioned table, while individual partitions may use a different
- storage setting.
+ Newly created partitions inherit the setting from the partitioned table
+ unless they define a different storage setting.
</para>
</listitem>
</varlistentry>
@@ -534,12 +529,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
at the time of data insertion to determine the method to use.
</para>
<para>
- When applied to a partitioned table, the compression setting is applied
- to the partitioned table definition and does not propagate to existing
- partitions. New partitions created afterwards inherit the setting from
- the partitioned table, while individual partitions may use a different
- compression method. Specifying <literal>ONLY</literal> is allowed but has
- no effect.
+ When applied to a partitioned table, the compression method must be
+ applied separately to the partitioned table and/or to individual partitions.
+ Specifying <literal>ONLY</literal> is allowed but has no effect.
+ However, newly created partitions inherit the compression method from the
+ partitioned table unless they define a different compression method.
</para>
</listitem>
</varlistentry>
@@ -584,9 +578,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the constraint is added to the
- partitioned table and is automatically applied to all partitions.
- Specifying <literal>ONLY</literal> is not allowed. New partitions
- created afterwards inherit the constraint from the partitioned table.
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ Newly created partitions inherit constraints from the partitioned table.
</para>
</listitem>
</varlistentry>
@@ -627,7 +621,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
This form is not supported on partitioned tables, but can be used on
- individual (leaf) partitions.
+ individual partitions.
</para>
<note>
<para>
@@ -651,7 +645,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the constraint is altered on the
- partitioned table definition and the change applies to all partitions.
+ partitioned table definition is implicitly applied to all partitions.
Specifying <literal>ONLY</literal> is not allowed, and this command
cannot be used on individual partitions.
</para>
@@ -676,7 +670,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
These forms are not supported on partitioned tables, but can be used
- on individual (leaf) partitions.
+ on individual partitions.
</para>
</listitem>
</varlistentry>
@@ -717,9 +711,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
When applied to a partitioned table, the constraint is dropped from
all existing partitions unless <literal>ONLY</literal> is specified.
- New partitions created afterwards do not include the dropped
- constraint, while individual partitions may drop the constraint
- independently of the partitioned table.
+ Individual partitions may drop constraints independently of the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -793,11 +786,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
described above.
</para>
<para>
- For partitioned tables, these forms can be applied independently to
- the partitioned table and to individual partitions. Changes made to
- the partitioned table do not propagate to existing partitions, and
- partitions created afterwards do not inherit the setting.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, these forms must be applied separately to
+ the partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit the setting from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -815,11 +807,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
<para>
- For partitioned tables, these forms can be applied independently to
- the partitioned table and to individual partitions. Changes made to
- the partitioned table do not propagate to existing partitions, and
- partitions created afterwards do not inherit the setting.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, these forms must be applied separately to
+ the partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit the setting from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -837,11 +828,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
<para>
- For partitioned tables, these forms can be applied independently to
- the partitioned table and to individual partitions. Changes made to
- the partitioned table do not propagate to existing partitions, and
- partitions created afterwards do not inherit the setting.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, these forms must be applied separately to
+ the partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit the setting from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -859,7 +849,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
This form is not supported on partitioned tables, but can be used on
- individual (leaf) partitions.
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -878,7 +868,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
This form is not supported on partitioned tables, but can be used on
- individual (leaf) partitions.
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -891,12 +881,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
system column. As <literal>oid</literal> system columns cannot be
added anymore, this never has an effect.
</para>
- <para>
- For partitioned tables, this form behaves the same as for regular
- tables; it has no effect on either the partitioned table or its
- partitions. Specifying <literal>ONLY</literal> is allowed but has no
- effect.
- </para>
</listitem>
</varlistentry>
@@ -972,7 +956,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
This form is not supported on partitioned tables, but can be used on
- individual (leaf) partitions.
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -1001,7 +985,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
This form is not supported on partitioned tables, but can be used on
- individual (leaf) partitions.
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -1016,7 +1000,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, this form is accepted but has no
- effect. It can be used on individual (leaf) partitions, where it
+ effect. It can be used on individual partitions, where it
behaves as for a regular table.
</para>
</listitem>
@@ -1109,11 +1093,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or foreign table to the specified user.
</para>
<para>
- For partitioned tables, this form can be applied independently to the
- partitioned table and to individual partitions. Changing the owner of
- the partitioned table does not affect existing partitions, and
- partitions created afterwards do not inherit the new owner.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, this form must be applied separately to
+ the partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit their owner from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -1173,11 +1156,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</variablelist></para>
<para>
- For partitioned tables, this form can be applied independently to the
- partitioned table and to individual partitions. Changing the replica
- identity of the partitioned table does not affect existing partitions,
- and partitions created afterwards do not inherit the setting.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, this form must be applied independently to the
+ partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit the setting from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
@@ -1196,8 +1178,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
For partitioned tables, this form behaves the same as for regular
tables. Renaming a partitioned table, column, or constraint does not
- propagate to partitions, and <literal>ONLY</literal> can be specified
- but has no effect.
+ propagate to partitions.
</para>
</listitem>
</varlistentry>
@@ -1210,11 +1191,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraints, and sequences owned by table columns are moved as well.
</para>
<para>
- For partitioned tables, this form can be applied independently to the
- partitioned table and to individual partitions. Moving the partitioned
- table to another schema does not affect existing partitions, and
- partitions created afterwards do not inherit the new schema.
- Specifying <literal>ONLY</literal> is allowed but has no effect.
+ For partitioned tables, this form must be applied independently to the
+ partitioned table and/or to individual partitions.
+ Newly created partitions do not inherit their schema from the
+ partitioned table.
</para>
</listitem>
</varlistentry>
--
2.34.1
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: docs: clarify ALTER TABLE behavior on partitioned tables
In-Reply-To: <CAKFQuwbWNCX1M1e9_-3P9RVGo10huPjuqdL74FJ+-a5EW791KA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox