From 65e30962d7d9adc72ded6844b418a8bed1865f1f Mon Sep 17 00:00:00 2001 From: jian he Date: Fri, 6 Jun 2025 10:15:14 +0800 Subject: [PATCH v40 1/1] test for MERGE PARTITION TRIGGER table property: (TRIGGER) 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 | 47 +++++++++++++++++-- src/test/regress/sql/partition_merge.sql | 14 ++++-- 2 files changed, 54 insertions(+), 7 deletions(-) diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index e8428b5864c..d8ac1796fb6 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -430,16 +430,55 @@ CREATE TRIGGER salespeople_after_insert_row_trigger ON salespeople FOR EACH ROW EXECUTE PROCEDURE after_insert_row_trigger('salespeople'); --- 2 triggers should fire here (row + statement): +CREATE TRIGGER salespeople_after_insert_row_trigger1020 + AFTER INSERT + ON salespeople10_20 + FOR EACH ROW + EXECUTE PROCEDURE after_insert_row_trigger('salespeople10_20'); +-- 3 triggers should fire here (row + statement): INSERT INTO salespeople VALUES (10, 'May'); NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW +NOTICE: trigger(salespeople10_20) called: action = INSERT, when = AFTER, level = ROW NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT --- 1 trigger should fire here (row): +-- 2 trigger should fire here (row): INSERT INTO salespeople10_20 VALUES (19, 'Ivanov'); NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW +NOTICE: trigger(salespeople10_20) called: action = INSERT, when = AFTER, level = ROW ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; +\d+ salespeople + Partitioned table "partitions_merge_schema.salespeople" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +------------------+-----------------------+-----------+----------+---------+----------+--------------+------------- + salesperson_id | integer | | not null | | plain | | + salesperson_name | character varying(30) | | | | extended | | +Partition key: RANGE (salesperson_id) +Indexes: + "salespeople_pkey" PRIMARY KEY, btree (salesperson_id) +Not-null constraints: + "salespeople_salesperson_id_not_null" NOT NULL "salesperson_id" +Triggers: + salespeople_after_insert_row_trigger AFTER INSERT ON salespeople FOR EACH ROW EXECUTE FUNCTION after_insert_row_trigger('salespeople') + salespeople_after_insert_statement_trigger AFTER INSERT ON salespeople FOR EACH STATEMENT EXECUTE FUNCTION after_insert_row_trigger('salespeople') +Partitions: salespeople01_10 FOR VALUES FROM (1) TO (10), + salespeople10_40 FOR VALUES FROM (10) TO (40) + +\d+ salespeople10_40 + Table "partitions_merge_schema.salespeople10_40" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +------------------+-----------------------+-----------+----------+---------+----------+--------------+------------- + salesperson_id | integer | | not null | | plain | | + salesperson_name | character varying(30) | | | | extended | | +Partition of: salespeople FOR VALUES FROM (10) TO (40) +Partition constraint: ((salesperson_id IS NOT NULL) AND (salesperson_id >= 10) AND (salesperson_id < 40)) +Indexes: + "salespeople10_40_pkey" PRIMARY KEY, btree (salesperson_id) +Not-null constraints: + "salespeople_salesperson_id_not_null" NOT NULL "salesperson_id" (inherited) +Triggers: + salespeople_after_insert_row_trigger AFTER INSERT ON salespeople10_40 FOR EACH ROW EXECUTE FUNCTION after_insert_row_trigger('salespeople'), ON TABLE salespeople + -- 2 triggers should fire here (row + statement): -INSERT INTO salespeople VALUES (20, 'Smirnoff'); +INSERT INTO salespeople VALUES (18, 'Smirnoff'); NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT -- 1 trigger should fire here (row): @@ -456,7 +495,7 @@ SELECT * FROM salespeople10_40; ----------------+------------------ 10 | May 19 | Ivanov - 20 | Smirnoff + 18 | Smirnoff 30 | Ford (4 rows) diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index 861bd186b56..7637e35ee89 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -260,15 +260,23 @@ CREATE TRIGGER salespeople_after_insert_row_trigger FOR EACH ROW EXECUTE PROCEDURE after_insert_row_trigger('salespeople'); --- 2 triggers should fire here (row + statement): +CREATE TRIGGER salespeople_after_insert_row_trigger1020 + AFTER INSERT + ON salespeople10_20 + FOR EACH ROW + EXECUTE PROCEDURE after_insert_row_trigger('salespeople10_20'); + +-- 3 triggers should fire here (row + statement): INSERT INTO salespeople VALUES (10, 'May'); --- 1 trigger should fire here (row): +-- 2 trigger should fire here (row): INSERT INTO salespeople10_20 VALUES (19, 'Ivanov'); ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; +\d+ salespeople +\d+ salespeople10_40 -- 2 triggers should fire here (row + statement): -INSERT INTO salespeople VALUES (20, 'Smirnoff'); +INSERT INTO salespeople VALUES (18, 'Smirnoff'); -- 1 trigger should fire here (row): INSERT INTO salespeople10_40 VALUES (30, 'Ford'); -- 2.34.1