Skip to content

Commit 8a1e76c

Browse files
zilderzilder
committed
pathman: initial commit
1 parent 47f9783 commit 8a1e76c

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+
ifdef USE_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+
CREATE TABLE IF NOT EXISTS pg_pathman_rels (
5+
id SERIAL PRIMARY KEY,
6+
relname VARCHAR(127),
7+
attr VARCHAR(127),
8+
parttype INTEGER
9+
);
10+
11+
/*
12+
* Relations using hash strategy
13+
*/
14+
CREATE TABLE IF NOT EXISTS pg_pathman_hash_rels (
15+
id SERIAL PRIMARY KEY,
16+
parent VARCHAR(127),
17+
hash INTEGER,
18+
child VARCHAR(127)
19+
);
20+
21+
/*
22+
* Creates hash partitions for specified relation
23+
*/
24+
CREATE OR REPLACE FUNCTION public.create_hash_partitions(
25+
IN relation TEXT, IN attribute TEXT, IN partitions_count INTEGER
26+
) RETURNS VOID AS
27+
$$
28+
DECLARE
29+
row INTEGER;
30+
q TEXT;
31+
relid INTEGER;
32+
attnum INTEGER;
33+
child_oid INTEGER;
34+
BEGIN
35+
relid := relfilenode FROM pg_class WHERE relname = relation;
36+
attnum := pg_attribute.attnum FROM pg_attribute WHERE attrelid = relid AND attname = attribute;
37+
38+
IF EXISTS (SELECT * FROM pg_pathman_rels WHERE 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 partnum IN 0..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 REPLACE FUNCTION public.create_hash_trigger(IN relation TEXT, IN attr TEXT, IN partitions_count INTEGER)
63+
RETURNS VOID AS
64+
$$
65+
DECLARE
66+
func TEXT := '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+
trigger TEXT := 'CREATE TRIGGER %s_trigger ' ||
71+
'BEFORE INSERT ON %1$s ' ||
72+
'FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_trigger_func();';
73+
relid INTEGER;
74+
fields TEXT;
75+
fields_format TEXT;
76+
insert_stmt TEXT;
77+
execute_stmt TEXT;
78+
BEGIN
79+
/* drop trigger and corresponding function */
80+
PERFORM drop_hash_trigger(relation);
81+
82+
/* determine fields for INSERT */
83+
relid := relfilenode FROM pg_class WHERE relname = relation;
84+
SELECT string_agg('NEW.' || attname, ', '), string_agg('$' || attnum, ', ')
85+
FROM pg_attribute
86+
WHERE attrelid=relid AND 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 REPLACE FUNCTION public.drop_hash_partitions(IN relation TEXT)
106+
RETURNS VOID AS
107+
$$
108+
DECLARE
109+
relid INTEGER;
110+
partitions_count INTEGER;
111+
q TEXT := 'DROP TABLE %s_%s';
112+
BEGIN
113+
/* Drop trigger first */
114+
PERFORM drop_hash_trigger(relation);
115+
116+
relid := relfilenode FROM pg_class WHERE relname = relation;
117+
partitions_count := COUNT(*) FROM pg_pathman_hash_rels WHERE parent = relation;
118+
119+
FOR partnum IN 0..partitions_count-1
120+
LOOP
121+
EXECUTE format(q, relation, partnum);
122+
END LOOP;
123+
124+
DELETE FROM pg_pathman_rels WHERE relname = relation;
125+
DELETE FROM pg_pathman_hash_rels WHERE 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 REPLACE FUNCTION public.drop_hash_trigger(IN relation TEXT)
136+
RETURNS VOID AS
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 REPLACE FUNCTION pg_pathman_on_create_partitions(relid INTEGER)
145+
RETURNS VOID AS 'pathman', 'on_partitions_created' LANGUAGE C STRICT;
146+
147+
CREATE OR REPLACE FUNCTION pg_pathman_on_remove_partitions(relid INTEGER)
148+
RETURNS VOID AS '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)