From 0f2d517d28042a16d3ebd89d18327f0cfc7525c5 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Thu, 2 May 2024 14:43:56 +0900 Subject: [PATCH v2 4/4] Recurse ALTER TABLE SET LOGGED/UNLOGGED for partitioned tables This commit recurses the command of $subject to apply on all the partitions of a partitioned table, except if ONLY is used. Regression tests are expanded for both cases, with multiple levels of partitioning. --- src/backend/commands/tablecmds.c | 2 + src/test/regress/expected/alter_table.out | 91 +++++++++++++++++++---- src/test/regress/sql/alter_table.sql | 15 ++++ doc/src/sgml/ref/alter_table.sgml | 6 +- 4 files changed, 97 insertions(+), 17 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 8854e31f14..8f18b7aa39 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5075,6 +5075,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change persistence setting twice"))); ATPrepSetPersistence(tab, rel, true); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); pass = AT_PASS_MISC; break; case AT_SetUnLogged: /* SET UNLOGGED */ @@ -5084,6 +5085,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change persistence setting twice"))); ATPrepSetPersistence(tab, rel, false); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); pass = AT_PASS_MISC; break; case AT_DropOids: /* SET WITHOUT OIDS */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 5071e7d963..8ec5c668c8 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3665,22 +3665,85 @@ CREATE TABLE logged_part_2_3 PARTITION OF logged_part_2 FOR VALUES IN (3); -- unlogged, inherited CREATE LOGGED TABLE logged_part_2_4 PARTITION OF logged_part_2 FOR VALUES IN (4); -- logged, not inherited +-- Partitions of partitions +CREATE TABLE logged_part_2_56 PARTITION OF logged_part_2 + FOR VALUES IN (5, 6) PARTITION BY LIST(f1); +CREATE TABLE logged_part_2_5 PARTITION OF logged_part_2_56 + FOR VALUES IN (5); -- unlogged, inherited +CREATE LOGGED TABLE logged_part_2_6 PARTITION OF logged_part_2_56 + FOR VALUES IN (6); -- logged, not inherited SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part_2' ORDER BY relname; - relname | relpersistence -----------------------+---------------- - logged_part_2 | u - logged_part_2_1 | p - logged_part_2_1_pkey | p - logged_part_2_2 | u - logged_part_2_2_pkey | u - logged_part_2_3 | u - logged_part_2_3_pkey | u - logged_part_2_4 | p - logged_part_2_4_pkey | p - logged_part_2_f1_seq | u - logged_part_2_pkey | u -(11 rows) + relname | relpersistence +-----------------------+---------------- + logged_part_2 | u + logged_part_2_1 | u + logged_part_2_1_pkey | u + logged_part_2_2 | u + logged_part_2_2_pkey | u + logged_part_2_3 | u + logged_part_2_3_pkey | u + logged_part_2_4 | p + logged_part_2_4_pkey | p + logged_part_2_5 | u + logged_part_2_56 | u + logged_part_2_56_pkey | u + logged_part_2_5_pkey | u + logged_part_2_6 | p + logged_part_2_6_pkey | p + logged_part_2_f1_seq | u + logged_part_2_pkey | u +(17 rows) + +-- All partitions are logged. +ALTER TABLE logged_part_2 SET LOGGED; +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part_2' + ORDER BY relname; + relname | relpersistence +-----------------------+---------------- + logged_part_2 | p + logged_part_2_1 | p + logged_part_2_1_pkey | p + logged_part_2_2 | p + logged_part_2_2_pkey | p + logged_part_2_3 | p + logged_part_2_3_pkey | p + logged_part_2_4 | p + logged_part_2_4_pkey | p + logged_part_2_5 | p + logged_part_2_56 | p + logged_part_2_56_pkey | p + logged_part_2_5_pkey | p + logged_part_2_6 | p + logged_part_2_6_pkey | p + logged_part_2_f1_seq | p + logged_part_2_pkey | p +(17 rows) + +-- Only the partitioned partition is unlogged. +ALTER TABLE ONLY logged_part_2_56 SET UNLOGGED; +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part_2' + ORDER BY relname; + relname | relpersistence +-----------------------+---------------- + logged_part_2 | p + logged_part_2_1 | p + logged_part_2_1_pkey | p + logged_part_2_2 | p + logged_part_2_2_pkey | p + logged_part_2_3 | p + logged_part_2_3_pkey | p + logged_part_2_4 | p + logged_part_2_4_pkey | p + logged_part_2_5 | p + logged_part_2_56 | u + logged_part_2_56_pkey | u + logged_part_2_5_pkey | p + logged_part_2_6 | p + logged_part_2_6_pkey | p + logged_part_2_f1_seq | p + logged_part_2_pkey | p +(17 rows) DROP TABLE logged_part_3; DROP TABLE logged_part_2; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 30aa62d256..3393c67b9c 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2290,6 +2290,21 @@ CREATE TABLE logged_part_2_3 PARTITION OF logged_part_2 FOR VALUES IN (3); -- unlogged, inherited CREATE LOGGED TABLE logged_part_2_4 PARTITION OF logged_part_2 FOR VALUES IN (4); -- logged, not inherited +-- Partitions of partitions +CREATE TABLE logged_part_2_56 PARTITION OF logged_part_2 + FOR VALUES IN (5, 6) PARTITION BY LIST(f1); +CREATE TABLE logged_part_2_5 PARTITION OF logged_part_2_56 + FOR VALUES IN (5); -- unlogged, inherited +CREATE LOGGED TABLE logged_part_2_6 PARTITION OF logged_part_2_56 + FOR VALUES IN (6); -- logged, not inherited +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part_2' + ORDER BY relname; +-- All partitions are logged. +ALTER TABLE logged_part_2 SET LOGGED; +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part_2' + ORDER BY relname; +-- Only the partitioned partition is unlogged. +ALTER TABLE ONLY logged_part_2_56 SET UNLOGGED; SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part_2' ORDER BY relname; DROP TABLE logged_part_3; diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 7937194462..79827796cc 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -805,9 +805,9 @@ WITH ( MODULUS numeric_literal, REM - Setting this property for a partitioned table has no direct effect, - because such tables have no storage of their own, but the configured - value will be inherited by newly-created partitions. + Setting this property on a partitioned table updates any partitions + attached to it, unless ONLY is specified. The + configured value is inherited by newly-created partitions. -- 2.43.0