On Wed, Jan 7, 2026 at 1:29 AM Chao Li <[email protected]> wrote:
> > > > > > > [1] > https://postgr.es/m/caeowx2nj71hy8r614hqr7vqhkbreo9aanpodpg0asqs74eo...@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.
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
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
