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

Commit6d6eb26

Browse files
committed
pathman: update triggers added
1 parent76fbfd5 commit6d6eb26

File tree

5 files changed

+164
-24
lines changed

5 files changed

+164
-24
lines changed

‎contrib/pathman/expected/pathman.out‎

Lines changed: 48 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6,8 +6,8 @@ CREATE TABLE test.hash_rel (
66
id SERIAL PRIMARY KEY,
77
value INTEGER);
88
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
9-
NOTICE: trigger "test_hash_rel_insert_trigger" for relation "test.hash_rel" does not exist, skipping
109
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
10+
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
1111
create_hash_partitions
1212
------------------------
1313

@@ -34,14 +34,57 @@ NOTICE: sequence "num_range_rel_seq" does not exist, skipping
3434

3535
(1 row)
3636

37+
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
38+
VACUUM;
3739
INSERT INTO test.hash_rel VALUES (1, 1);
3840
INSERT INTO test.hash_rel VALUES (2, 2);
3941
INSERT INTO test.hash_rel VALUES (3, 3);
4042
INSERT INTO test.hash_rel VALUES (4, 4);
4143
INSERT INTO test.hash_rel VALUES (5, 5);
4244
INSERT INTO test.hash_rel VALUES (6, 6);
43-
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
44-
VACUUM;
45+
/* update triggers test */
46+
SELECT pathman.create_hash_update_trigger('test.hash_rel');
47+
create_hash_update_trigger
48+
----------------------------
49+
50+
(1 row)
51+
52+
UPDATE test.hash_rel SET value = 7 WHERE value = 6;
53+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 7;
54+
QUERY PLAN
55+
------------------------------
56+
Append
57+
-> Seq Scan on hash_rel_1
58+
Filter: (value = 7)
59+
(3 rows)
60+
61+
SELECT * FROM test.hash_rel WHERE value = 7;
62+
id | value
63+
----+-------
64+
6 | 7
65+
(1 row)
66+
67+
SELECT pathman.create_range_update_trigger('test.num_range_rel');
68+
create_range_update_trigger
69+
-----------------------------
70+
71+
(1 row)
72+
73+
UPDATE test.num_range_rel SET id = 3001 WHERE id = 1;
74+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = 3001;
75+
QUERY PLAN
76+
-----------------------------------
77+
Append
78+
-> Seq Scan on num_range_rel_4
79+
Filter: (id = 3001)
80+
(3 rows)
81+
82+
SELECT * FROM test.num_range_rel WHERE id = 3001;
83+
id | txt
84+
------+----------------------------------
85+
3001 | c4ca4238a0b923820dcc509a6f75849b
86+
(1 row)
87+
4588
SET enable_indexscan = OFF;
4689
SET enable_bitmapscan = OFF;
4790
SET enable_seqscan = ON;
@@ -282,6 +325,8 @@ NOTICE: Done!
282325
* Clean up
283326
*/
284327
SELECT pathman.drop_hash_partitions('test.hash_rel');
328+
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
329+
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
285330
drop_hash_partitions
286331
----------------------
287332

‎contrib/pathman/sql/hash.sql‎

Lines changed: 34 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -37,8 +37,8 @@ BEGIN
3737

3838
/* Create triggers*/
3939
PERFORM @extschema@.create_hash_insert_trigger(relation, attribute, partitions_count);
40-
/*TODO: вернуть*/
41-
-- PERFORM create_hash_update_trigger(relation, attribute, partitions_count);
40+
/*Do not create update trigger by default*/
41+
-- PERFORM@extschema@.create_hash_update_trigger(relation, attribute, partitions_count);
4242

4343
/* Notify backend about changes*/
4444
PERFORM @extschema@.on_create_partitions(relation::regclass::oid);
@@ -105,12 +105,21 @@ $$
105105
DECLARE
106106
relidINTEGER;
107107
partitions_countINTEGER;
108-
qTEXT :='DROP TABLE %s_%s';
108+
rec RECORD;
109+
numINTEGER :=0;
109110
BEGIN
110111
/* Drop trigger first*/
111112
PERFORM @extschema@.drop_hash_triggers(relation);
112113
DELETEFROM @extschema@.pathman_configWHERE relname= relation;
113114
-- EXECUTE format('DROP TABLE %s CASCADE', relation);
115+
116+
-- FOR rec in (SELECT * FROM pg_inherits WHERE inhparent = relation::regclass::oid)
117+
-- LOOP
118+
-- EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func() CASCADE'
119+
-- , @extschema@.get_schema_qualified_name(relation::regclass)
120+
-- , num);
121+
-- num := num + 1;
122+
-- END LOOP;
114123

115124
/* Notify backend about changes*/
116125
PERFORM @extschema@.on_remove_partitions(relation::regclass::oid);
@@ -124,19 +133,18 @@ CREATE OR REPLACE FUNCTION @extschema@.drop_hash_triggers(IN relation TEXT)
124133
RETURNS VOIDAS
125134
$$
126135
BEGIN
127-
EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %s'
128-
, @extschema@.get_schema_qualified_name(relation::regclass)
129-
, relation);
130-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func()', relation::regclass::text);
131-
-- EXECUTE format('DROP TRIGGER IF EXISTS %s_update_trigger ON %1$s', relation);
132-
-- EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func()', relation);
136+
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func() CASCADE'
137+
, relation::regclass::text);
138+
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func() CASCADE'
139+
, relation::regclass::text);
133140
END
134141
$$ LANGUAGE plpgsql;
135142

143+
/*
144+
* Creates an update trigger
145+
*/
136146
CREATEOR REPLACE FUNCTION @extschema@.create_hash_update_trigger(
137-
IN relationTEXT
138-
,IN attrTEXT
139-
,IN partitions_countINTEGER)
147+
IN relationTEXT)
140148
RETURNS VOIDAS
141149
$$
142150
DECLARE
@@ -155,21 +163,24 @@ DECLARE
155163
EXECUTE q USING %7$s;
156164
RETURN NULL;
157165
END $body$ LANGUAGE plpgsql';
158-
triggerTEXT :='CREATE TRIGGER %s_update_trigger'||
159-
'BEFORE UPDATE ON %1$s_%s'||
160-
'FOR EACH ROW EXECUTE PROCEDURE %1$s_update_trigger_func()';
166+
triggerTEXT :='CREATE TRIGGER %s_%s_update_trigger'||
167+
'BEFORE UPDATE ON %s_%2$s'||
168+
'FOR EACH ROW EXECUTE PROCEDURE %3$s_update_trigger_func()';
161169
att_namesTEXT;
162170
old_fieldsTEXT;
163171
new_fieldsTEXT;
164172
att_val_fmtTEXT;
165173
att_fmtTEXT;
166174
relidINTEGER;
175+
partitions_countINTEGER;
176+
attrTEXT;
167177
BEGIN
168-
relid :=relfilenodeFROM pg_classWHERE relname=relation;
178+
relid := relation::regclass::oid;
169179
SELECT string_agg(attname,','),
170180
string_agg('OLD.'|| attname,','),
171181
string_agg('NEW.'|| attname,','),
172-
string_agg(attname||'=$'|| attnum,' AND'),
182+
string_agg('CASE WHEN NOT $'|| attnum||' IS NULL THEN'|| attname||' = $'|| attnum||
183+
' ELSE'|| attname||' IS NULL END',' AND'),
173184
string_agg('$'|| attnum,',')
174185
FROM pg_attribute
175186
WHERE attrelid=relidAND attnum>0
@@ -179,11 +190,16 @@ BEGIN
179190
att_val_fmt,
180191
att_fmt;
181192

193+
attr := attnameFROM @extschema@.pathman_configWHERE relname= relation;
194+
partitions_count :=COUNT(*)FROM pg_inheritsWHERE inhparent= relation::regclass::oid;
182195
EXECUTE format(func, relation, attr, partitions_count, att_val_fmt,
183196
old_fields, att_fmt, new_fields);
184197
FOR numIN0..partitions_count-1
185198
LOOP
186-
EXECUTE format(trigger, relation, num);
199+
EXECUTE format(trigger
200+
, @extschema@.get_schema_qualified_name(relation::regclass)
201+
, num
202+
, relation);
187203
END LOOP;
188204
END
189205
$$ LANGUAGE plpgsql;

‎contrib/pathman/sql/init.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -105,7 +105,7 @@ LANGUAGE plpgsql;
105105

106106

107107
/*
108-
* Validates relation name. It must befully qualified (containschemaname)
108+
* Validates relation name. It must be schemaqualified
109109
*/
110110
CREATEOR REPLACE FUNCTION @extschema@.validate_relname(relnameTEXT)
111111
RETURNSTEXTAS

‎contrib/pathman/sql/pathman.sql‎

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,8 @@ CREATE TABLE test.num_range_rel (
1919
idSERIALPRIMARY KEY,
2020
txtTEXT);
2121
SELECTpathman.create_range_partitions('test.num_range_rel','id',0,1000,3);
22+
INSERT INTOtest.num_range_relSELECT g, md5(g::TEXT)FROM generate_series(1,3000)as g;
23+
VACUUM;
2224

2325
INSERT INTOtest.hash_relVALUES (1,1);
2426
INSERT INTOtest.hash_relVALUES (2,2);
@@ -27,8 +29,16 @@ INSERT INTO test.hash_rel VALUES (4, 4);
2729
INSERT INTOtest.hash_relVALUES (5,5);
2830
INSERT INTOtest.hash_relVALUES (6,6);
2931

30-
INSERT INTOtest.num_range_relSELECT g, md5(g::TEXT)FROM generate_series(1,3000)as g;
31-
VACUUM;
32+
/* update triggers test*/
33+
SELECTpathman.create_hash_update_trigger('test.hash_rel');
34+
UPDATEtest.hash_relSET value=7WHERE value=6;
35+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=7;
36+
SELECT*FROMtest.hash_relWHERE value=7;
37+
38+
SELECTpathman.create_range_update_trigger('test.num_range_rel');
39+
UPDATEtest.num_range_relSET id=3001WHERE id=1;
40+
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id=3001;
41+
SELECT*FROMtest.num_range_relWHERE id=3001;
3242

3343
SET enable_indexscan= OFF;
3444
SET enable_bitmapscan= OFF;

‎contrib/pathman/sql/range.sql‎

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -498,6 +498,75 @@ END
498498
$$ LANGUAGE plpgsql;
499499

500500

501+
/*
502+
* Creates an update trigger
503+
*/
504+
CREATEOR REPLACE FUNCTION @extschema@.create_range_update_trigger(
505+
IN relationTEXT)
506+
RETURNS VOIDAS
507+
$$
508+
DECLARE
509+
funcTEXT :='
510+
CREATE OR REPLACE FUNCTION %s_update_trigger_func()
511+
RETURNS TRIGGER AS
512+
$body$
513+
DECLARE
514+
old_oid INTEGER;
515+
new_oid INTEGER;
516+
q TEXT;
517+
BEGIN
518+
old_oid := TG_RELID;
519+
new_oid := @extschema@.find_range_partition(''%1$s''::regclass::oid, NEW.%2$s);
520+
IF old_oid = new_oid THEN RETURN NEW; END IF;
521+
q := format(''DELETE FROM %%s WHERE %4$s'', old_oid::regclass::text);
522+
EXECUTE q USING %5$s;
523+
q := format(''INSERT INTO %%s VALUES (%6$s)'', new_oid::regclass::text);
524+
EXECUTE q USING %7$s;
525+
RETURN NULL;
526+
END $body$ LANGUAGE plpgsql';
527+
triggerTEXT :='CREATE TRIGGER %s_update_trigger'||
528+
'BEFORE UPDATE ON %s'||
529+
'FOR EACH ROW EXECUTE PROCEDURE %s_update_trigger_func()';
530+
att_namesTEXT;
531+
old_fieldsTEXT;
532+
new_fieldsTEXT;
533+
att_val_fmtTEXT;
534+
att_fmtTEXT;
535+
relidINTEGER;
536+
rec RECORD;
537+
numINTEGER :=0;
538+
attrTEXT;
539+
BEGIN
540+
relid := relation::regclass::oid;
541+
SELECT string_agg(attname,','),
542+
string_agg('OLD.'|| attname,','),
543+
string_agg('NEW.'|| attname,','),
544+
string_agg('CASE WHEN NOT $'|| attnum||' IS NULL THEN'|| attname||' = $'|| attnum||
545+
' ELSE'|| attname||' IS NULL END',' AND'),
546+
string_agg('$'|| attnum,',')
547+
FROM pg_attribute
548+
WHERE attrelid=relidAND attnum>0
549+
INTO att_names,
550+
old_fields,
551+
new_fields,
552+
att_val_fmt,
553+
att_fmt;
554+
555+
attr := attnameFROM @extschema@.pathman_configWHERE relname= relation;
556+
EXECUTE format(func, relation, attr,0, att_val_fmt,
557+
old_fields, att_fmt, new_fields);
558+
FOR recin (SELECT*FROM pg_inheritsWHERE inhparent= relation::regclass::oid)
559+
LOOP
560+
EXECUTE format(trigger
561+
, @extschema@.get_schema_qualified_name(relation::regclass)
562+
,rec.inhrelid::regclass
563+
, relation);
564+
num := num+1;
565+
END LOOP;
566+
END
567+
$$ LANGUAGE plpgsql;
568+
569+
501570
/*
502571
* Drop partitions
503572
*/

0 commit comments

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