You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/02/13 01:05:54 UTC

(cloudberry) 08/11: Fail the SplitUpdate if table's any child has update triggers (#17227)

This is an automated email from the ASF dual-hosted git repository.

maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git

commit 13c72534bb7662627c67ea9cc26a8a94edabd73e
Author: Adam Lee <ad...@gmail.com>
AuthorDate: Wed Mar 27 16:37:52 2024 +0800

    Fail the SplitUpdate if table's any child has update triggers (#17227)
    
    The SplitUpdate is supposed to fail if any child has update triggers,
    but Greenplum with ORCA skipped the check on the table's children while
    merging PostgreSQL 12.
    
    This commit gets the check back.
---
 src/backend/cdb/cdbpath.c                 |   2 +-
 src/backend/gpopt/gpdbwrappers.cpp        |   2 +-
 src/backend/utils/cache/lsyscache.c       | 101 ++++++++++--------------------
 src/include/utils/lsyscache.h             |   3 +-
 src/test/regress/expected/triggers_gp.out |  45 +++++++++++--
 src/test/regress/sql/triggers_gp.sql      |  39 ++++++++++--
 6 files changed, 110 insertions(+), 82 deletions(-)

diff --git a/src/backend/cdb/cdbpath.c b/src/backend/cdb/cdbpath.c
index 6f24d77bd0..b24445b8e4 100644
--- a/src/backend/cdb/cdbpath.c
+++ b/src/backend/cdb/cdbpath.c
@@ -2864,7 +2864,7 @@ make_splitupdate_path(PlannerInfo *root, Path *subpath, Index rti)
 	 * So an update trigger is not allowed when updating the
 	 * distribution key.
 	 */
-	if (has_update_triggers(rte->relid))
+	if (has_update_triggers(rte->relid, false))
 		ereport(ERROR,
 				(errcode(ERRCODE_GP_FEATURE_NOT_YET),
 				 errmsg("UPDATE on distributed key column not allowed on relation with update triggers")));
diff --git a/src/backend/gpopt/gpdbwrappers.cpp b/src/backend/gpopt/gpdbwrappers.cpp
index ecf9f03d26..aa7e1d944b 100644
--- a/src/backend/gpopt/gpdbwrappers.cpp
+++ b/src/backend/gpopt/gpdbwrappers.cpp
@@ -2168,7 +2168,7 @@ gpdb::HasUpdateTriggers(Oid relid)
 {
 	GP_WRAP_START;
 	{
-		return has_update_triggers(relid);
+		return has_update_triggers(relid, true);
 	}
 	GP_WRAP_END;
 	return false;
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index cb2cd4112e..5d80ec0193 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1744,15 +1744,22 @@ get_oprjoin(Oid opno)
 /*				---------- TRIGGER CACHE ----------					 */
 
 
-/* Does table have update triggers? */
+/*
+ * Does table have update triggers?
+ *
+ * From Greenplum 7, the Postgres planner expands partitioned table's children,
+ * including directly and indirectly, and generates a plan for each child table,
+ * but ORCA doesn't do that.
+ *
+ * So having an extra parameter including_children only for ORCA.
+ */
 bool
-has_update_triggers(Oid relid)
+has_update_triggers(Oid relid, bool including_children)
 {
 	Relation	relation;
 	bool		result = false;
 
-	/* Assume the caller already holds a suitable lock. */
-	relation = table_open(relid, NoLock);
+	relation = RelationIdGetRelation(relid);
 
 	if (relation->rd_rel->relhastriggers)
 	{
@@ -1769,15 +1776,33 @@ has_update_triggers(Oid relid)
 				found = trigger_enabled(trigger.tgoid) &&
 					(get_trigger_type(trigger.tgoid) & TRIGGER_TYPE_UPDATE) == TRIGGER_TYPE_UPDATE;
 				if (found)
+				{
+					result = true;
 					break;
+				}
 			}
 		}
+	}
 
-		/* GPDB_96_MERGE_FIXME: Why is this not allowed? */
-		if (found || child_triggers(relation->rd_id, TRIGGER_TYPE_UPDATE))
-			result = true;
+	if (including_children && !result && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		List	   *partitions = find_inheritance_children(relid, NoLock);
+		ListCell   *lc;
+
+		foreach(lc, partitions)
+		{
+			Oid			partrelid = lfirst_oid(lc);
+			if (has_update_triggers(partrelid, true))
+			{
+				result = true;
+				break;
+			}
+		}
+
+		list_free(partitions);
 	}
-	table_close(relation, NoLock);
+
+	RelationClose(relation);
 
 	return result;
 }
@@ -4657,66 +4682,6 @@ child_distribution_mismatch(Relation rel)
 	return false;
 }
 
-/*
- *  child_triggers
- *  Return true if the table is partitioned and any of the child partitions
- *  have a trigger of the given type.
- */
-bool
-child_triggers(Oid relationId, int32 triggerType)
-{
-/* GPDB_12_MERGE_FIXME */
-	return false;
-#if 0
-	Assert(InvalidOid != relationId);
-	if (PART_STATUS_NONE == rel_part_status(relationId))
-	{
-		/* not a partitioned table */
-		return false;
-	}
-
-	List *childOids = find_all_inheritors(relationId, NoLock, NULL);
-	ListCell *lc;
-
-	bool found = false;
-	foreach (lc, childOids)
-	{
-		Oid oidChild = lfirst_oid(lc);
-		Relation relChild = RelationIdGetRelation(oidChild);
-		Assert(NULL != relChild);
-
-		if (relChild->rd_rel->relhastriggers && NULL == relChild->trigdesc)
-		{
-			RelationBuildTriggers(relChild);
-			if (NULL == relChild->trigdesc)
-			{
-				relChild->rd_rel->relhastriggers = false;
-			}
-		}
-
-		if (relChild->rd_rel->relhastriggers)
-		{
-			for (int i = 0; i < relChild->trigdesc->numtriggers && !found; i++)
-			{
-				Trigger trigger = relChild->trigdesc->triggers[i];
-				found = trigger_enabled(trigger.tgoid) &&
-						(get_trigger_type(trigger.tgoid) & triggerType) == triggerType;
-			}
-		}
-
-		RelationClose(relChild);
-		if (found)
-		{
-			break;
-		}
-	}
-
-	list_free(childOids);
-	
-	/* no child triggers matching the given type */
-	return found;
-#endif
-}
 
 /*
  * get_range_multirange
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 0ff1afde4e..0822b1ae49 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -140,7 +140,7 @@ extern Oid	get_commutator(Oid opno);
 extern Oid	get_negator(Oid opno);
 extern RegProcedure get_oprrest(Oid opno);
 extern RegProcedure get_oprjoin(Oid opno);
-extern bool has_update_triggers(Oid relid);
+extern bool has_update_triggers(Oid relid, bool including_children);
 extern int32 get_trigger_type(Oid triggerid);
 extern bool trigger_enabled(Oid triggerid);
 extern char *get_func_name(Oid funcid);
@@ -251,7 +251,6 @@ Oid get_check_constraint_relid(Oid oidCheckconstraint);
 extern bool has_subclass_slow(Oid relationId);
 extern GpPolicy *relation_policy(Relation rel);
 extern bool child_distribution_mismatch(Relation rel);
-extern bool child_triggers(Oid relationId, int32 triggerType);
 
 extern bool get_cast_func(Oid oidSrc, Oid oidDest, bool *is_binary_coercible, Oid *oidCastFunc, CoercionPathType *pathtype);
 
diff --git a/src/test/regress/expected/triggers_gp.out b/src/test/regress/expected/triggers_gp.out
index 5899327ea3..2ee4caead9 100644
--- a/src/test/regress/expected/triggers_gp.out
+++ b/src/test/regress/expected/triggers_gp.out
@@ -71,7 +71,11 @@ create table parted_trig2 partition of parted_trig for values in (2);
 NOTICE:  table has parent, setting distribution columns to match parent table
 create table parted_trig3 partition of parted_trig for values in (3);
 NOTICE:  table has parent, setting distribution columns to match parent table
-create table parted_trig4 partition of parted_trig for values in (4);
+create table parted_trig4 partition of parted_trig for values in (4,5) partition by list (partkey);
+NOTICE:  table has parent, setting distribution columns to match parent table
+create table parted_trig4_1 partition of parted_trig4 for values in (4);
+NOTICE:  table has parent, setting distribution columns to match parent table
+create table parted_trig4_2 partition of parted_trig4 for values in (5);
 NOTICE:  table has parent, setting distribution columns to match parent table
 /* Could create similar structure with this legacy GPDB syntax:
 create table parted_trig (partkey int, nonkey int, distkey int)
@@ -80,8 +84,6 @@ create table parted_trig (partkey int, nonkey int, distkey int)
 */
 create trigger trig_ins_after after insert on parted_trig
   for each row execute procedure insert_notice_trig();
-create trigger trig_upd_after after update on parted_trig
-  for each row execute procedure update_notice_trig();
 create trigger trig_del_after after delete on parted_trig
   for each row execute procedure delete_notice_trig();
 -- Inserts. Should fire the INSERT trigger.
@@ -89,19 +91,48 @@ insert into parted_trig values (1, 1, 1);
 NOTICE:  insert trigger fired on parted_trig1 for INSERT  (seg1 127.0.0.1:40001 pid=10560)
 insert into parted_trig values (2, 2, 2);
 NOTICE:  insert trigger fired on parted_trig2 for INSERT  (seg0 127.0.0.1:40000 pid=10559)
+insert into parted_trig values (5, 5, 5);
+NOTICE:  insert trigger fired on parted_trig4_2 for INSERT  (seg2 127.0.0.1:40002 pid=250082)
+-- Have an UPDATE trigger on the middle level partition.
+create trigger trig_upd_after after update on parted_trig4
+  for each row execute procedure update_notice_trig();
+-- Update distribution key column on each level partition. Throws an error, currently.
+update parted_trig set distkey = 4 where distkey = 5;
+update parted_trig4 set distkey = 4 where distkey = 5;
+ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
+update parted_trig4_1 set distkey = 4 where distkey = 5;
+ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
+drop trigger trig_upd_after on parted_trig4;
+-- Have an UPDATE trigger on a leaf partition.
+create trigger trig_upd_after after update on parted_trig4_1
+  for each row execute procedure update_notice_trig();
+-- Update distribution key column on each level partition. Throws an error, currently.
+update parted_trig set distkey = 3 where distkey = 4;
+update parted_trig4 set distkey = 3 where distkey = 4;
+ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
+update parted_trig4_1 set distkey = 3 where distkey = 4;
+ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
+drop trigger trig_upd_after on parted_trig4_1;
+-- Have an UPDATE trigger on the top level partition.
+create trigger trig_upd_after after update on parted_trig
+  for each row execute procedure update_notice_trig();
 -- Update non-key column. Should fire the UPDATE trigger.
 update parted_trig set nonkey = 3 where nonkey = 1;
 NOTICE:  update trigger fired on parted_trig1 for UPDATE  (seg1 127.0.0.1:40001 pid=10560)
--- Update distribution key column. Throws an error, currently.
+-- Update distribution key column on each level partition. Throws an error, currently.
 update parted_trig set distkey = 3 where distkey = 1;
 ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
+update parted_trig4 set distkey = 3 where distkey = 1;
+ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
+update parted_trig4_1 set distkey = 3 where distkey = 1;
+ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
 -- Update partitioning key column. Should fire the DELETE+INSERT triggers,
 -- like in PostgreSQL.
 update parted_trig set partkey = 3 where partkey = 1;
 NOTICE:  delete trigger fired on parted_trig1 for DELETE  (seg1 127.0.0.1:40001 pid=10560)
 NOTICE:  insert trigger fired on parted_trig3 for INSERT  (seg1 127.0.0.1:40001 pid=10560)
 -- Update everything in one statement. Throws an error, currently, because
--- updating the distributon key is not allowed.
+-- updating the distribution key is not allowed.
 update parted_trig set partkey = partkey + 1, distkey = distkey + 1;
 ERROR:  UPDATE on distributed key column not allowed on relation with update triggers
 -- Should fire the DELETE trigger.
@@ -128,7 +159,11 @@ SET gp_enable_statement_trigger = off;
 -- Currently disabled.
 --
 create table trigtest_ao(a int) using ao_row;
+NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
+HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
 create table trigtest_co(a int) using ao_column;
+NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
+HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
 create trigger trig_ao after insert on trigtest_ao for each row execute function insert_notice_trig();
 create trigger trig_co after insert on trigtest_co for each row execute function insert_notice_trig();
 insert into trigtest_ao values(1);
diff --git a/src/test/regress/sql/triggers_gp.sql b/src/test/regress/sql/triggers_gp.sql
index f9761e632f..46dacfa475 100644
--- a/src/test/regress/sql/triggers_gp.sql
+++ b/src/test/regress/sql/triggers_gp.sql
@@ -74,7 +74,9 @@ create table parted_trig (partkey int, nonkey int, distkey int)
 create table parted_trig1 partition of parted_trig for values in (1);
 create table parted_trig2 partition of parted_trig for values in (2);
 create table parted_trig3 partition of parted_trig for values in (3);
-create table parted_trig4 partition of parted_trig for values in (4);
+create table parted_trig4 partition of parted_trig for values in (4,5) partition by list (partkey);
+create table parted_trig4_1 partition of parted_trig4 for values in (4);
+create table parted_trig4_2 partition of parted_trig4 for values in (5);
 
 /* Could create similar structure with this legacy GPDB syntax:
 create table parted_trig (partkey int, nonkey int, distkey int)
@@ -84,27 +86,54 @@ create table parted_trig (partkey int, nonkey int, distkey int)
 
 create trigger trig_ins_after after insert on parted_trig
   for each row execute procedure insert_notice_trig();
-create trigger trig_upd_after after update on parted_trig
-  for each row execute procedure update_notice_trig();
 create trigger trig_del_after after delete on parted_trig
   for each row execute procedure delete_notice_trig();
 
 -- Inserts. Should fire the INSERT trigger.
 insert into parted_trig values (1, 1, 1);
 insert into parted_trig values (2, 2, 2);
+insert into parted_trig values (5, 5, 5);
+
+-- Have an UPDATE trigger on the middle level partition.
+create trigger trig_upd_after after update on parted_trig4
+  for each row execute procedure update_notice_trig();
+
+-- Update distribution key column on each level partition. Throws an error, currently.
+update parted_trig set distkey = 4 where distkey = 5;
+update parted_trig4 set distkey = 4 where distkey = 5;
+update parted_trig4_1 set distkey = 4 where distkey = 5;
+
+drop trigger trig_upd_after on parted_trig4;
+
+-- Have an UPDATE trigger on a leaf partition.
+create trigger trig_upd_after after update on parted_trig4_1
+  for each row execute procedure update_notice_trig();
+
+-- Update distribution key column on each level partition. Throws an error, currently.
+update parted_trig set distkey = 3 where distkey = 4;
+update parted_trig4 set distkey = 3 where distkey = 4;
+update parted_trig4_1 set distkey = 3 where distkey = 4;
+
+drop trigger trig_upd_after on parted_trig4_1;
+
+-- Have an UPDATE trigger on the top level partition.
+create trigger trig_upd_after after update on parted_trig
+  for each row execute procedure update_notice_trig();
 
 -- Update non-key column. Should fire the UPDATE trigger.
 update parted_trig set nonkey = 3 where nonkey = 1;
 
--- Update distribution key column. Throws an error, currently.
+-- Update distribution key column on each level partition. Throws an error, currently.
 update parted_trig set distkey = 3 where distkey = 1;
+update parted_trig4 set distkey = 3 where distkey = 1;
+update parted_trig4_1 set distkey = 3 where distkey = 1;
 
 -- Update partitioning key column. Should fire the DELETE+INSERT triggers,
 -- like in PostgreSQL.
 update parted_trig set partkey = 3 where partkey = 1;
 
 -- Update everything in one statement. Throws an error, currently, because
--- updating the distributon key is not allowed.
+-- updating the distribution key is not allowed.
 update parted_trig set partkey = partkey + 1, distkey = distkey + 1;
 
 -- Should fire the DELETE trigger.


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org