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

Commit1a0f23e

Browse files
committed
plpgsql code cleanup, introduce parent_of_partition() function, remove acquire_partitions_lock() & release_partitions_lock(), fix spawn_partitions()
1 parentf6fe29c commit1a0f23e

File tree

7 files changed

+543
-538
lines changed

7 files changed

+543
-538
lines changed

‎expected/pg_pathman.out‎

Lines changed: 0 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -58,7 +58,6 @@ SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DAT
5858
ERROR: Not enough partitions to fit all the values of 'dt'
5959
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
6060
NOTICE: sequence "range_rel_seq" does not exist, skipping
61-
NOTICE: Copying data to partitions...
6261
create_range_partitions
6362
-------------------------
6463
4
@@ -81,7 +80,6 @@ CREATE TABLE test.num_range_rel (
8180
txtTEXT);
8281
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
8382
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
84-
NOTICE: Copying data to partitions...
8583
create_range_partitions
8684
-------------------------
8785
4
@@ -1103,7 +1101,6 @@ CREATE TABLE test.range_rel (
11031101
idSERIAL PRIMARY KEY,
11041102
dtTIMESTAMP NOT NULL);
11051103
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
1106-
NOTICE: Copying data to partitions...
11071104
create_range_partitions
11081105
-------------------------
11091106
1
@@ -1153,7 +1150,6 @@ CREATE TABLE test.num_range_rel (
11531150
idSERIAL PRIMARY KEY,
11541151
txtTEXT);
11551152
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 1000, 1000, 4);
1156-
NOTICE: Copying data to partitions...
11571153
create_range_partitions
11581154
-------------------------
11591155
4
@@ -1279,7 +1275,6 @@ INSERT INTO test."RangeRel" (dt, txt)
12791275
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-01-03', '1 day'::interval) as g;
12801276
SELECT pathman.create_range_partitions('test."RangeRel"', 'dt', '2015-01-01'::DATE, '1 day'::INTERVAL);
12811277
NOTICE: sequence "RangeRel_seq" does not exist, skipping
1282-
NOTICE: Copying data to partitions...
12831278
create_range_partitions
12841279
-------------------------
12851280
3
@@ -1322,7 +1317,6 @@ NOTICE: 0 rows copied from test."RangeRel_1"
13221317
(1 row)
13231318

13241319
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01'::DATE, '2015-01-05'::DATE, '1 day'::INTERVAL);
1325-
NOTICE: Copying data to partitions...
13261320
create_partitions_from_range
13271321
------------------------------
13281322
5
@@ -1341,7 +1335,6 @@ CREATE TABLE test."RangeRel" (
13411335
dtTIMESTAMP NOT NULL,
13421336
txtTEXT);
13431337
SELECT pathman.create_range_partitions('test."RangeRel"', 'id', 1, 100, 3);
1344-
NOTICE: Copying data to partitions...
13451338
create_range_partitions
13461339
-------------------------
13471340
3
@@ -1358,7 +1351,6 @@ NOTICE: 0 rows copied from test."RangeRel_1"
13581351
(1 row)
13591352

13601353
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'id', 1, 300, 100);
1361-
NOTICE: Copying data to partitions...
13621354
create_partitions_from_range
13631355
------------------------------
13641356
3
@@ -1400,7 +1392,6 @@ CREATE TABLE range_rel (
14001392
INSERT INTO range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
14011393
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
14021394
NOTICE: sequence "range_rel_seq" does not exist, skipping
1403-
NOTICE: Copying data to partitions...
14041395
create_range_partitions
14051396
-------------------------
14061397
12
@@ -1530,7 +1521,6 @@ NOTICE: 44 rows copied from range_rel_1
15301521
(1 row)
15311522

15321523
SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
1533-
NOTICE: Copying data to partitions...
15341524
create_partitions_from_range
15351525
------------------------------
15361526
10
@@ -1544,7 +1534,6 @@ NOTICE: function public.range_rel_update_trigger_func() does not exist, skippin
15441534
(1 row)
15451535

15461536
SELECT create_partitions_from_range('range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);
1547-
NOTICE: Copying data to partitions...
15481537
create_partitions_from_range
15491538
------------------------------
15501539
12
@@ -1568,7 +1557,6 @@ ERROR: Relation 'messages' is referenced from other relations
15681557
ALTER TABLE replies DROP CONSTRAINT replies_message_id_fkey;
15691558
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
15701559
NOTICE: sequence "messages_seq" does not exist, skipping
1571-
NOTICE: Copying data to partitions...
15721560
create_range_partitions
15731561
-------------------------
15741562
2

‎hash.sql‎

Lines changed: 66 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -12,59 +12,60 @@
1212
* Creates hash partitions for specified relation
1313
*/
1414
CREATEOR REPLACE FUNCTION @extschema@.create_hash_partitions(
15-
relationREGCLASS
16-
,attributeTEXT
17-
,partitions_countINTEGER
15+
parent_relidREGCLASS,
16+
attributeTEXT,
17+
partitions_countINTEGER
1818
) RETURNSINTEGERAS
1919
$$
2020
DECLARE
21-
v_relnameTEXT;
22-
v_child_relnameTEXT;
23-
v_typeTEXT;
24-
v_plain_schemaTEXT;
25-
v_plain_relnameTEXT;
26-
v_hashfuncTEXT;
21+
v_child_relnameTEXT;
22+
v_typeTEXT;
23+
v_plain_schemaTEXT;
24+
v_plain_relnameTEXT;
25+
v_hashfuncTEXT;
26+
2727
BEGIN
28-
v_relname :=@extschema@.validate_relname(relation);
28+
PERFORM@extschema@.validate_relname(parent_relid);
2929
attribute :=lower(attribute);
30-
PERFORM @extschema@.common_relation_checks(relation, attribute);
30+
PERFORM @extschema@.common_relation_checks(parent_relid, attribute);
3131

32-
v_type := @extschema@.get_attribute_type_name(v_relname, attribute);
32+
v_type := @extschema@.get_attribute_type_name(parent_relid, attribute);
3333

3434
SELECT* INTO v_plain_schema, v_plain_relname
35-
FROM @extschema@.get_plain_schema_and_relname(relation);
35+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
3636

37-
v_hashfunc := @extschema@.get_type_hash_func(v_type::regtype::oid)::regproc;
37+
v_hashfunc := @extschema@.get_type_hash_func(v_type::regtype)::regproc;
3838

3939
/* Insert new entry to pathman config*/
4040
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype)
41-
VALUES (relation, attribute,1);
41+
VALUES (parent_relid, attribute,1);
4242

4343
/* Create partitions and update pg_pathman configuration*/
4444
FOR partnumIN0..partitions_count-1
4545
LOOP
4646
v_child_relname := format('%s.%s',
47-
v_plain_schema,
47+
quote_ident(v_plain_schema),
4848
quote_ident(v_plain_relname||'_'|| partnum));
4949

50-
EXECUTE format('CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)'
51-
, v_child_relname
52-
, v_relname);
53-
54-
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (@extschema@.get_hash(%s(%s), %s) = %s)'
55-
, v_child_relname
56-
, @extschema@.build_check_constraint_name(v_child_relname::regclass, attribute)
57-
, v_hashfunc
58-
, attribute
59-
, partitions_count
60-
, partnum);
50+
EXECUTE format('CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)',
51+
v_child_relname,
52+
parent_relid::text);
53+
54+
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (@extschema@.get_hash(%s(%s), %s) = %s)',
55+
v_child_relname,
56+
@extschema@.build_check_constraint_name(v_child_relname::regclass,
57+
attribute),
58+
v_hashfunc,
59+
attribute,
60+
partitions_count,
61+
partnum);
6162
END LOOP;
6263

6364
/* Notify backend about changes*/
64-
PERFORM @extschema@.on_create_partitions(relation::oid);
65+
PERFORM @extschema@.on_create_partitions(parent_relid);
6566

6667
/* Copy data*/
67-
PERFORM @extschema@.partition_data(relation);
68+
PERFORM @extschema@.partition_data(parent_relid);
6869

6970
RETURN partitions_count;
7071
END
@@ -75,7 +76,7 @@ SET client_min_messages = WARNING;
7576
* Creates an update trigger
7677
*/
7778
CREATEOR REPLACE FUNCTION @extschema@.create_hash_update_trigger(
78-
IN relationREGCLASS)
79+
parent_relidREGCLASS)
7980
RETURNS VOIDAS
8081
$$
8182
DECLARE
@@ -105,61 +106,62 @@ DECLARE
105106
END $body$
106107
LANGUAGE plpgsql';
107108

108-
triggerTEXT :='CREATE TRIGGER %s
109-
BEFORE UPDATE ON %s
110-
FOR EACH ROW EXECUTE PROCEDURE %s()';
111-
112-
att_namesTEXT;
113-
old_fieldsTEXT;
114-
new_fieldsTEXT;
115-
att_val_fmtTEXT;
116-
att_fmtTEXT;
117-
relidINTEGER;
118-
partitions_countINTEGER;
119-
attrTEXT;
120-
plain_schemaTEXT;
121-
plain_relnameTEXT;
122-
funcnameTEXT;
123-
triggernameTEXT;
124-
child_relname_formatTEXT;
125-
atttypeTEXT;
126-
hashfuncTEXT;
109+
triggerTEXT :='CREATE TRIGGER %s
110+
BEFORE UPDATE ON %s
111+
FOR EACH ROW EXECUTE PROCEDURE %s()';
112+
113+
att_namesTEXT;
114+
old_fieldsTEXT;
115+
new_fieldsTEXT;
116+
att_val_fmtTEXT;
117+
att_fmtTEXT;
118+
attrTEXT;
119+
plain_schemaTEXT;
120+
plain_relnameTEXT;
121+
funcnameTEXT;
122+
triggernameTEXT;
123+
child_relname_formatTEXT;
124+
atttypeTEXT;
125+
hashfuncTEXT;
126+
partitions_countINTEGER;
127127

128128
BEGIN
129129
SELECT* INTO plain_schema, plain_relname
130-
FROM @extschema@.get_plain_schema_and_relname(relation);
130+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
131131

132-
relid := relation::oid;
133132
SELECT string_agg(attname,','),
134133
string_agg('OLD.'|| attname,','),
135134
string_agg('NEW.'|| attname,','),
136-
string_agg('CASE WHEN NOT $'|| attnum||' IS NULL THEN'|| attname||' = $'|| attnum||
137-
' ELSE'|| attname||' IS NULL END',' AND'),
135+
string_agg('CASE WHEN NOT $'|| attnum||' IS NULL THEN'||
136+
attname||' = $'|| attnum||''||
137+
'ELSE'||
138+
attname||' IS NULL END',
139+
' AND'),
138140
string_agg('$'|| attnum,',')
139141
FROM pg_attribute
140-
WHERE attrelid=relidAND attnum>0
142+
WHERE attrelid= parent_relidAND attnum>0
141143
INTO att_names,
142144
old_fields,
143145
new_fields,
144146
att_val_fmt,
145147
att_fmt;
146148

147-
attr := attnameFROM @extschema@.pathman_configWHERE partrel=relation;
149+
attr := attnameFROM @extschema@.pathman_configWHERE partrel=parent_relid;
148150

149151
IF attr ISNULL THEN
150-
RAISE EXCEPTION'Table % is not partitioned', quote_ident(relation::TEXT);
152+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(parent_relid::TEXT);
151153
END IF;
152154

153-
partitions_count :=COUNT(*)FROM pg_inheritsWHERE inhparent=relation::oid;
155+
partitions_count :=COUNT(*)FROM pg_inheritsWHERE inhparent=parent_relid::oid;
154156

155157
/* Function name, trigger name and child relname template*/
156158
funcname := plain_schema||'.'|| quote_ident(format('%s_update_trigger_func', plain_relname));
157159
child_relname_format := plain_schema||'.'|| quote_ident(plain_relname||'_%s');
158160
triggername := quote_ident(format('%s_%s_update_trigger', plain_schema, plain_relname));
159161

160162
/* base hash function for type*/
161-
atttype := @extschema@.get_attribute_type_name(relation, attr);
162-
hashfunc := @extschema@.get_type_hash_func(atttype::regtype::oid)::regproc;
163+
atttype := @extschema@.get_attribute_type_name(parent_relid, attr);
164+
hashfunc := @extschema@.get_type_hash_func(atttype::regtype)::regproc;
163165

164166
/* Format function definition and execute it*/
165167
func := format(func, funcname, attr, partitions_count, att_val_fmt,
@@ -169,18 +171,18 @@ BEGIN
169171
/* Create triggers on child relations*/
170172
FOR numIN0..partitions_count-1
171173
LOOP
172-
EXECUTE format(trigger
173-
,triggername
174-
,format(child_relname_format, num)
175-
,funcname);
174+
EXECUTE format(trigger,
175+
triggername,
176+
format(child_relname_format, num),
177+
funcname);
176178
END LOOP;
177179
END
178180
$$ LANGUAGE plpgsql;
179181

180182
/*
181183
* Returns hash function OID for specified type
182184
*/
183-
CREATEOR REPLACE FUNCTION @extschema@.get_type_hash_func(OID)
185+
CREATEOR REPLACE FUNCTION @extschema@.get_type_hash_func(REGTYPE)
184186
RETURNSOIDAS'pg_pathman','get_type_hash_func'
185187
LANGUAGE C STRICT;
186188

0 commit comments

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