docs: clarify ALTER TABLE behavior on partitioned tables
| От | Chao Li |
|---|---|
| Тема | docs: clarify ALTER TABLE behavior on partitioned tables |
| Дата | |
| Msg-id | CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: docs: clarify ALTER TABLE behavior on partitioned tables
|
| Список | pgsql-hackers |
Hi Hackers,
* ENABLE/DISABLE ROW LEVEL SECURITY
* NO FORCE / FORCE ROW LEVEL SECURITY
This task is derived from [1], as “ALTER TABLE” is considered to exhibit “inconsistent” behaviors with partitioned tables. From the current documentation of “ALTER TABLE”, readers won’t get enough information to form a clear understanding of how these commands behave with partitioned tables, and this gap needs to be addressed.
I have spent several days verifying sub-commands of “ALTER TABLE” one by one against partitioned tables from the following points:
1) Does an action on a parent partitioned table propagate to child partitions?
2) Does a value set on a parent partitioned table get automatically inherited by newly created partitions?
3) Does “ONLY partitioned-table” work as the documentation states (preventing propagation)?
4) Can an action be performed on a partitioned table and its children independently?
From these four criteria, sub-commands fall into the following categories:
C1 - Sub-commands that can only be used with a partitioned table; ONLY will lead to an error; using them with a child partition will lead to an error.
* ADD COLUMN
* DROP COLUMN
* SET DATA TYPE
* DROP EXPRESSION
* ADD GENERATED AS IDENTITY
* ADD GENERATED
* DROP IDENTITY
* SET sequence_option
* RESTART
* ALTER CONSTRAINT
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.
* SET DEFAULT
* DROP DEFAULT
* SET EXPRESSION AS
* SET STORAGE
* DROP CONSTRAINT
* ENABLE/DISABLE [ REPLICA | ALWAYS] TRIGGER
C3 - Slightly different from C2: new partitions will not inherit the parent’s setting.
* SET STATISTICS
C4 - Sub-commands that can be used on a partitioned table and child partitions independently; actions on the parent will not propagate to children; children
can have different settings than the parent; new partitions will not inherit the parent’s setting; ONLY can be used but has no effect.
* SET/RESET (attribute_option = value)
* ENABLE/DISABLE [ REPLICA | ALWAYS] RULE* ENABLE/DISABLE ROW LEVEL SECURITY
* NO FORCE / FORCE ROW LEVEL SECURITY
* OWNER TO
* REPLICA IDENTITY
* SET SCHEMA
C5 - Slightly different from C4: new partitions will automatically inherit the parent’s setting.
* SET COMPRESSION
C6 - Slightly different from C2: ONLY cannot be used with a parent partitioned table, meaning the setting must be propagated to child partitions.
* ADD table_constraint
C7 - Sub-commands that cannot be used with a partitioned table, but can only be used with a leaf partition.
* ADD table_constraint_with_index
* ALTER CONSTRAINT … INHERIT / NO INHERIT
* CLUSTER ON
* SET WITHOUT CLUSTER
* SET { LOGGED | UNLOGGED }
* SET (storage_parameter)
C8 - Slightly different from C1: child partitions can set different values than the parent.
* VALIDATE CONSTRAINT
C9 - Slightly different from C2: if the parent has a value, then new partitions will inherit that value; otherwise, they use the default from the GUC.
* SET ACCESS METHOD
C10 - Sub-commands used with a parent partitioned table will NOT propagate to child partitions; but new partitions will automatically inherit the parent’s setting; partitions can be set to different values than the parent; ONLY can be used but has no effect.
* SET TABLESPACE
C11 - Sub-commands used with a parent partitioned table won’t fail but do nothing; using them with a leaf partition works as with a normal table.
* RESET (storage_parameter) # this appears questionable, because SET (storage_parameter) is not allowed on a partitioned table
C12 - Sub-commands that don’t support partitioned tables; neither parent partitioned tables nor child partitions.
* INHERIT parent_table
* NO INHERIT parent_table
C13 - Sub-commands that support only parent partitioned tables; ONLY can be used but has no effect; using them with a child partition will fail.
* OF type
* NOT OF
C14 - Sub-commands that treat partitioned tables, either parent partitioned tables or child partitions, as normal tables, so no propagation occurs; ONLY can be used but has no effect.
* RENAME
C15 - Sub-commands that operate on partitioned tables.
* ATTACH PARTITION
* DETACH PARTITION
* MERGE PARTITIONS
* SPLIT PARTITION
— End of categories —
With these categories, we can clearly see where “inconsistencies” exist, and measure whether future changes mitigate these “inconsistencies” or make them worse. Some categories include only one or two sub-commands; maybe they can be adjusted to other categories so that some categories are eliminated and the overall “inconsistency” situation is improved.
In this patch, I just want to add clarifications to the “ALTER TABLE” documentation, without changing any existing behaviors.
This patch is pretty massive. Although I have done a self-review, I may still have missed things. I know this patch is a challenge for reviewers, so I’m open to any suggestions to make it easier to review and commit, such as splitting it in some way.
Вложения
В списке pgsql-hackers по дате отправления: