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

Commit4a264d2

Browse files
committed
pathman: binary search fixes and pl-functions fixes
1 parent5d319bd commit4a264d2

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
elseif (value<re->min)
400400
endidx=i-1;
401-
elseif (value>re->max)
401+
elseif (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 VOIDAS
6060
$$
6161
DECLARE
62-
funcTEXT :='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-
triggerTEXT :='CREATE TRIGGER %s_insert_trigger'||
67-
'BEFORE INSERT ON %1$s'||
68-
'FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_insert_trigger_func();';
62+
funcTEXT :='
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+
triggerTEXT :='
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
relidINTEGER;
7077
fieldsTEXT;
7178
fields_formatTEXT;
@@ -146,17 +153,21 @@ CREATE OR REPLACE FUNCTION public.create_hash_update_trigger(
146153
RETURNS VOIDAS
147154
$$
148155
DECLARE
149-
funcTEXT :='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+
funcTEXT :='
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
triggerTEXT :='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 REPLACEFUNCTIONcreate_range_partitions_time(
4+
CREATE OR REPLACEFUNCTIONcreate_range_partitions(
55
v_relationTEXT
66
, v_attributeTEXT
77
, v_start_timestampTIMESTAMPTZ
@@ -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+
SELECTcurrent_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 REPLACEFUNCTIONappend_range_partitions_time(
52+
CREATE OR REPLACEFUNCTIONappend_range_partitions(
4853
v_relationTEXT
49-
, v_start_timestampTIMESTAMPTZ
5054
, v_interval INTERVAL
5155
, v_premakeINTEGER)
5256
RETURNS VOIDAS
5357
$$
5458
DECLARE
5559
v_part_timestampTIMESTAMPTZ;
56-
v_part_relnameTEXT;
5760
v_partnumINTEGER;
5861
v_relidINTEGER;
5962
BEGIN
6063
SELECT relfilenode INTO v_relid
6164
FROM pg_classWHERE relname= v_relation;
6265

63-
IF v_start_timestamp!=NULL THEN
64-
v_part_timestamp := v_start_timestamp;
65-
ELSE
66-
SELECTcurrent_date INTO v_part_timestamp;
67-
END IF;
66+
SELECTmax('max_dt') INTO v_part_timestampFROM pg_pathman_range_rels;
6867

6968
/* Create partitions and update pg_pathman configuration*/
7069
FOR v_partnumIN0..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_tablesWHERE 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 REPLACEFUNCTIONcreate_single_range_partition(
85+
v_parent_relnameTEXT
86+
, v_start_timestampTIMESTAMPTZ
87+
, v_interval INTERVAL)
88+
RETURNS VOIDAS
89+
$$
90+
DECLARE
91+
v_child_relnameTEXT;
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_tablesWHERE 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 %sINHERIT %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 REPLACEFUNCTIONpublic.drop_range_partitions(IN relationTEXT)
181+
CREATE OR REPLACEFUNCTIONdrop_range_partitions(IN relationTEXT)
164182
RETURNS VOIDAS
165183
$$
166184
DECLARE
@@ -178,7 +196,7 @@ BEGIN
178196
END LOOP;
179197

180198
DELETEFROM pg_pathman_relsWHERE relname= relation;
181-
DELETEFROMpg_pathman_hash_relsWHERE parent= relation;
199+
DELETEFROMpg_pathman_range_relsWHERE 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 REPLACEFUNCTIONpublic.drop_range_triggers(IN relationTEXT)
209+
CREATE OR REPLACEFUNCTIONdrop_range_triggers(IN relationTEXT)
192210
RETURNS VOIDAS
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)
[8]ページ先頭
©2009-2025 Movatter.jp