v40-0001-test-for-MERGE-PARTITION-TRIGGER.no-cfbot

From 65e30962d7d9adc72ded6844b418a8bed1865f1f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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