v40-0001-test-for-MERGE-PARTITION.no-cfbot
application/octet-stream
Filename: v40-0001-test-for-MERGE-PARTITION.no-cfbot
Type: application/octet-stream
Part: 0
From 4b7a5b7651eb79458139372eeebe55b0d84f49ea Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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