Skip to content

Commit 4a264d2

Browse files
zilderzilder
committed
pathman: binary search fixes and pl-functions fixes
1 parent 5d319bd commit 4a264d2

File tree

3 files changed

+87
-58
lines changed

3 files changed

+87
-58
lines changed

‎contrib/pathman/pathman.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -391,14 +391,14 @@ handle_binary_opexpr(const PartRelationInfo *prel, const OpExpr *expr,
391391
if (i >= 0 && i < rangerel->nranges)
392392
{
393393
re = &rangerel->ranges[i];
394-
if (re->min <= value && re->max >= value)
394+
if (re->min <= value && value < re->max)
395395
{
396396
found = true;
397397
break;
398398
}
399399
else if (value < re->min)
400400
endidx = i - 1;
401-
else if (value > re->max)
401+
else if (value >= re->max)
402402
startidx = i + 1;
403403
}
404404
/* for debug's sake */

‎contrib/pathman/sql/hash.sql

Lines changed: 29 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -59,13 +59,20 @@ CREATE OR REPLACE FUNCTION public.create_hash_insert_trigger(
5959
RETURNS VOID AS
6060
$$
6161
DECLARE
62-
func TEXT := 'CREATE OR REPLACE FUNCTION %s_hash_insert_trigger_func() ' ||
63-
'RETURNS TRIGGER AS $body$ DECLARE hash INTEGER; BEGIN ' ||
64-
'hash := NEW.%s %% %s; %s ' ||
65-
'RETURN NULL; END $body$ LANGUAGE plpgsql;';
66-
trigger TEXT := 'CREATE TRIGGER %s_insert_trigger ' ||
67-
'BEFORE INSERT ON %1$s ' ||
68-
'FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_insert_trigger_func();';
62+
func TEXT := '
63+
CREATE OR REPLACE FUNCTION %s_hash_insert_trigger_func()
64+
RETURNS TRIGGER AS $body$
65+
DECLARE
66+
hash INTEGER;
67+
BEGIN
68+
hash := NEW.%s %% %s;
69+
%s
70+
RETURN NULL;
71+
END $body$ LANGUAGE plpgsql;';
72+
trigger TEXT := '
73+
CREATE TRIGGER %s_insert_trigger
74+
BEFORE INSERT ON %1$s
75+
FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_insert_trigger_func();';
6976
relid INTEGER;
7077
fields TEXT;
7178
fields_format TEXT;
@@ -146,17 +153,21 @@ CREATE OR REPLACE FUNCTION public.create_hash_update_trigger(
146153
RETURNS VOID AS
147154
$$
148155
DECLARE
149-
func TEXT := 'CREATE OR REPLACE FUNCTION %s_update_trigger_func() RETURNS TRIGGER AS ' ||
150-
'$body$ DECLARE old_hash INTEGER; new_hash INTEGER; q TEXT; BEGIN ' ||
151-
'old_hash := OLD.%2$s %% %3$s; ' ||
152-
'new_hash := NEW.%2$s %% %3$s; ' ||
153-
'IF old_hash = new_hash THEN RETURN NEW; END IF; ' ||
154-
'q := format(''DELETE FROM %1$s_%%s WHERE %4$s'', old_hash); ' ||
155-
'EXECUTE q USING %5$s; ' ||
156-
'q := format(''INSERT INTO %1$s_%%s VALUES (%6$s)'', new_hash); ' ||
157-
'EXECUTE q USING %7$s; ' ||
158-
'RETURN NULL; ' ||
159-
'END $body$ LANGUAGE plpgsql';
156+
func TEXT := '
157+
CREATE OR REPLACE FUNCTION %s_update_trigger_func()
158+
RETURNS TRIGGER AS
159+
$body$
160+
DECLARE old_hash INTEGER; new_hash INTEGER; q TEXT;
161+
BEGIN
162+
old_hash := OLD.%2$s %% %3$s;
163+
new_hash := NEW.%2$s %% %3$s;
164+
IF old_hash = new_hash THEN RETURN NEW; END IF;
165+
q := format(''DELETE FROM %1$s_%%s WHERE %4$s'', old_hash);
166+
EXECUTE q USING %5$s;
167+
q := format(''INSERT INTO %1$s_%%s VALUES (%6$s)'', new_hash);
168+
EXECUTE q USING %7$s;
169+
RETURN NULL;
170+
END $body$ LANGUAGE plpgsql';
160171
trigger TEXT := 'CREATE TRIGGER %s_update_trigger ' ||
161172
'BEFORE UPDATE ON %1$s_%s ' ||
162173
'FOR EACH ROW EXECUTE PROCEDURE %1$s_update_trigger_func()';

‎contrib/pathman/sql/range.sql

Lines changed: 56 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/*
22
* Creates RANGE partitions for specified relation
33
*/
4-
CREATE OR REPLACE FUNCTION create_range_partitions_time(
4+
CREATE OR REPLACE FUNCTION create_range_partitions(
55
v_relation TEXT
66
, v_attribute TEXT
77
, v_start_timestamp TIMESTAMPTZ
@@ -19,10 +19,15 @@ BEGIN
1919
RAISE EXCEPTION 'Reltion "%s" has already been partitioned', v_relation;
2020
END IF;
2121

22-
PERFORM append_range_partitions_time(v_relation
23-
, v_start_timestamp
24-
, v_interval
25-
, v_premake);
22+
IF v_start_timestamp != NULL THEN
23+
v_start_timestamp := v_start_timestamp;
24+
ELSE
25+
SELECT current_date INTO v_start_timestamp;
26+
END IF;
27+
28+
PERFORM create_single_range_partition(v_relation
29+
, v_start_timestamp
30+
, v_interval);
2631

2732
INSERT INTO pg_pathman_rels (
2833
relname
@@ -44,60 +49,74 @@ $$ LANGUAGE plpgsql;
4449
/*
4550
* Create additional partitions for existing RANGE partitioning
4651
*/
47-
CREATE OR REPLACE FUNCTION append_range_partitions_time(
52+
CREATE OR REPLACE FUNCTION append_range_partitions(
4853
v_relation TEXT
49-
, v_start_timestamp TIMESTAMPTZ
5054
, v_interval INTERVAL
5155
, v_premake INTEGER)
5256
RETURNS VOID AS
5357
$$
5458
DECLARE
5559
v_part_timestamp TIMESTAMPTZ;
56-
v_part_relname TEXT;
5760
v_partnum INTEGER;
5861
v_relid INTEGER;
5962
BEGIN
6063
SELECT relfilenode INTO v_relid
6164
FROM pg_class WHERE relname = v_relation;
6265

63-
IF v_start_timestamp != NULL THEN
64-
v_part_timestamp := v_start_timestamp;
65-
ELSE
66-
SELECT current_date INTO v_part_timestamp;
67-
END IF;
66+
SELECT max('max_dt') INTO v_part_timestamp FROM pg_pathman_range_rels;
6867

6968
/* Create partitions and update pg_pathman configuration */
7069
FOR v_partnum IN 0..v_premake-1
7170
LOOP
72-
v_part_relname := format('%s_%s'
73-
, v_relation
74-
, to_char(v_part_timestamp, 'YYYY_MM_DD'));
71+
PERFORM create_single_range_partition(v_relation
72+
, v_part_timestamp
73+
, v_interval);
7574
v_part_timestamp := v_part_timestamp + v_interval;
75+
END LOOP;
7676

77-
/* Skip existing partitions */
78-
IF EXISTS (SELECT * FROM pg_tables WHERE tablename = v_part_relname) THEN
79-
CONTINUE;
80-
END IF;
77+
PERFORM pg_pathman_on_update_partitions(v_relid);
78+
END
79+
$$ LANGUAGE plpgsql;
8180

82-
EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING ALL)'
83-
, v_part_relname
84-
, v_relation);
81+
/*
82+
*
83+
*/
84+
CREATE OR REPLACE FUNCTION create_single_range_partition(
85+
v_parent_relname TEXT
86+
, v_start_timestamp TIMESTAMPTZ
87+
, v_interval INTERVAL)
88+
RETURNS VOID AS
89+
$$
90+
DECLARE
91+
v_child_relname TEXT;
92+
BEGIN
93+
v_child_relname := format('%s_%s'
94+
, v_parent_relname
95+
, to_char(v_start_timestamp, 'YYYY_MM_DD'));
96+
97+
/* Skip existing partitions */
98+
IF EXISTS (SELECT * FROM pg_tables WHERE tablename = v_child_relname) THEN
99+
RAISE WARNING 'Relation % already exists, skipping...', v_child_relname;
100+
RETURN;
101+
END IF;
85102

86-
EXECUTE format('ALTER TABLE %s INHERIT %s'
87-
, v_part_relname
88-
, v_relation);
103+
EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING ALL)'
104+
, v_child_relname
105+
, v_parent_relname);
89106

90-
INSERT INTO pg_pathman_range_rels (parent, min_dt, max_dt, child)
91-
VALUES (v_relation
92-
, v_part_timestamp
93-
, v_part_timestamp + v_interval
94-
, v_part_relname);
95-
END LOOP;
107+
EXECUTE format('ALTER TABLE %s INHERIT %s'
108+
, v_child_relname
109+
, v_parent_relname);
96110

97-
PERFORM pg_pathman_on_update_partitions(v_relid);
111+
INSERT INTO pg_pathman_range_rels (parent, min_dt, max_dt, child)
112+
VALUES (v_parent_relname
113+
, v_start_timestamp
114+
, v_start_timestamp + v_interval
115+
, v_child_relname);
98116
END
99117
$$ LANGUAGE plpgsql;
100118

119+
101120
/*
102121
* Creates range partitioning insert trigger
103122
*/
@@ -152,15 +171,14 @@ BEGIN
152171

153172
EXECUTE v_func;
154173
EXECUTE v_trigger;
155-
-- RETURN v_func;
156174
RETURN;
157175
END
158176
$$ LANGUAGE plpgsql;
159177

160178
/*
161179
* Drop partitions
162180
*/
163-
CREATE OR REPLACE FUNCTION public.drop_range_partitions(IN relation TEXT)
181+
CREATE OR REPLACE FUNCTION drop_range_partitions(IN relation TEXT)
164182
RETURNS VOID AS
165183
$$
166184
DECLARE
@@ -178,7 +196,7 @@ BEGIN
178196
END LOOP;
179197

180198
DELETE FROM pg_pathman_rels WHERE relname = relation;
181-
DELETE FROM pg_pathman_hash_rels WHERE parent = relation;
199+
DELETE FROM pg_pathman_range_rels WHERE parent = relation;
182200

183201
/* Notify backend about changes */
184202
PERFORM pg_pathman_on_remove_partitions(v_relid);
@@ -188,10 +206,10 @@ $$ LANGUAGE plpgsql;
188206
/*
189207
* Drop trigger
190208
*/
191-
CREATE OR REPLACE FUNCTION public.drop_range_triggers(IN relation TEXT)
209+
CREATE OR REPLACE FUNCTION drop_range_triggers(IN relation TEXT)
192210
RETURNS VOID AS
193211
$$
194212
BEGIN
195-
EXECUTE format('DROP TRIGGER IF EXISTS %s_range_insert_trigger_func ON %1$s CASCADE', relation);
213+
EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %1$s CASCADE', relation);
196214
END
197215
$$ LANGUAGE plpgsql;

0 commit comments

Comments
 (0)