Re: docs: clarify ALTER TABLE behavior on partitioned tables - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: docs: clarify ALTER TABLE behavior on partitioned tables |
| Date | |
| Msg-id | CAKFQuwZwAuO3qXEeqxwV6vM+89BkB-aSkcXqDGG8e_xBy_Q3Xw@mail.gmail.com Whole thread Raw |
| In response to | Re: docs: clarify ALTER TABLE behavior on partitioned tables (Zsolt Parragi <zsolt.parragi@percona.com>) |
| Responses |
Re: docs: clarify ALTER TABLE behavior on partitioned tables
Re: docs: clarify ALTER TABLE behavior on partitioned tables |
| List | pgsql-hackers |
Thank you for the review!
On Fri, Jan 23, 2026 at 3:07 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition is implicitly applied to all partitions.
an "and" is missing here (definition and is)
Correct. But I'd go with:
...the constraint is altered on the partitioned table and implicitly applied to all partitions.
+ When applied to a partitioned table, partition columns constraints
+ are implicitly renamed and specifying <literal>ONLY</literal>
is not allowed.
+ </para>
"partition columns constraints" - that seems like a strange/unclear
wording to me. maybe ", the partition's column constraints are ... " ?
This is just wrong - only is not permitted for either columns or constraints. Only cannot be implicit if cascading is allowed.
The unclear wording noted is just missing an "and" - of the three things that can be renamed (relation name, column name, constraint name) only these two apply.
"the partition columns and constraints..."
<para>
When applied to a partitioned table, partition columns and constraints
are implicitly renamed.
Specifying <literal>ONLY</literal> is not allowed, and this command
cannot be used on individual partitions.
</para>
<para>
For inheritance setups, index-based constraints are always considered
independent. ~~Dependent columns and constraints are implicitly renamed
and specifying <literal>ONLY</literal> is not allowed.~~
</para>
When applied to a partitioned table, partition columns and constraints
are implicitly renamed.
Specifying <literal>ONLY</literal> is not allowed, and this command
cannot be used on individual partitions.
</para>
<para>
For inheritance setups, index-based constraints are always considered
independent. ~~Dependent columns and constraints are implicitly renamed
and specifying <literal>ONLY</literal> is not allowed.~~
</para>
The last sentence is redundant with the notes though, I'd remove it as noted above:
<para>
For inheritance setups, the behavior described for partitioned tables applies
only to the dependent column(s) on the descendant table(s). It is always
allowed to target a descendant table with column altering commands on independent
columns.
</para>
For inheritance setups, the behavior described for partitioned tables applies
only to the dependent column(s) on the descendant table(s). It is always
allowed to target a descendant table with column altering commands on independent
columns.
</para>
But that note should have "dependent constraints" added to it.
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
"When applied to a partitioned table, <literal>ONLY</literal> is
implicit and ..." (at multiple places, this is an example)
I've grown unfond of my suggested wording here during reviews too. But because it's too wordy and a bit redundant.
"When applied to a partitioned table ONLY is implicit, however, this command can be used on individual partitions."
has a better symmetry with:
Specifying <literal>ONLY</literal> is not allowed, and this command cannot be used on individual partitions.
"A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)
never removes any descendant columns, but instead marks them as
independently defined rather than inherited."
This part is now undocumented, it was only mentioned in this paragraph.
True, it's left implied instead of explicitly stated. Any column that exists on a child but not the parent is by definition "independently defined". So if either ONLY is supplied or the rules for cascading delete are not met the result is children with independently defined columns with that name. The original note was wrong anyway for the two-parent case - the second parent prevents the marking as independent when the first parent's column is dropped.
> C2 - Sub-commands where using them with a partitioned table will automatically propagate to child partitions; ONLY prevents propagation; new partitions inherit the parent’s new setting; and child partitions can be set to different values than the parent.
The documentation of this group is inconsistent.
DROP CONSTRAINT mentions that individual partitions can be dropped separately:
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Individual partitions may drop constraints independently of the
+ partitioned table.
But most of the sub commands in the C2 group leave the last sentence
out, and also the C7 (ADD table_constraint)
I didn't try and verify this dynamic or keep to it - though am on board with considering changes that do so and remain accurate.
Also, isn't DROP CONSTRAINT on a partition limited to constraints
defined on that partition? So it would be better to say "may drop
constraints defined directly on that individual partition
independently".
"When applied to a partitioned table, dependent constraints are dropped from ... is specified." should suffice.
I'd be fine leaving out the entire "Individual partitions may drop..." business with that wording. It implies partitions may have independent constraints which by extension may be targeted.
For Add Constraint - mention dependent constraints
"When applied to a partitioned table, the constraint is added to the partitioned table and dependent constraints are added to all partitions."
Which implies independent ones may exist and the logic for drop constraint then follows.
(We should explain what happens if a partition already has an independent constraint of the given name as that would be relevant here.)
CREATE TABLE parent (id int, val int) PARTITION BY RANGE (id);
ALTER TABLE parent ADD CONSTRAINT val_positive CHECK (val > 0);
CREATE TABLE child PARTITION OF parent FOR VALUES FROM (1) TO (100);
ALTER TABLE child DROP CONSTRAINT val_positive;
-- ERROR: cannot drop inherited constraint "val_positive" of relation "child"
+ When a new partition is created, it generally inherits the current
+ definition-level properties of the parent partitioned table.
Maybe something like the following?
When a new partition is created, it generally inherits structural
properties of the parent partitioned table, such as column
definitions, constraints, and storage settings.
To be more explicit about what's inherited, and not only focus on
what's not. (The commit message also says that the change describes
both what's inherited and what's not inherited)
I concur with the premise but how about:
When a partition is created, it inherits many of the properties
of the parent table. However, properties related to ownership,
schema, replica identity, row-level security configuration,
per-attribute statistics targets, and per-attribute options
are not inherited.
of the parent table. However, properties related to ownership,
schema, replica identity, row-level security configuration,
per-attribute statistics targets, and per-attribute options
are not inherited.
"new" is superfluous on this page.
"definition-level" are the only kind of properties that exist - I'm not being wordy thinking people might believe properties includes data.
"parent" suffices as well.
We did all the work to identify things - use "however" instead of "in particular" to give us credit for the work.
Even if a property is explicitly set for the partition it isn't "inherited" - the partition has its own independent value that in a rare case might happen to match the parent at the time of creation. (i.e., remove automatically and 'not inherited unless')
I'm not that inclined to mention the inclusion list. The general premise of assuming inherited unless told otherwise works fine here; minimal future-proofing.
David J.
pgsql-hackers by date: