From 4b7a5b7651eb79458139372eeebe55b0d84f49ea Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 5 Jun 2025 20:21:51 +0800 Subject: [PATCH v40 1/1] test for MERGE PARTITION table property: (COMMENTS, COMPRESSION, DEFAULTS, GENERATED, STATISTICS, STORAGE) partition can differ from root partitioned table. When using ALTER TABLE ... MERGE PARTITIONS, the new partition's properties will not be inherited from to be merged partitions; instead, they will be directly copied from the root partitioned table. --- src/test/regress/expected/partition_merge.out | 50 +++++++++++++++++++ src/test/regress/sql/partition_merge.sql | 27 ++++++++++ 2 files changed, 77 insertions(+) diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index a24874798d8..e8428b5864c 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -8,6 +8,53 @@ SET search_path = partitions_merge_schema, public; -- -- BY RANGE partitioning -- +-- test merged new partition properties (COMMENTS, COMPRESSION, DEFAULTS, +-- GENERATED, STATISTICS, STORAGE) be same as merged partitioned table. +-- STATISTICS on to be merged partiton will be dropped. +create table pp ( + f1 int, f2 text storage main default 'pp', + f3 int, f4 date generated always as ('2022-01-01') stored) partition by range (abs(f1)); +create table pp_1 ( + f1 int, f2 text storage external compression pglz default 'pp_1', + f3 int, f4 date generated always as ('2022-01-02') stored) with (fillfactor=70); +create table pp_2 ( + f1 int, f2 text storage extended compression lz4 default 'pp_2', + f3 int, f4 date generated always as ('2022-01-03') stored) with (fillfactor=10); +alter table pp attach partition pp_1 for values from (-1) to (10); +alter table pp attach partition pp_2 for values from (10) to (20); +insert into pp(f1) values (-1), (1), (10), (11); +create statistics pp_stat (dependencies) on f1, f3 from pp; +create statistics pp_1_stat (dependencies) on f1, f3 from pp_1; +create statistics pp_2_stat (dependencies) on f1, f3 from pp_2; +comment on column pp_1.f1 is 'pp_1.f1'; +comment on column pp_2.f1 is 'pp_2 f1'; +comment on column pp.f1 is 'pp f1'; +\d+ pp_2 + Table "partitions_merge_schema.pp_2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+-------------------------------------------------+----------+--------------+------------- + f1 | integer | | | | plain | | pp_2 f1 + f2 | text | | | 'pp_2'::text | extended | | + f3 | integer | | | | plain | | + f4 | date | | | generated always as ('01-03-2022'::date) stored | plain | | +Partition of: pp FOR VALUES FROM (10) TO (20) +Partition constraint: ((abs(f1) IS NOT NULL) AND (abs(f1) >= 10) AND (abs(f1) < 20)) +Statistics objects: + "partitions_merge_schema.pp_2_stat" (dependencies) ON f1, f3 FROM pp_2 +Options: fillfactor=10 + +ALTER TABLE pp MERGE PARTITIONS (pp_1, pp_2) INTO pp_2; +\d+ pp_2 + Table "partitions_merge_schema.pp_2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+-------------------------------------------------+---------+--------------+------------- + f1 | integer | | | | plain | | + f2 | text | | | 'pp'::text | main | | + f3 | integer | | | | plain | | + f4 | date | | | generated always as ('01-01-2022'::date) stored | plain | | +Partition of: pp FOR VALUES FROM ('-1') TO (20) +Partition constraint: ((abs(f1) IS NOT NULL) AND (abs(f1) >= '-1'::integer) AND (abs(f1) < 20)) + -- -- Test for error codes -- @@ -1000,4 +1047,7 @@ DROP TABLE t; RESET search_path; -- DROP SCHEMA partitions_merge_schema; +ERROR: cannot drop schema partitions_merge_schema because other objects depend on it +DETAIL: table partitions_merge_schema.pp depends on schema partitions_merge_schema +HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP SCHEMA partitions_merge_schema2; diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index c53ac5a1999..861bd186b56 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -11,6 +11,33 @@ SET search_path = partitions_merge_schema, public; -- BY RANGE partitioning -- +-- test merged new partition properties (COMMENTS, COMPRESSION, DEFAULTS, +-- GENERATED, STATISTICS, STORAGE) be same as merged partitioned table. +-- STATISTICS on to be merged partiton will be dropped. +create table pp ( + f1 int, f2 text storage main default 'pp', + f3 int, f4 date generated always as ('2022-01-01') stored) partition by range (abs(f1)); +create table pp_1 ( + f1 int, f2 text storage external compression pglz default 'pp_1', + f3 int, f4 date generated always as ('2022-01-02') stored) with (fillfactor=70); +create table pp_2 ( + f1 int, f2 text storage extended compression lz4 default 'pp_2', + f3 int, f4 date generated always as ('2022-01-03') stored) with (fillfactor=10); + +alter table pp attach partition pp_1 for values from (-1) to (10); +alter table pp attach partition pp_2 for values from (10) to (20); +insert into pp(f1) values (-1), (1), (10), (11); + +create statistics pp_stat (dependencies) on f1, f3 from pp; +create statistics pp_1_stat (dependencies) on f1, f3 from pp_1; +create statistics pp_2_stat (dependencies) on f1, f3 from pp_2; +comment on column pp_1.f1 is 'pp_1.f1'; +comment on column pp_2.f1 is 'pp_2 f1'; +comment on column pp.f1 is 'pp f1'; +\d+ pp_2 +ALTER TABLE pp MERGE PARTITIONS (pp_1, pp_2) INTO pp_2; +\d+ pp_2 + -- -- Test for error codes -- -- 2.34.1