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

Commit4e5e2fb

Browse files
committed
introduce functions build_update_trigger[_func]_name()
1 parentbce18a5 commit4e5e2fb

File tree

5 files changed

+120
-67
lines changed

5 files changed

+120
-67
lines changed

‎expected/pg_pathman.out

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -118,9 +118,9 @@ SET pg_pathman.enable_runtimemergeappend = OFF;
118118
VACUUM;
119119
/* update triggers test */
120120
SELECT pathman.create_hash_update_trigger('test.hash_rel');
121-
create_hash_update_trigger
122-
----------------------------
123-
121+
create_hash_update_trigger
122+
-----------------------------
123+
test.hash_rel_upd_trig_func
124124
(1 row)
125125

126126
UPDATE test.hash_rel SET value = 7 WHERE value = 6;
@@ -139,9 +139,9 @@ SELECT * FROM test.hash_rel WHERE value = 7;
139139
(1 row)
140140

141141
SELECT pathman.create_range_update_trigger('test.num_range_rel');
142-
create_range_update_trigger
143-
------------------------------------------
144-
test.num_range_rel_update_trigger_func()
142+
create_range_update_trigger
143+
----------------------------------
144+
test.num_range_rel_upd_trig_func
145145
(1 row)
146146

147147
UPDATE test.num_range_rel SET id = 3001 WHERE id = 1;
@@ -1068,7 +1068,7 @@ SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
10681068
(1 row)
10691069

10701070
SELECT pathman.drop_partitions('test.hash_rel', TRUE);
1071-
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
1071+
NOTICE: function test.hash_rel_upd_trig_func() does not exist, skipping
10721072
drop_partitions
10731073
-----------------
10741074
3
@@ -1221,7 +1221,7 @@ SELECT * FROM test."TeSt";
12211221
SELECT pathman.create_hash_update_trigger('test."TeSt"');
12221222
create_hash_update_trigger
12231223
----------------------------
1224-
1224+
test."TeSt_upd_trig_func"
12251225
(1 row)
12261226

12271227
UPDATE test."TeSt" SET a = 1;
@@ -1305,7 +1305,7 @@ SELECT pathman.split_range_partition('test."RangeRel_1"', '2015-01-01'::DATE);
13051305
(1 row)
13061306

13071307
SELECT pathman.drop_partitions('test."RangeRel"');
1308-
NOTICE: function test.RangeRel_update_trigger_func() does not exist, skipping
1308+
NOTICE: function test.RangeRel_upd_trig_func() does not exist, skipping
13091309
NOTICE: 1 rows copied from test."RangeRel_6"
13101310
NOTICE: 0 rows copied from test."RangeRel_4"
13111311
NOTICE: 1 rows copied from test."RangeRel_3"
@@ -1341,7 +1341,7 @@ SELECT pathman.create_range_partitions('test."RangeRel"', 'id', 1, 100, 3);
13411341
(1 row)
13421342

13431343
SELECT pathman.drop_partitions('test."RangeRel"');
1344-
NOTICE: function test.RangeRel_update_trigger_func() does not exist, skipping
1344+
NOTICE: function test.RangeRel_upd_trig_func() does not exist, skipping
13451345
NOTICE: 0 rows copied from test."RangeRel_3"
13461346
NOTICE: 0 rows copied from test."RangeRel_2"
13471347
NOTICE: 0 rows copied from test."RangeRel_1"
@@ -1500,7 +1500,7 @@ EXPLAIN (COSTS OFF) DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02
15001500
DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
15011501
/* Create range partitions from whole range */
15021502
SELECT drop_partitions('range_rel');
1503-
NOTICE: function public.range_rel_update_trigger_func() does not exist, skipping
1503+
NOTICE: function public.range_rel_upd_trig_func() does not exist, skipping
15041504
NOTICE: 0 rows copied from range_rel_15
15051505
NOTICE: 0 rows copied from range_rel_14
15061506
NOTICE: 14 rows copied from range_rel_13
@@ -1527,7 +1527,7 @@ SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
15271527
(1 row)
15281528

15291529
SELECT drop_partitions('range_rel', TRUE);
1530-
NOTICE: function public.range_rel_update_trigger_func() does not exist, skipping
1530+
NOTICE: function public.range_rel_upd_trig_func() does not exist, skipping
15311531
drop_partitions
15321532
-----------------
15331533
10

‎hash.sql

Lines changed: 26 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -78,16 +78,15 @@ SET client_min_messages = WARNING;
7878
*/
7979
CREATEOR REPLACE FUNCTION @extschema@.create_hash_update_trigger(
8080
parent_relidREGCLASS)
81-
RETURNSVOIDAS
81+
RETURNSTEXTAS
8282
$$
8383
DECLARE
84-
funcTEXT :='CREATE OR REPLACE FUNCTION %s()
84+
funcTEXT :='CREATE OR REPLACE FUNCTION %1$s()
8585
RETURNS TRIGGER AS
8686
$body$
8787
DECLARE
8888
old_idxINTEGER; /* partition indices */
8989
new_idxINTEGER;
90-
q TEXT;
9190
9291
BEGIN
9392
old_idx := @extschema@.get_hash_part_idx(%9$s(OLD.%2$s), %3$s);
@@ -97,11 +96,11 @@ DECLARE
9796
RETURN NEW;
9897
END IF;
9998
100-
q :=format(''DELETE FROM %8$s WHERE %4$s'', old_idx);
101-
EXECUTE qUSING %5$s;
99+
EXECUTEformat(''DELETE FROM %8$s WHERE %4$s'', old_idx)
100+
USING %5$s;
102101
103-
q :=format(''INSERT INTO %8$s VALUES (%6$s)'', new_idx);
104-
EXECUTE qUSING %7$s;
102+
EXECUTEformat(''INSERT INTO %8$s VALUES (%6$s)'', new_idx)
103+
USING %7$s;
105104
106105
RETURN NULL;
107106
END $body$
@@ -119,16 +118,19 @@ DECLARE
119118
attrTEXT;
120119
plain_schemaTEXT;
121120
plain_relnameTEXT;
121+
child_relname_formatTEXT;
122122
funcnameTEXT;
123123
triggernameTEXT;
124-
child_relname_formatTEXT;
125124
atttypeTEXT;
126125
hashfuncTEXT;
127126
partitions_countINTEGER;
128127

129128
BEGIN
130-
SELECT* INTO plain_schema, plain_relname
131-
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
129+
attr := attnameFROM @extschema@.pathman_configWHERE partrel= parent_relid;
130+
131+
IF attr ISNULL THEN
132+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(parent_relid::TEXT);
133+
END IF;
132134

133135
SELECT string_agg(attname,','),
134136
string_agg('OLD.'|| attname,','),
@@ -147,21 +149,21 @@ BEGIN
147149
att_val_fmt,
148150
att_fmt;
149151

150-
attr := attnameFROM @extschema@.pathman_configWHERE partrel= parent_relid;
151-
152-
IF attr ISNULL THEN
153-
RAISE EXCEPTION'Table % is not partitioned', quote_ident(parent_relid::TEXT);
154-
END IF;
155-
156152
partitions_count :=COUNT(*)FROMpg_catalog.pg_inherits
157153
WHERE inhparent= parent_relid::oid;
158154

159-
/* Function name, trigger name and child relname template*/
160-
funcname := plain_schema||'.'|| quote_ident(format('%s_update_trigger_func', plain_relname));
161-
child_relname_format := plain_schema||'.'|| quote_ident(plain_relname||'_%s');
162-
triggername := quote_ident(format('%s_%s_update_trigger', plain_schema, plain_relname));
155+
/* Build trigger & trigger function's names*/
156+
funcname := @extschema@.build_update_trigger_func_name(parent_relid);
157+
triggername := @extschema@.build_update_trigger_name(parent_relid);
163158

164-
/* base hash function for type*/
159+
/* Build partition name template*/
160+
SELECT* INTO plain_schema, plain_relname
161+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
162+
163+
child_relname_format := quote_ident(plain_schema)||'.'||
164+
quote_ident(plain_relname||'_%s');
165+
166+
/* Fetch base hash function for atttype*/
165167
atttype := @extschema@.get_attribute_type_name(parent_relid, attr);
166168
hashfunc := @extschema@.get_type_hash_func(atttype::regtype)::regproc;
167169

@@ -170,14 +172,16 @@ BEGIN
170172
old_fields, att_fmt, new_fields, child_relname_format, hashfunc);
171173
EXECUTE func;
172174

173-
/* Createtriggers onchild relations*/
175+
/* Createtrigger onevery partition*/
174176
FOR numIN0..partitions_count-1
175177
LOOP
176178
EXECUTE format(trigger,
177179
triggername,
178180
format(child_relname_format, num),
179181
funcname);
180182
END LOOP;
183+
184+
return funcname;
181185
END
182186
$$ LANGUAGE plpgsql;
183187

‎init.sql

Lines changed: 15 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -240,19 +240,14 @@ LANGUAGE plpgsql;
240240
* Drop trigger
241241
*/
242242
CREATEOR REPLACE FUNCTION @extschema@.drop_triggers(
243-
relationREGCLASS)
243+
parent_relidREGCLASS)
244244
RETURNS VOIDAS
245245
$$
246246
DECLARE
247-
relnameTEXT;
248-
schemaTEXT;
249247
funcnameTEXT;
250248

251249
BEGIN
252-
SELECT* INTO schema, relname
253-
FROM @extschema@.get_plain_schema_and_relname(relation);
254-
255-
funcname := schema||'.'|| quote_ident(format('%s_update_trigger_func', relname));
250+
funcname := @extschema@.build_update_trigger_func_name(parent_relid);
256251
EXECUTE format('DROP FUNCTION IF EXISTS %s() CASCADE', funcname);
257252
END
258253
$$ LANGUAGE plpgsql;
@@ -375,6 +370,19 @@ CREATE OR REPLACE FUNCTION @extschema@.build_check_constraint_name(
375370
RETURNSTEXTAS'pg_pathman','build_check_constraint_name_attname'
376371
LANGUAGE C STRICT;
377372

373+
/*
374+
* Build update trigger and its underlying function's names.
375+
*/
376+
CREATEOR REPLACE FUNCTION @extschema@.build_update_trigger_name(
377+
REGCLASS)
378+
RETURNSTEXTAS'pg_pathman','build_update_trigger_name'
379+
LANGUAGE C STRICT;
380+
381+
CREATEOR REPLACE FUNCTION @extschema@.build_update_trigger_func_name(
382+
REGCLASS)
383+
RETURNSTEXTAS'pg_pathman','build_update_trigger_func_name'
384+
LANGUAGE C STRICT;
385+
378386
/*
379387
* DEBUG: Place this inside some plpgsql fuction and set breakpoint.
380388
*/

‎range.sql

Lines changed: 30 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -972,39 +972,40 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_update_trigger(
972972
RETURNSTEXTAS
973973
$$
974974
DECLARE
975-
funcTEXT :='CREATE OR REPLACE FUNCTION %s_update_trigger_func()
975+
funcTEXT :='CREATE OR REPLACE FUNCTION %1$s()
976976
RETURNS TRIGGER AS
977977
$body$
978978
DECLARE
979-
old_oid INTEGER;
980-
new_oid INTEGER;
981-
q TEXT;
979+
old_oidOid;
980+
new_oidOid;
982981
983982
BEGIN
984983
old_oid := TG_RELID;
985984
new_oid := @extschema@.find_or_create_range_partition(
986-
''%1$s''::regclass, NEW.%2$s);
985+
''%2$s''::regclass, NEW.%3$s);
987986
988987
IF old_oid = new_oid THEN
989988
RETURN NEW;
990989
END IF;
991990
992-
q :=format(''DELETE FROM %%s WHERE %4$s'',
993-
old_oid::regclass::text);
994-
EXECUTE qUSING %5$s;
991+
EXECUTEformat(''DELETE FROM %%s WHERE %5$s'',
992+
old_oid::regclass::text)
993+
USING %6$s;
995994
996-
q :=format(''INSERT INTO %%s VALUES (%6$s)'',
997-
new_oid::regclass::text);
998-
EXECUTE qUSING %7$s;
995+
EXECUTEformat(''INSERT INTO %%s VALUES (%7$s)'',
996+
new_oid::regclass::text)
997+
USING %8$s;
999998
1000999
RETURN NULL;
10011000
END $body$
10021001
LANGUAGE plpgsql';
10031002

1004-
triggerTEXT :='CREATE TRIGGER %s_update_trigger'||
1003+
triggerTEXT :='CREATE TRIGGER %s'||
10051004
'BEFORE UPDATE ON %s'||
1006-
'FOR EACH ROW EXECUTE PROCEDURE %s_update_trigger_func()';
1005+
'FOR EACH ROW EXECUTE PROCEDURE %s()';
10071006

1007+
triggernameTEXT;
1008+
funcnameTEXT;
10081009
att_namesTEXT;
10091010
old_fieldsTEXT;
10101011
new_fieldsTEXT;
@@ -1014,6 +1015,12 @@ DECLARE
10141015
recRECORD;
10151016

10161017
BEGIN
1018+
attr := attnameFROM @extschema@.pathman_configWHERE partrel= parent_relid;
1019+
1020+
IF attr ISNULL THEN
1021+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(parent_relid::TEXT);
1022+
END IF;
1023+
10171024
SELECT string_agg(attname,','),
10181025
string_agg('OLD.'|| attname,','),
10191026
string_agg('NEW.'|| attname,','),
@@ -1031,28 +1038,25 @@ BEGIN
10311038
att_val_fmt,
10321039
att_fmt;
10331040

1034-
attr := attname
1035-
FROM @extschema@.pathman_config
1036-
WHERE partrel= parent_relid;
1037-
1038-
IF attr ISNULL THEN
1039-
RAISE EXCEPTION'Table % is not partitioned', quote_ident(parent_relid::TEXT);
1040-
END IF;
1041+
/* Build trigger & trigger function's names*/
1042+
funcname := @extschema@.build_update_trigger_func_name(parent_relid);
1043+
triggername := @extschema@.build_update_trigger_name(parent_relid);
10411044

10421045
/* Create function for trigger*/
1043-
EXECUTE format(func, parent_relid, attr,0, att_val_fmt,
1046+
EXECUTE format(func,funcname,parent_relid, attr,0, att_val_fmt,
10441047
old_fields, att_fmt, new_fields);
10451048

10461049
/* Create trigger on every partition*/
1047-
FOR recin (SELECT*FROM pg_inheritsWHERE inhparent= parent_relid)
1050+
FOR recin (SELECT*FROMpg_catalog.pg_inherits
1051+
WHERE inhparent= parent_relid)
10481052
LOOP
10491053
EXECUTE format(trigger,
1050-
@extschema@.get_schema_qualified_name(parent_relid,'_'),
1051-
rec.inhrelid::regclass,
1052-
parent_relid);
1054+
triggername,
1055+
@extschema@.get_schema_qualified_name(rec.inhrelid),
1056+
funcname);
10531057
END LOOP;
10541058

1055-
RETURN format('%s_update_trigger_func()', parent_relid);
1059+
return funcname;
10561060
END
10571061
$$ LANGUAGE plpgsql;
10581062

‎src/pl_funcs.c

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,8 @@ PG_FUNCTION_INFO_V1( check_overlap );
4040
PG_FUNCTION_INFO_V1(build_range_condition );
4141
PG_FUNCTION_INFO_V1(build_check_constraint_name_attnum );
4242
PG_FUNCTION_INFO_V1(build_check_constraint_name_attname );
43+
PG_FUNCTION_INFO_V1(build_update_trigger_func_name );
44+
PG_FUNCTION_INFO_V1(build_update_trigger_name );
4345
PG_FUNCTION_INFO_V1(is_date_type );
4446
PG_FUNCTION_INFO_V1(is_attribute_nullable );
4547
PG_FUNCTION_INFO_V1(debug_capture );
@@ -554,6 +556,41 @@ build_check_constraint_name_attname(PG_FUNCTION_ARGS)
554556
PG_RETURN_TEXT_P(cstring_to_text(result));
555557
}
556558

559+
Datum
560+
build_update_trigger_func_name(PG_FUNCTION_ARGS)
561+
{
562+
Oidrelid=PG_GETARG_OID(0),
563+
nspid;
564+
constchar*result;
565+
566+
/* Check that relation exists */
567+
if (get_rel_type_id(relid)==InvalidOid)
568+
elog(ERROR,"Invalid relation %u",relid);
569+
570+
nspid=get_rel_namespace(relid);
571+
result=psprintf("%s.%s",
572+
quote_identifier(get_namespace_name(nspid)),
573+
quote_identifier(psprintf("%s_upd_trig_func",
574+
get_rel_name(relid))));
575+
576+
PG_RETURN_TEXT_P(cstring_to_text(result));
577+
}
578+
579+
Datum
580+
build_update_trigger_name(PG_FUNCTION_ARGS)
581+
{
582+
Oidrelid=PG_GETARG_OID(0);
583+
constchar*result;/* trigger's name can't be qualified */
584+
585+
/* Check that relation exists */
586+
if (get_rel_type_id(relid)==InvalidOid)
587+
elog(ERROR,"Invalid relation %u",relid);
588+
589+
result=quote_identifier(psprintf("%s_upd_trig",get_rel_name(relid)));
590+
591+
PG_RETURN_TEXT_P(cstring_to_text(result));
592+
}
593+
557594
/*
558595
* NOTE: used for DEBUG, set breakpoint here.
559596
*/

0 commit comments

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