rewrited drop_triggers() func and tests are fixed

zilder · zilder · commit ded87de43ee1 · 2017-02-16T18:10:47.000+03:00
diff --git a/expected/pathman_basic.out b/expected/pathman_basic.out
@@ -81,7 +81,6 @@ SELECT * FROM test.hash_rel;
 (3 rows)
 
 SELECT pathman.drop_partitions('test.hash_rel');
-NOTICE:  function test.hash_rel_upd_trig_func() does not exist, skipping
 NOTICE:  0 rows copied from test.hash_rel_0
 NOTICE:  0 rows copied from test.hash_rel_1
 NOTICE:  0 rows copied from test.hash_rel_2
@@ -570,10 +569,10 @@ SET pg_pathman.enable_runtimeappend = OFF;
 SET pg_pathman.enable_runtimemergeappend = OFF;
 VACUUM;
 /* update triggers test */
-SELECT pathman.create_hash_update_trigger('test.hash_rel');
- create_hash_update_trigger  
------------------------------
- test.hash_rel_upd_trig_func
+SELECT pathman.create_update_triggers('test.hash_rel');
+ create_update_triggers 
+------------------------
+ 
 (1 row)
 
 UPDATE test.hash_rel SET value = 7 WHERE value = 6;
@@ -591,10 +590,10 @@ SELECT * FROM test.hash_rel WHERE value = 7;
   6 |     7
 (1 row)
 
-SELECT pathman.create_range_update_trigger('test.num_range_rel');
-   create_range_update_trigger    
-----------------------------------
- test.num_range_rel_upd_trig_func
+SELECT pathman.create_update_triggers('test.num_range_rel');
+ create_update_triggers 
+------------------------
+ 
 (1 row)
 
 UPDATE test.num_range_rel SET id = 3001 WHERE id = 1;
@@ -1477,7 +1476,7 @@ SELECT pathman.replace_hash_partition('test.hash_rel_0', 'test.hash_rel_extern')
 Indexes:
     "hash_rel_0_pkey" PRIMARY KEY, btree (id)
 Triggers:
-    hash_rel_upd_trig BEFORE UPDATE ON test.hash_rel_0 FOR EACH ROW EXECUTE PROCEDURE test.hash_rel_upd_trig_func()
+    hash_rel_upd_trig BEFORE UPDATE ON test.hash_rel_0 FOR EACH ROW EXECUTE PROCEDURE pathman.update_trigger_func()
 
 \d+ test.hash_rel_extern
                                              Table "test.hash_rel_extern"
@@ -1513,7 +1512,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
  * Clean up
  */
 SELECT pathman.drop_partitions('test.hash_rel');
-NOTICE:  drop cascades to 2 other objects
 NOTICE:  3 rows copied from test.hash_rel_1
 NOTICE:  2 rows copied from test.hash_rel_2
 NOTICE:  2 rows copied from test.hash_rel_extern
@@ -1535,7 +1533,6 @@ SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
 (1 row)
 
 SELECT pathman.drop_partitions('test.hash_rel', TRUE);
-NOTICE:  function test.hash_rel_upd_trig_func() does not exist, skipping
  drop_partitions 
 -----------------
                3
@@ -1549,7 +1546,6 @@ SELECT COUNT(*) FROM ONLY test.hash_rel;
 
 DROP TABLE test.hash_rel CASCADE;
 SELECT pathman.drop_partitions('test.num_range_rel');
-NOTICE:  drop cascades to 3 other objects
 NOTICE:  998 rows copied from test.num_range_rel_1
 NOTICE:  1000 rows copied from test.num_range_rel_2
 NOTICE:  1000 rows copied from test.num_range_rel_3
@@ -1690,10 +1686,10 @@ SELECT * FROM test."TeSt";
  1 | 1
 (3 rows)
 
-SELECT pathman.create_hash_update_trigger('test."TeSt"');
- create_hash_update_trigger 
-----------------------------
- test."TeSt_upd_trig_func"
+SELECT pathman.create_update_triggers('test."TeSt"');
+ create_update_triggers 
+------------------------
+ 
 (1 row)
 
 UPDATE test."TeSt" SET a = 1;
@@ -1722,7 +1718,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM test."TeSt" WHERE a = 1;
 (3 rows)
 
 SELECT pathman.drop_partitions('test."TeSt"');
-NOTICE:  drop cascades to 3 other objects
 NOTICE:  0 rows copied from test."TeSt_0"
 NOTICE:  0 rows copied from test."TeSt_1"
 NOTICE:  3 rows copied from test."TeSt_2"
@@ -1777,7 +1772,6 @@ SELECT pathman.split_range_partition('test."RangeRel_1"', '2015-01-01'::DATE);
 (1 row)
 
 SELECT pathman.drop_partitions('test."RangeRel"');
-NOTICE:  function test.RangeRel_upd_trig_func() does not exist, skipping
 NOTICE:  0 rows copied from test."RangeRel_1"
 NOTICE:  1 rows copied from test."RangeRel_2"
 NOTICE:  1 rows copied from test."RangeRel_3"
@@ -1813,7 +1807,6 @@ SELECT pathman.create_range_partitions('test."RangeRel"', 'id', 1, 100, 3);
 (1 row)
 
 SELECT pathman.drop_partitions('test."RangeRel"');
-NOTICE:  function test.RangeRel_upd_trig_func() does not exist, skipping
 NOTICE:  0 rows copied from test."RangeRel_1"
 NOTICE:  0 rows copied from test."RangeRel_2"
 NOTICE:  0 rows copied from test."RangeRel_3"
@@ -1971,7 +1964,6 @@ EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '
 DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
 /* Create range partitions from whole range */
 SELECT drop_partitions('test.range_rel');
-NOTICE:  function test.range_rel_upd_trig_func() does not exist, skipping
 NOTICE:  44 rows copied from test.range_rel_1
 NOTICE:  31 rows copied from test.range_rel_3
 NOTICE:  30 rows copied from test.range_rel_4
@@ -1998,7 +1990,6 @@ SELECT create_partitions_from_range('test.range_rel', 'id', 1, 1000, 100);
 (1 row)
 
 SELECT drop_partitions('test.range_rel', TRUE);
-NOTICE:  function test.range_rel_upd_trig_func() does not exist, skipping
  drop_partitions 
 -----------------
               10
diff --git a/expected/pathman_calamity.out b/expected/pathman_calamity.out
@@ -28,7 +28,6 @@ NOTICE:  sequence "part_test_seq" does not exist, skipping
 (1 row)
 
 SELECT drop_partitions('calamity.part_test');
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
 NOTICE:  10 rows copied from calamity.part_test_1
 NOTICE:  10 rows copied from calamity.part_test_2
 NOTICE:  10 rows copied from calamity.part_test_3
@@ -44,7 +43,6 @@ SELECT create_range_partitions('calamity.part_test', 'val', 1, 10);
 (1 row)
 
 SELECT drop_partitions('calamity.part_test');
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
 NOTICE:  10 rows copied from calamity.part_test_1
 NOTICE:  10 rows copied from calamity.part_test_2
 NOTICE:  10 rows copied from calamity.part_test_3
@@ -66,7 +64,6 @@ SELECT append_range_partition('calamity.part_test');
 (1 row)
 
 SELECT drop_partitions('calamity.part_test');
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
 NOTICE:  10 rows copied from calamity.part_test_1
 NOTICE:  10 rows copied from calamity.part_test_2
 NOTICE:  10 rows copied from calamity.part_test_3
@@ -89,7 +86,6 @@ SELECT append_range_partition('calamity.part_test');
 (1 row)
 
 SELECT drop_partitions('calamity.part_test');
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
 NOTICE:  10 rows copied from calamity.part_test_1
 NOTICE:  10 rows copied from calamity.part_test_2
 NOTICE:  10 rows copied from calamity.part_test_3
@@ -171,7 +167,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM calamity.part_test;
 (5 rows)
 
 SELECT drop_partitions('calamity.part_test', true);
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
  drop_partitions 
 -----------------
                4
@@ -199,7 +194,6 @@ ERROR:  invalid input syntax for integer: "15.6"
 SELECT set_interval('calamity.part_test', 'abc'::text);		/* not ok */
 ERROR:  invalid input syntax for integer: "abc"
 SELECT drop_partitions('calamity.part_test', true);
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
  drop_partitions 
 -----------------
                3
@@ -479,7 +473,6 @@ SELECT add_to_pathman_config('calamity.part_test', 'val');		/* OK */
 (1 row)
 
 SELECT disable_pathman_for('calamity.part_test');
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
  disable_pathman_for 
 ---------------------
  
@@ -492,7 +485,6 @@ SELECT add_to_pathman_config('calamity.part_test', 'val', '10'); /* OK */
 (1 row)
 
 SELECT disable_pathman_for('calamity.part_test');
-NOTICE:  function calamity.part_test_upd_trig_func() does not exist, skipping
  disable_pathman_for 
 ---------------------
  
@@ -599,7 +591,6 @@ SELECT set_enable_parent('calamity.to_be_disabled', true); /* add row to params
 (1 row)
 
 SELECT disable_pathman_for('calamity.to_be_disabled'); /* should delete both rows */
-NOTICE:  function calamity.to_be_disabled_upd_trig_func() does not exist, skipping
  disable_pathman_for 
 ---------------------
  
diff --git a/expected/pathman_callbacks.out b/expected/pathman_callbacks.out
@@ -133,7 +133,6 @@ WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_7",
 (1 row)
 
 SELECT drop_partitions('callbacks.abc');
-NOTICE:  function callbacks.abc_upd_trig_func() does not exist, skipping
 NOTICE:  0 rows copied from callbacks.abc_1
 NOTICE:  1 rows copied from callbacks.abc_2
 NOTICE:  1 rows copied from callbacks.abc_3
diff --git a/expected/pathman_cte.out b/expected/pathman_cte.out
@@ -121,7 +121,6 @@ WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
 (24 rows)
 
 SELECT drop_partitions('test_cte.cte_del_xacts'); /* now drop partitions */
-NOTICE:  function test_cte.cte_del_xacts_upd_trig_func() does not exist, skipping
 NOTICE:  50 rows copied from test_cte.cte_del_xacts_1
 NOTICE:  50 rows copied from test_cte.cte_del_xacts_2
  drop_partitions 
diff --git a/expected/pathman_domains.out b/expected/pathman_domains.out
@@ -88,7 +88,6 @@ ORDER BY range_min::INT, range_max::INT;
 (14 rows)
 
 SELECT drop_partitions('domains.dom_table');
-NOTICE:  function domains.dom_table_upd_trig_func() does not exist, skipping
 NOTICE:  49 rows copied from domains.dom_table_1
 NOTICE:  100 rows copied from domains.dom_table_3
 NOTICE:  100 rows copied from domains.dom_table_4
diff --git a/expected/pathman_foreign_keys.out b/expected/pathman_foreign_keys.out
@@ -20,7 +20,6 @@ INSERT INTO fkeys.test_fkey VALUES(1, 'wrong');
 ERROR:  insert or update on table "test_fkey_1" violates foreign key constraint "test_fkey_1_comment_fkey"
 INSERT INTO fkeys.test_fkey VALUES(1, 'test');
 SELECT drop_partitions('fkeys.test_fkey');
-NOTICE:  function fkeys.test_fkey_upd_trig_func() does not exist, skipping
 NOTICE:  101 rows copied from fkeys.test_fkey_1
 NOTICE:  100 rows copied from fkeys.test_fkey_2
 NOTICE:  100 rows copied from fkeys.test_fkey_3
@@ -46,7 +45,6 @@ INSERT INTO fkeys.test_fkey VALUES(1, 'wrong');
 ERROR:  insert or update on table "test_fkey_0" violates foreign key constraint "test_fkey_0_comment_fkey"
 INSERT INTO fkeys.test_fkey VALUES(1, 'test');
 SELECT drop_partitions('fkeys.test_fkey');
-NOTICE:  function fkeys.test_fkey_upd_trig_func() does not exist, skipping
 NOTICE:  100 rows copied from fkeys.test_fkey_0
 NOTICE:  90 rows copied from fkeys.test_fkey_1
 NOTICE:  90 rows copied from fkeys.test_fkey_2
diff --git a/expected/pathman_permissions.out b/expected/pathman_permissions.out
@@ -134,7 +134,6 @@ ERROR:  no suitable partition for key '55'
 /* Finally drop partitions */
 SET ROLE user1;
 SELECT drop_partitions('permissions.user1_table');
-NOTICE:  function permissions.user1_table_upd_trig_func() does not exist, skipping
 NOTICE:  10 rows copied from permissions.user1_table_1
 NOTICE:  10 rows copied from permissions.user1_table_2
 NOTICE:  0 rows copied from permissions.user1_table_4
@@ -157,7 +156,6 @@ SELECT create_hash_partitions('permissions.user2_table', 'id', 3);
 
 INSERT INTO permissions.user2_table SELECT generate_series(1, 30);
 SELECT drop_partitions('permissions.user2_table');
-NOTICE:  function permissions.user2_table_upd_trig_func() does not exist, skipping
 NOTICE:  9 rows copied from permissions.user2_table_0
 NOTICE:  11 rows copied from permissions.user2_table_1
 NOTICE:  10 rows copied from permissions.user2_table_2
diff --git a/init.sql b/init.sql
@@ -551,15 +551,64 @@ $$
 LANGUAGE plpgsql;
 
 /*
- * Drop triggers.
+ * Function for update triggers
+ */
+CREATE OR REPLACE FUNCTION @extschema@.update_trigger_func()
+RETURNS TRIGGER AS 'pg_pathman', 'update_trigger_func'
+LANGUAGE C;
+
+/*
+ * Creates an update trigger
+ */
+CREATE OR REPLACE FUNCTION @extschema@.create_update_triggers(
+	IN parent_relid	REGCLASS)
+RETURNS VOID AS
+$$
+DECLARE
+	trigger			TEXT := 'CREATE TRIGGER %s 
+							 BEFORE UPDATE ON %s 
+							 FOR EACH ROW EXECUTE PROCEDURE 
+							 @extschema@.update_trigger_func()';
+	triggername		TEXT;
+	rec				RECORD;
+
+BEGIN
+	triggername := @extschema@.build_update_trigger_name(parent_relid);
+
+	/* Create trigger on every partition */
+	FOR rec in (SELECT * FROM pg_catalog.pg_inherits
+				WHERE inhparent = parent_relid)
+	LOOP
+		EXECUTE format(trigger,
+					   triggername,
+					   rec.inhrelid::REGCLASS::TEXT);
+	END LOOP;
+END
+$$ LANGUAGE plpgsql;
+
+/*
+ * Drop triggers
  */
 CREATE OR REPLACE FUNCTION @extschema@.drop_triggers(
 	parent_relid	REGCLASS)
 RETURNS VOID AS
 $$
+DECLARE
+	triggername	TEXT;
+	rec			RECORD;
+
 BEGIN
-	EXECUTE format('DROP FUNCTION IF EXISTS %s() CASCADE',
-				   @extschema@.build_update_trigger_func_name(parent_relid));
+	triggername := @extschema@.build_update_trigger_name(parent_relid);
+
+	/* Drop trigger for each partition if exists */
+	FOR rec IN (SELECT pg_catalog.pg_inherits.* FROM pg_catalog.pg_inherits
+				JOIN pg_catalog.pg_trigger on inhrelid = tgrelid
+				WHERE inhparent = parent_relid AND tgname = triggername)
+	LOOP
+		EXECUTE format('DROP TRIGGER IF EXISTS %s ON %s',
+					   triggername,
+					   rec.inhrelid::REGCLASS::TEXT);
+	END LOOP;
 END
 $$ LANGUAGE plpgsql STRICT;
 
@@ -896,43 +945,3 @@ CREATE OR REPLACE FUNCTION @extschema@.invoke_on_partition_created_callback(
 	init_callback	REGPROCEDURE)
 RETURNS VOID AS 'pg_pathman', 'invoke_on_partition_created_callback'
 LANGUAGE C;
-
-
-/*
- * Function for update triggers
- */
-CREATE OR REPLACE FUNCTION @extschema@.update_trigger_func()
-RETURNS TRIGGER AS 'pg_pathman', 'update_trigger_func'
-LANGUAGE C;
-
-
-/*
- * Creates an update trigger
- */
-CREATE OR REPLACE FUNCTION @extschema@.create_update_trigger(
-	IN parent_relid	REGCLASS)
-RETURNS TEXT AS
-$$
-DECLARE
-	trigger			TEXT := 'CREATE TRIGGER %s 
-							 BEFORE UPDATE ON %s 
-							 FOR EACH ROW EXECUTE PROCEDURE 
-							 @extschema@.update_trigger_func()';
-	triggername		TEXT;
-	rec				RECORD;
-
-BEGIN
-	triggername := @extschema@.build_update_trigger_name(parent_relid);
-
-	/* Create trigger on every partition */
-	FOR rec in (SELECT * FROM pg_catalog.pg_inherits
-				WHERE inhparent = parent_relid)
-	LOOP
-		EXECUTE format(trigger,
-					   triggername,
-					   rec.inhrelid::REGCLASS::TEXT);
-	END LOOP;
-
-	RETURN 'update_trigger_func()';
-END
-$$ LANGUAGE plpgsql;
diff --git a/sql/pathman_basic.sql b/sql/pathman_basic.sql
@@ -173,12 +173,12 @@ SET pg_pathman.enable_runtimemergeappend = OFF;
 VACUUM;
 
 /* update triggers test */
-SELECT pathman.create_hash_update_trigger('test.hash_rel');
+SELECT pathman.create_update_triggers('test.hash_rel');
 UPDATE test.hash_rel SET value = 7 WHERE value = 6;
 EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 7;
 SELECT * FROM test.hash_rel WHERE value = 7;
 
-SELECT pathman.create_range_update_trigger('test.num_range_rel');
+SELECT pathman.create_update_triggers('test.num_range_rel');
 UPDATE test.num_range_rel SET id = 3001 WHERE id = 1;
 EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = 3001;
 SELECT * FROM test.num_range_rel WHERE id = 3001;
@@ -464,7 +464,7 @@ INSERT INTO test."TeSt" VALUES (1, 1);
 INSERT INTO test."TeSt" VALUES (2, 2);
 INSERT INTO test."TeSt" VALUES (3, 3);
 SELECT * FROM test."TeSt";
-SELECT pathman.create_hash_update_trigger('test."TeSt"');
+SELECT pathman.create_update_triggers('test."TeSt"');
 UPDATE test."TeSt" SET a = 1;
 SELECT * FROM test."TeSt";
 SELECT * FROM test."TeSt" WHERE a = 1;
