Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit337a092

Browse files
committed
fix triggers
1 parent22743b0 commit337a092

File tree

5 files changed

+42
-27
lines changed

5 files changed

+42
-27
lines changed

‎expected/pg_pathman.out

Lines changed: 21 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,8 @@ SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1212
ERROR: Partitioning key 'value' must be NOT NULL
1313
ALTER TABLE test.hash_rel ALTER COLUMN value SET NOT NULL;
1414
SELECT pathman.create_hash_partitions('test.hash_rel', 'Value', 3);
15-
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
16-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
15+
NOTICE: function test.hash_rel_insert_trigger_func() does not exist, skipping
16+
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
1717
NOTICE: Copying data to partitions...
1818
create_hash_partitions
1919
------------------------
@@ -600,15 +600,26 @@ CREATE TABLE test.range_rel_test2 (
600600
dt TIMESTAMP);
601601
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_test2', '2013-01-01'::DATE, '2014-01-01'::DATE);
602602
ERROR: Partition must have the exact same structure as parent P0001
603+
/*
604+
* Check that altering table columns doesn't break trigger
605+
*/
606+
ALTER TABLE test.hash_rel ADD COLUMN abc int;
607+
INSERT INTO test.hash_rel (id, value, abc) VALUES (123, 456, 789);
608+
SELECT * FROM test.hash_rel WHERE id = 123;
609+
id | value | abc
610+
-----+-------+-----
611+
123 | 456 | 789
612+
(1 row)
613+
603614
/*
604615
* Clean up
605616
*/
606617
SELECT pathman.drop_hash_partitions('test.hash_rel');
607618
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
608-
NOTICE:function test.hash_rel_hash_update_trigger_func() does not exist, skipping
619+
NOTICE:drop cascades to 3 other objects
609620
NOTICE: 2 rows copied from test.hash_rel_2
610621
NOTICE: 3 rows copied from test.hash_rel_1
611-
NOTICE:1 rows copied from test.hash_rel_0
622+
NOTICE:2 rows copied from test.hash_rel_0
612623
drop_hash_partitions
613624
----------------------
614625
3
@@ -617,12 +628,12 @@ NOTICE: 1 rows copied from test.hash_rel_0
617628
SELECT COUNT(*) FROM ONLY test.hash_rel;
618629
count
619630
-------
620-
6
631+
7
621632
(1 row)
622633

623634
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
624-
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
625-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
635+
NOTICE: function test.hash_rel_insert_trigger_func() does not exist, skipping
636+
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
626637
NOTICE: Copying data to partitions...
627638
create_hash_partitions
628639
------------------------
@@ -631,7 +642,7 @@ NOTICE: Copying data to partitions...
631642

632643
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
633644
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
634-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
645+
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
635646
drop_hash_partitions
636647
----------------------
637648
3
@@ -770,8 +781,8 @@ CREATE TABLE hash_rel (
770781
valueINTEGER NOT NULL);
771782
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
772783
SELECT create_hash_partitions('hash_rel', 'value', 3);
773-
NOTICE: functionhash_rel_hash_insert_trigger_func() does not exist, skipping
774-
NOTICE: functionhash_rel_hash_update_trigger_func() does not exist, skipping
784+
NOTICE: functionhash_rel_insert_trigger_func() does not exist, skipping
785+
NOTICE: functionhash_rel_update_trigger_func() does not exist, skipping
775786
NOTICE: Copying data to partitions...
776787
create_hash_partitions
777788
------------------------

‎hash.sql

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,7 @@ RETURNS VOID AS
7676
$$
7777
DECLARE
7878
funcTEXT :='
79-
CREATE OR REPLACE FUNCTION %s_hash_insert_trigger_func()
79+
CREATE OR REPLACE FUNCTION %s_insert_trigger_func()
8080
RETURNS TRIGGER AS $body$
8181
DECLARE
8282
hash INTEGER;
@@ -88,7 +88,7 @@ DECLARE
8888
triggerTEXT :='
8989
CREATE TRIGGER %s_insert_trigger
9090
BEFORE INSERT ON %s
91-
FOR EACH ROW EXECUTE PROCEDURE %2$s_hash_insert_trigger_func();';
91+
FOR EACH ROW EXECUTE PROCEDURE %2$s_insert_trigger_func();';
9292
fieldsTEXT;
9393
fields_formatTEXT;
9494
insert_stmtTEXT;
@@ -103,10 +103,8 @@ BEGIN
103103
INTO fields, fields_format;
104104

105105
/* generate INSERT statement for trigger*/
106-
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s VALUES (%s)'', hash) USING %s;'
107-
, relation
108-
, fields_format
109-
, fields);
106+
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s SELECT $1.*'', hash) USING NEW;'
107+
, relation);
110108

111109
/* format and create new trigger for relation*/
112110
func := format(func, relation, attr, partitions_count, insert_stmt);
@@ -164,9 +162,9 @@ CREATE OR REPLACE FUNCTION @extschema@.drop_hash_triggers(IN relation TEXT)
164162
RETURNS VOIDAS
165163
$$
166164
BEGIN
167-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func() CASCADE'
165+
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE'
168166
, relation::regclass::text);
169-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func() CASCADE'
167+
EXECUTE format('DROP FUNCTION IF EXISTS %s_update_trigger_func() CASCADE'
170168
, relation::regclass::text);
171169
END
172170
$$ LANGUAGE plpgsql;

‎init.sql

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -119,11 +119,18 @@ LANGUAGE plpgsql;
119119
CREATEOR REPLACE FUNCTION @extschema@.disable_partitioning(IN relationTEXT)
120120
RETURNS VOIDAS
121121
$$
122+
DECLARE
123+
parttypeINTEGER;
122124
BEGIN
123125
relation := @extschema@.validate_relname(relation);
126+
parttype := parttypeFROM pathman_configWHERE relname= relation;
124127

125128
DELETEFROM @extschema@.pathman_configWHERE relname= relation;
126-
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE', relation);
129+
IF parttype=1 THEN
130+
PERFORM @extschema@.drop_hash_triggers(relation);
131+
ELSIF parttype=2 THEN
132+
PERFORM @extschema@.drop_range_triggers(relation);
133+
END IF;
127134

128135
/* Notify backend about changes*/
129136
PERFORM on_remove_partitions(relation::regclass::integer);

‎range.sql

Lines changed: 0 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -406,9 +406,6 @@ BEGIN
406406
LIMIT1;
407407
EXIT WHEN v_child_relname_exists=0;
408408
END LOOP;
409-
-- v_child_relname := format('%s_%s'
410-
-- , p_parent_relname
411-
-- , regexp_replace(p_start_value::text, '[ :-]*', '', 'g'));
412409

413410
/* Skip existing partitions*/
414411
IF EXISTS (SELECT*FROM pg_tablesWHERE tablename= v_child_relname) THEN
@@ -431,7 +428,6 @@ BEGIN
431428
, v_cond);
432429

433430
EXECUTE v_sql;
434-
-- RAISE NOTICE 'partition % created', v_child_relname;
435431
RETURN v_child_relname;
436432
END
437433
$$ LANGUAGE plpgsql;
@@ -763,9 +759,6 @@ DECLARE
763759
BEGIN
764760
p_range := @extschema@.get_range_by_idx(p_relation::regclass::oid,0,0);
765761
RAISE NOTICE'Prepending new partition...';
766-
-- v_part_name := @extschema@.create_single_range_partition(p_relation
767-
-- , p_range[1] - (p_range[2] - p_range[1])
768-
-- , p_range[1]);
769762

770763
IF @extschema@.is_date(p_atttype::regtype) THEN
771764
v_part_name := @extschema@.create_single_range_partition(p_relation
@@ -1117,7 +1110,6 @@ BEGIN
11171110
END LOOP;
11181111

11191112
DELETEFROM @extschema@.pathman_configWHERE relname= relation;
1120-
-- DELETE FROM pg_pathman_range_rels WHERE parent = relation;
11211113

11221114
/* Notify backend about changes*/
11231115
PERFORM @extschema@.on_remove_partitions(relation::regclass::oid);

‎sql/pg_pathman.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -149,6 +149,13 @@ CREATE TABLE test.range_rel_test2 (
149149
dtTIMESTAMP);
150150
SELECTpathman.attach_range_partition('test.range_rel','test.range_rel_test2','2013-01-01'::DATE,'2014-01-01'::DATE);
151151

152+
/*
153+
* Check that altering table columns doesn't break trigger
154+
*/
155+
ALTERTABLEtest.hash_rel ADD COLUMN abcint;
156+
INSERT INTOtest.hash_rel (id, value, abc)VALUES (123,456,789);
157+
SELECT*FROMtest.hash_relWHERE id=123;
158+
152159
/*
153160
* Clean up
154161
*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp