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

Commit8a1e76c

Browse files
committed
pathman: initial commit
1 parent47f9783 commit8a1e76c

File tree

4 files changed

+936
-0
lines changed

4 files changed

+936
-0
lines changed

‎contrib/pathman/Makefile

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
# contrib/pathman/Makefile
2+
3+
MODULE_big = pathman
4+
OBJS = pathman.o$(WIN32RES)
5+
6+
EXTENSION = pathman
7+
DATA = pathman--0.1.sql
8+
PGFILEDESC = "pathman - partitioning tool"
9+
10+
REGRESS = pathman
11+
12+
ifdefUSE_PGXS
13+
PG_CONFIG = pg_config
14+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
15+
include$(PGXS)
16+
else
17+
subdir = contrib/pathman
18+
top_builddir = ../..
19+
include$(top_builddir)/src/Makefile.global
20+
include$(top_srcdir)/contrib/contrib-global.mk
21+
endif

‎contrib/pathman/pathman--0.1.sql

Lines changed: 187 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,187 @@
1+
/*
2+
* Relations using partitioning
3+
*/
4+
CREATETABLEIF NOT EXISTS pg_pathman_rels (
5+
idSERIALPRIMARY KEY,
6+
relnameVARCHAR(127),
7+
attrVARCHAR(127),
8+
parttypeINTEGER
9+
);
10+
11+
/*
12+
* Relations using hash strategy
13+
*/
14+
CREATETABLEIF NOT EXISTS pg_pathman_hash_rels (
15+
idSERIALPRIMARY KEY,
16+
parentVARCHAR(127),
17+
hashINTEGER,
18+
childVARCHAR(127)
19+
);
20+
21+
/*
22+
* Creates hash partitions for specified relation
23+
*/
24+
CREATE OR REPLACEFUNCTIONpublic.create_hash_partitions(
25+
IN relationTEXT,IN attributeTEXT,IN partitions_countINTEGER
26+
) RETURNS VOIDAS
27+
$$
28+
DECLARE
29+
rowINTEGER;
30+
qTEXT;
31+
relidINTEGER;
32+
attnumINTEGER;
33+
child_oidINTEGER;
34+
BEGIN
35+
relid := relfilenodeFROM pg_classWHERE relname= relation;
36+
attnum :=pg_attribute.attnumFROM pg_attributeWHERE attrelid= relidAND attname= attribute;
37+
38+
IF EXISTS (SELECT*FROM pg_pathman_relsWHERE relname= relation) THEN
39+
RAISE EXCEPTION'Reltion "%s" has already been partitioned', relation;
40+
END IF;
41+
42+
/* Create partitions and update pg_pathman configuration*/
43+
FOR partnumIN0..partitions_count-1
44+
LOOP
45+
EXECUTE format('CREATE TABLE %s_%s (LIKE %1$s INCLUDING ALL)', relation, partnum);
46+
-- child_oid := relfilenode FROM pg_class WHERE relname = format('%s_%s', relation, partnum);
47+
INSERT INTO pg_pathman_hash_rels (parent, hash, child)
48+
VALUES (relation, partnum, format('%s_%s', relation, partnum));
49+
END LOOP;
50+
INSERT INTO pg_pathman_rels (relname, attr, parttype)VALUES (relation, attribute,1);
51+
52+
/* Create trigger*/
53+
PERFORM create_hash_trigger(relation, attribute, partitions_count);
54+
/* Notify backend about changes*/
55+
PERFORM pg_pathman_on_create_partitions(relid);
56+
END
57+
$$ LANGUAGE plpgsql;
58+
59+
/*
60+
* Creates hash trigger for specified relation
61+
*/
62+
CREATE OR REPLACEFUNCTIONpublic.create_hash_trigger(IN relationTEXT,IN attrTEXT,IN partitions_countINTEGER)
63+
RETURNS VOIDAS
64+
$$
65+
DECLARE
66+
funcTEXT :='CREATE OR REPLACE FUNCTION %s_hash_trigger_func()'||
67+
'RETURNS TRIGGER AS $body$ DECLARE hash INTEGER; BEGIN'||
68+
'hash := NEW.%s %% %s; %s'||
69+
'RETURN NULL; END $body$ LANGUAGE plpgsql;';
70+
triggerTEXT :='CREATE TRIGGER %s_trigger'||
71+
'BEFORE INSERT ON %1$s'||
72+
'FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_trigger_func();';
73+
relidINTEGER;
74+
fieldsTEXT;
75+
fields_formatTEXT;
76+
insert_stmtTEXT;
77+
execute_stmtTEXT;
78+
BEGIN
79+
/* drop trigger and corresponding function*/
80+
PERFORM drop_hash_trigger(relation);
81+
82+
/* determine fields for INSERT*/
83+
relid := relfilenodeFROM pg_classWHERE relname= relation;
84+
SELECT string_agg('NEW.'|| attname,','), string_agg('$'|| attnum,',')
85+
FROM pg_attribute
86+
WHERE attrelid=relidAND attnum>0
87+
INTO fields, fields_format;
88+
89+
/* generate INSERT statement for trigger*/
90+
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s VALUES (%s)'', hash) USING %s;',
91+
relation, fields_format, fields);
92+
93+
/* format and create new trigger for relation*/
94+
func := format(func, relation, attr, partitions_count, insert_stmt);
95+
96+
trigger := format(trigger, relation);
97+
EXECUTE func;
98+
EXECUTE trigger;
99+
END
100+
$$ LANGUAGE plpgsql;
101+
102+
/*
103+
* Drops all partitions for specified relation
104+
*/
105+
CREATE OR REPLACEFUNCTIONpublic.drop_hash_partitions(IN relationTEXT)
106+
RETURNS VOIDAS
107+
$$
108+
DECLARE
109+
relidINTEGER;
110+
partitions_countINTEGER;
111+
qTEXT :='DROP TABLE %s_%s';
112+
BEGIN
113+
/* Drop trigger first*/
114+
PERFORM drop_hash_trigger(relation);
115+
116+
relid := relfilenodeFROM pg_classWHERE relname= relation;
117+
partitions_count :=COUNT(*)FROM pg_pathman_hash_relsWHERE parent= relation;
118+
119+
FOR partnumIN0..partitions_count-1
120+
LOOP
121+
EXECUTE format(q, relation, partnum);
122+
END LOOP;
123+
124+
DELETEFROM pg_pathman_relsWHERE relname= relation;
125+
DELETEFROM pg_pathman_hash_relsWHERE parent= relation;
126+
127+
/* Notify backend about changes*/
128+
PERFORM pg_pathman_on_remove_partitions(relid);
129+
END
130+
$$ LANGUAGE plpgsql;
131+
132+
/*
133+
* Drops hash trigger
134+
*/
135+
CREATE OR REPLACEFUNCTIONpublic.drop_hash_trigger(IN relationTEXT)
136+
RETURNS VOIDAS
137+
$$
138+
BEGIN
139+
EXECUTE format('DROP TRIGGER IF EXISTS %s_trigger ON %1$s', relation);
140+
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_trigger_func()', relation);
141+
END
142+
$$ LANGUAGE plpgsql;
143+
144+
CREATE OR REPLACEFUNCTIONpg_pathman_on_create_partitions(relidINTEGER)
145+
RETURNS VOIDAS'pathman','on_partitions_created' LANGUAGE C STRICT;
146+
147+
CREATE OR REPLACEFUNCTIONpg_pathman_on_remove_partitions(relidINTEGER)
148+
RETURNS VOIDAS'pathman','on_partitions_removed' LANGUAGE C STRICT;
149+
150+
151+
-- CREATE OR REPLACE FUNCTION sample_rel_trigger_func()
152+
-- RETURNS TRIGGER AS $$
153+
-- DECLARE
154+
-- hash integer := 0;
155+
-- -- q TEXT = 'INSERT INTO sample_rel_% VALUES (NEW.*)';
156+
-- BEGIN
157+
-- hash := NEW.val % 1000;
158+
-- EXECUTE format('INSERT INTO sample_rel_%s VALUES ($1, $2)', hash)
159+
-- USING NEW.id, NEW.val;
160+
-- RETURN NULL;
161+
-- END
162+
-- $$ LANGUAGE plpgsql;
163+
164+
-- CREATE TRIGGER sample_rel_trigger
165+
-- BEFORE INSERT ON sample_rel
166+
-- FOR EACH ROW EXECUTE PROCEDURE sample_rel_trigger_func();
167+
168+
169+
170+
/* INHERITANCE TEST*/
171+
-- CREATE OR REPLACE FUNCTION public.create_children_tables(IN relation TEXT)
172+
-- RETURNS INTEGER AS $$
173+
-- DECLARE
174+
-- q TEXT := 'CREATE TABLE %s_%s (CHECK (val IN (%s))) INHERITS (%s)';
175+
-- BEGIN
176+
-- FOR partnum IN 0..999
177+
-- LOOP
178+
-- EXECUTE format(q, relation, partnum, partnum, relation);
179+
-- END LOOP;
180+
-- RETURN 0;
181+
-- END
182+
-- $$ LANGUAGE plpgsql;
183+
184+
/* sample data*/
185+
-- insert into pg_pathman_rels (oid, attnum, parttype) values (49350, 2, 1);
186+
-- insert into pg_pathman_hash_rels (parent_oid, hash, child_oid) values (49350, 1, 49355);
187+
-- insert into pg_pathman_hash_rels (parent_oid, hash, child_oid) values (49350, 0, 49360);

0 commit comments

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