Skip to content

Commit 4e5e2fb

Browse files
funbringerfunbringer
committed
introduce functions build_update_trigger[_func]_name()
1 parent bce18a5 commit 4e5e2fb

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
CREATE OR REPLACE FUNCTION @extschema@.create_hash_update_trigger(
8080
parent_relid REGCLASS)
81-
RETURNS VOID AS
81+
RETURNS TEXT AS
8282
$$
8383
DECLARE
84-
func TEXT := 'CREATE OR REPLACE FUNCTION %s()
84+
func TEXT := 'CREATE OR REPLACE FUNCTION %1$s()
8585
RETURNS TRIGGER AS
8686
$body$
8787
DECLARE
8888
old_idx INTEGER; /* partition indices */
8989
new_idx INTEGER;
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 q USING %5$s;
99+
EXECUTE format(''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 q USING %7$s;
102+
EXECUTE format(''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
attr TEXT;
120119
plain_schema TEXT;
121120
plain_relname TEXT;
121+
child_relname_format TEXT;
122122
funcname TEXT;
123123
triggername TEXT;
124-
child_relname_format TEXT;
125124
atttype TEXT;
126125
hashfunc TEXT;
127126
partitions_count INTEGER;
128127

129128
BEGIN
130-
SELECT * INTO plain_schema, plain_relname
131-
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
129+
attr := attname FROM @extschema@.pathman_config WHERE partrel = parent_relid;
130+
131+
IF attr IS NULL 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 := attname FROM @extschema@.pathman_config WHERE partrel = parent_relid;
151-
152-
IF attr IS NULL THEN
153-
RAISE EXCEPTION 'Table % is not partitioned', quote_ident(parent_relid::TEXT);
154-
END IF;
155-
156152
partitions_count := COUNT(*) FROM pg_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-
/* Create triggers on child relations */
175+
/* Create trigger on every partition */
174176
FOR num IN 0..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
CREATE OR REPLACE FUNCTION @extschema@.drop_triggers(
243-
relation REGCLASS)
243+
parent_relid REGCLASS)
244244
RETURNS VOID AS
245245
$$
246246
DECLARE
247-
relname TEXT;
248-
schema TEXT;
249247
funcname TEXT;
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
RETURNS TEXT AS '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+
CREATE OR REPLACE FUNCTION @extschema@.build_update_trigger_name(
377+
REGCLASS)
378+
RETURNS TEXT AS 'pg_pathman', 'build_update_trigger_name'
379+
LANGUAGE C STRICT;
380+
381+
CREATE OR REPLACE FUNCTION @extschema@.build_update_trigger_func_name(
382+
REGCLASS)
383+
RETURNS TEXT AS '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
RETURNS TEXT AS
973973
$$
974974
DECLARE
975-
func TEXT := 'CREATE OR REPLACE FUNCTION %s_update_trigger_func()
975+
func TEXT := '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_oid Oid;
980+
new_oid Oid;
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 q USING %5$s;
991+
EXECUTE format(''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 q USING %7$s;
995+
EXECUTE format(''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-
trigger TEXT := 'CREATE TRIGGER %s_update_trigger ' ||
1003+
trigger TEXT := '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+
triggername TEXT;
1008+
funcname TEXT;
10081009
att_names TEXT;
10091010
old_fields TEXT;
10101011
new_fields TEXT;
@@ -1014,6 +1015,12 @@ DECLARE
10141015
rec RECORD;
10151016

10161017
BEGIN
1018+
attr := attname FROM @extschema@.pathman_config WHERE partrel = parent_relid;
1019+
1020+
IF attr IS NULL 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 IS NULL 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 rec in (SELECT * FROM pg_inherits WHERE inhparent = parent_relid)
1050+
FOR rec in (SELECT * FROM pg_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+
Oid relid = PG_GETARG_OID(0),
563+
nspid;
564+
const char *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+
Oid relid = PG_GETARG_OID(0);
583+
const char *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)