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

Commitc980be9

Browse files
committed
pathman: overlap check added for some functions; fixed issue with inserting to missing partitions (holes)
1 parent45b88a5 commitc980be9

File tree

8 files changed

+191
-38
lines changed

8 files changed

+191
-38
lines changed

‎contrib/pg_pathman/README.rus.md‎

Lines changed: 46 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -154,54 +154,71 @@ disable_partitioning(relation TEXT)
154154

155155
##Примеры использования
156156
###HASH
157-
Рассмотрим пример секционирования таблицы, используя HASH-стратегию на примере таблицы.
157+
Рассмотрим пример секционирования таблицы, используя HASH-стратегию на примере таблицы товаров.
158158
```
159-
CREATE TABLE hash_rel (
160-
id SERIAL PRIMARY KEY,
161-
value INTEGER);
162-
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
159+
CREATE TABLE items (
160+
id SERIAL PRIMARY KEY,
161+
name TEXT,
162+
code BIGINT);
163+
164+
INSERT INTO items (id, name, code)
165+
SELECT g, md5(g::text), random() * 100000
166+
FROM generate_series(1, 100000) as g;
163167
```
164168
Если дочерние секции подразумевают наличие индексов, то стоит их создать в родительской таблице до разбиения. Тогда при разбиении pg_pathman автоматически создаст соответствующие индексы в дочерних.таблицах. Разобьем таблицу`hash_rel` на 100 секций по полю`value`:
165169
```
166-
SELECT create_hash_partitions('hash_rel', 'value', 100);
170+
SELECT create_hash_partitions('items', 'id', 100);
167171
```
168172
Пример построения плана для запроса с фильтрацией по ключевому полю:
169173
```
170-
SELECT * FROM hash_rel WHERE value = 1234;
171-
id | value
172-
------+-------
173-
1234 | 1234
174+
SELECT * FROM items WHERE id = 1234;
175+
id | name | code
176+
------+----------------------------------+------
177+
1234 | 81dc9bdb52d04dc20036dbd8313ed055 | 1855
178+
(1 row)
174179
175-
EXPLAIN SELECT * FROMhash_rel WHEREvalue = 1234;
176-
QUERY PLAN
177-
-----------------------------------------------------------------
178-
Append (cost=0.00..2.00 rows=0 width=0)
179-
->Seq Scan onhash_rel_34 (cost=0.00..2.00 rows=0 width=0)
180-
Filter: (value = 1234)
180+
EXPLAIN SELECT * FROMitems WHEREid = 1234;
181+
QUERY PLAN
182+
------------------------------------------------------------------------------------
183+
Append (cost=0.28..8.29 rows=0 width=0)
184+
->Index Scanusing items_34_pkeyonitems_34 (cost=0.28..8.29 rows=0 width=0)
185+
Index Cond: (id = 1234)
181186
```
182187
Стоит отметить, что pg_pathman исключает из плана запроса родительскую таблицу, и чтобы получить данные из нее, следует использовать модификатор ONLY:
183188
```
184-
EXPLAIN SELECT * FROM ONLYhash_rel;
185-
QUERY PLAN
186-
--------------------------------------------------------
187-
Seq Scan onhash_rel (cost=0.00..0.00 rows=1 width=8)
189+
EXPLAIN SELECT * FROM ONLYitems;
190+
QUERY PLAN
191+
------------------------------------------------------
192+
Seq Scan onitems (cost=0.00..0.00 rows=1 width=45)
188193
```
189194

190195
###RANGE
191-
Пример секционированиятаблицыс использованием стратегии RANGE.
196+
Рассмотрим пример разбиениятаблицыпо диапазону дат. Пусть у нас имеется таблица логов:
192197
```
193-
CREATE TABLE range_rel (
194-
id SERIAL PRIMARY KEY,
195-
dt TIMESTAMP);
196-
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2014-12-31'::date, '1 day') as g;
198+
CREATE TABLE journal (
199+
id SERIAL PRIMARY KEY,
200+
dt TIMESTAMP NOT NULL,
201+
level INTEGER,
202+
msg TEXT
203+
);
204+
CREATE INDEX ON journal(dt);
205+
206+
INSERT INTO journal (dt, level, msg)
207+
SELECT g, random()*6, md5(g::text)
208+
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g;
197209
```
198-
Разобьем таблицу на60 секций так, чтобы каждая секция содержала данные за одинмесяц:
210+
Разобьем таблицу на365 секций так, чтобы каждая секция содержала данные за одиндень:
199211
```
200-
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1month'::interval, 60);
212+
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1day'::interval);
201213
```
202-
Объединим секции первые две секции:
214+
Новые секции добавляются автоматически при вставке новых записей в непокрытую область. Однако есть возможность добавлять секции вручную. Для этого можно воспользоваться следующими функциями:
215+
```
216+
SELECT add_partition()
217+
```
218+
219+
Объединим первые две секции:
203220
```
204-
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');
221+
SELECT merge_range_partitions('journal_1', 'journal_2');
205222
```
206223
Разделим первую секцию на две по дате '2010-02-15':
207224
```

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -498,6 +498,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' A
498498
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
499499
(3 rows)
500500

501+
SELECT pathman.add_range_partition('test.range_rel', '2014-12-01'::DATE, '2015-01-02'::DATE);
502+
ERROR: Specified range overlaps with existing partitions P0001
501503
SELECT pathman.add_range_partition('test.range_rel', '2014-12-01'::DATE, '2015-01-01'::DATE);
502504
NOTICE: Done!
503505
add_range_partition
@@ -516,6 +518,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' A
516518
(5 rows)
517519

518520
CREATE TABLE test.range_rel_archive (LIKE test.range_rel INCLUDING ALL);
521+
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_archive', '2014-01-01'::DATE, '2015-01-01'::DATE);
522+
ERROR: Specified range overlaps with existing partitions P0001
519523
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_archive', '2014-01-01'::DATE, '2014-12-01'::DATE);
520524
attach_range_partition
521525
------------------------
@@ -656,6 +660,59 @@ SELECT * FROM pathman.pathman_config;
656660
----+---------+---------+----------+----------------
657661
(0 rows)
658662

663+
/* Check overlaps */
664+
CREATE TABLE test.num_range_rel (
665+
id SERIAL PRIMARY KEY,
666+
txt TEXT);
667+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 1000, 1000, 4);
668+
NOTICE: Copying data to partitions...
669+
create_range_partitions
670+
-------------------------
671+
4
672+
(1 row)
673+
674+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 4001, 5000);
675+
check_overlap
676+
---------------
677+
t
678+
(1 row)
679+
680+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 4000, 5000);
681+
check_overlap
682+
---------------
683+
t
684+
(1 row)
685+
686+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 3999, 5000);
687+
check_overlap
688+
---------------
689+
t
690+
(1 row)
691+
692+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 3000, 3500);
693+
check_overlap
694+
---------------
695+
t
696+
(1 row)
697+
698+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 0, 999);
699+
check_overlap
700+
---------------
701+
f
702+
(1 row)
703+
704+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 0, 1000);
705+
check_overlap
706+
---------------
707+
f
708+
(1 row)
709+
710+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 0, 1001);
711+
check_overlap
712+
---------------
713+
t
714+
(1 row)
715+
659716
DROP EXTENSION pg_pathman;
660717
/* Test that everithing works fine without schemas */
661718
CREATE EXTENSION pg_pathman;

‎contrib/pg_pathman/init.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -342,7 +342,7 @@ load_check_constraints(Oid parent_oid, Snapshot snapshot)
342342
{
343343
Datumcur_upper=PATHMAN_GET_DATUM(ranges[i].max,byVal);
344344
Datumnext_lower=PATHMAN_GET_DATUM(ranges[i+1].min,byVal);
345-
booloverlap=FunctionCall2(qsort_type_cmp_func,next_lower,cur_upper)>0;
345+
booloverlap=FunctionCall2(qsort_type_cmp_func,next_lower,cur_upper)<0;
346346

347347
if (overlap)
348348
// if (ranges[i].max > ranges[i+1].min)

‎contrib/pg_pathman/init.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,14 @@ CREATE OR REPLACE FUNCTION @extschema@.get_max_range_value(
5858
parent_relidOID, dummy ANYELEMENT)
5959
RETURNS ANYELEMENTAS'pg_pathman','get_max_range_value' LANGUAGE C STRICT;
6060

61+
/*
62+
* Checks if range overlaps with existing partitions.
63+
* Returns TRUE if overlaps and FALSE otherwise.
64+
*/
65+
CREATEOR REPLACE FUNCTION @extschema@.check_overlap(
66+
parent_relidOID, range_min ANYELEMENT, range_max ANYELEMENT)
67+
RETURNSBOOLEANAS'pg_pathman','check_overlap' LANGUAGE C STRICT;
68+
6169
/*
6270
* Copy rows to partitions
6371
*/

‎contrib/pg_pathman/pg_pathman.c‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -777,7 +777,7 @@ handle_binary_opexpr(const PartRelationInfo *prel, WrapperNode *result,
777777
}
778778

779779
/* If we still didn't find partition then it doesn't exist */
780-
if (startidx==endidx)
780+
if (startidx>=endidx)
781781
{
782782
result->rangeset=NIL;
783783
return;
@@ -851,7 +851,8 @@ make_hash(const PartRelationInfo *prel, int value)
851851
/*
852852
* Search for range section. Returns position of the item in array.
853853
* If item wasn't found then function returns closest position and sets
854-
* foundPtr to false.
854+
* foundPtr to false. If value is outside the range covered by partitions
855+
* then returns -1.
855856
*/
856857
int
857858
range_binary_search(constRangeRelation*rangerel,FmgrInfo*cmp_func,Datumvalue,bool*foundPtr)
@@ -874,9 +875,9 @@ range_binary_search(const RangeRelation *rangerel, FmgrInfo *cmp_func, Datum val
874875
cmp_min=FunctionCall2(cmp_func,value,PATHMAN_GET_DATUM(ranges[0].min,byVal)),
875876
cmp_max=FunctionCall2(cmp_func,value,PATHMAN_GET_DATUM(ranges[rangerel->ranges.length-1].max,byVal));
876877

877-
if (cmp_min<0||cmp_max>0)
878+
if (cmp_min<0||cmp_max >=0)
878879
{
879-
returni;
880+
return-1;
880881
}
881882

882883
while (true)
@@ -893,7 +894,7 @@ range_binary_search(const RangeRelation *rangerel, FmgrInfo *cmp_func, Datum val
893894
break;
894895
}
895896

896-
if (startidx==endidx)
897+
if (startidx>=endidx)
897898
returni;
898899

899900
if (cmp_min<0)

‎contrib/pg_pathman/pl_funcs.c‎

Lines changed: 50 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ PG_FUNCTION_INFO_V1( get_range_by_idx );
1919
PG_FUNCTION_INFO_V1(get_partition_range );
2020
PG_FUNCTION_INFO_V1(acquire_partitions_lock );
2121
PG_FUNCTION_INFO_V1(release_partitions_lock );
22+
PG_FUNCTION_INFO_V1(check_overlap );
2223
PG_FUNCTION_INFO_V1(get_min_range_value );
2324
PG_FUNCTION_INFO_V1(get_max_range_value );
2425

@@ -118,6 +119,11 @@ find_or_create_range_partition(PG_FUNCTION_ARGS)
118119
*/
119120
if (found)
120121
PG_RETURN_OID(ranges[pos].child_oid);
122+
/*
123+
* If not found and value is between first and last partitions
124+
*/
125+
if (!found&&pos >=0)
126+
PG_RETURN_NULL();
121127
else
122128
{
123129
Oidchild_oid;
@@ -290,8 +296,8 @@ get_max_range_value(PG_FUNCTION_ARGS)
290296
{
291297
intparent_oid=DatumGetInt32(PG_GETARG_DATUM(0));
292298
PartRelationInfo*prel;
293-
RangeRelation*rangerel;
294-
RangeEntry*ranges;
299+
RangeRelation*rangerel;
300+
RangeEntry*ranges;
295301

296302
prel=get_pathman_relation_info(parent_oid,NULL);
297303
rangerel=get_pathman_range_relation(parent_oid,NULL);
@@ -303,6 +309,48 @@ get_max_range_value(PG_FUNCTION_ARGS)
303309
PG_RETURN_DATUM(PATHMAN_GET_DATUM(ranges[rangerel->ranges.length-1].max,rangerel->by_val));
304310
}
305311

312+
/*
313+
* Checks if range overlaps with existing partitions.
314+
* Returns TRUE if overlaps and FALSE otherwise.
315+
*/
316+
Datum
317+
check_overlap(PG_FUNCTION_ARGS)
318+
{
319+
intparent_oid=DatumGetInt32(PG_GETARG_DATUM(0));
320+
Datump1=PG_GETARG_DATUM(1);
321+
Oidp1_type=get_fn_expr_argtype(fcinfo->flinfo,1);
322+
Datump2=PG_GETARG_DATUM(2);
323+
Oidp2_type=get_fn_expr_argtype(fcinfo->flinfo,2);
324+
PartRelationInfo*prel;
325+
RangeRelation*rangerel;
326+
RangeEntry*ranges;
327+
FmgrInfocmp_func_1;
328+
FmgrInfocmp_func_2;
329+
inti;
330+
331+
prel=get_pathman_relation_info(parent_oid,NULL);
332+
rangerel=get_pathman_range_relation(parent_oid,NULL);
333+
334+
if (!prel|| !rangerel||prel->parttype!=PT_RANGE)
335+
PG_RETURN_NULL();
336+
337+
/* comparison functions */
338+
cmp_func_1=*get_cmp_func(p1_type,prel->atttype);
339+
cmp_func_2=*get_cmp_func(p2_type,prel->atttype);
340+
341+
ranges= (RangeEntry*)dsm_array_get_pointer(&rangerel->ranges);
342+
for (i=0;i<rangerel->ranges.length;i++)
343+
{
344+
boolc1=FunctionCall2(&cmp_func_1,p1,ranges[i].max);
345+
boolc2=FunctionCall2(&cmp_func_2,p2,ranges[i].min);
346+
347+
if (c1<0&&c2>0)
348+
PG_RETURN_BOOL(true);
349+
}
350+
351+
PG_RETURN_BOOL(false);
352+
}
353+
306354
/*
307355
* Acquire partitions lock
308356
*/

‎contrib/pg_pathman/range.sql‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -798,7 +798,10 @@ BEGIN
798798

799799
p_relation := @extschema@.validate_relname(p_relation);
800800

801-
/* TODO: check range overlap*/
801+
/* check range overlap*/
802+
IF @extschema@.check_overlap(p_relation::regclass::oid, p_start_value, p_end_value)!= FALSE THEN
803+
RAISE EXCEPTION'Specified range overlaps with existing partitions';
804+
END IF;
802805

803806
IF p_start_value>= p_end_value THEN
804807
RAISE EXCEPTION'Failed to create partition: p_start_value is greater than p_end_value';
@@ -881,6 +884,10 @@ BEGIN
881884

882885
p_relation := @extschema@.validate_relname(p_relation);
883886

887+
IF @extschema@.check_overlap(p_relation::regclass::oid, p_start_value, p_end_value)!= FALSE THEN
888+
RAISE EXCEPTION'Specified range overlaps with existing partitions';
889+
END IF;
890+
884891
/* Set inheritance*/
885892
EXECUTE format('ALTER TABLE %s INHERIT %s'
886893
, p_partition

‎contrib/pg_pathman/sql/pg_pathman.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -114,9 +114,11 @@ SELECT pathman.prepend_range_partition('test.range_rel');
114114
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt BETWEEN'2014-12-15'AND'2015-01-15';
115115
SELECTpathman.drop_range_partition('test.range_rel_7');
116116
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt BETWEEN'2014-12-15'AND'2015-01-15';
117+
SELECTpathman.add_range_partition('test.range_rel','2014-12-01'::DATE,'2015-01-02'::DATE);
117118
SELECTpathman.add_range_partition('test.range_rel','2014-12-01'::DATE,'2015-01-01'::DATE);
118119
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt BETWEEN'2014-12-15'AND'2015-01-15';
119120
CREATETABLEtest.range_rel_archive (LIKEtest.range_rel INCLUDING ALL);
121+
SELECTpathman.attach_range_partition('test.range_rel','test.range_rel_archive','2014-01-01'::DATE,'2015-01-01'::DATE);
120122
SELECTpathman.attach_range_partition('test.range_rel','test.range_rel_archive','2014-01-01'::DATE,'2014-12-01'::DATE);
121123
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt BETWEEN'2014-11-15'AND'2015-01-15';
122124
SELECTpathman.detach_range_partition('test.range_rel_archive');
@@ -156,6 +158,19 @@ SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
156158
DROPTABLEtest.range_rel CASCADE;
157159
SELECT*FROMpathman.pathman_config;
158160

161+
/* Check overlaps*/
162+
CREATETABLEtest.num_range_rel (
163+
idSERIALPRIMARY KEY,
164+
txtTEXT);
165+
SELECTpathman.create_range_partitions('test.num_range_rel','id',1000,1000,4);
166+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,4001,5000);
167+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,4000,5000);
168+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,3999,5000);
169+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,3000,3500);
170+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,0,999);
171+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,0,1000);
172+
SELECTpathman.check_overlap('test.num_range_rel'::regclass::oid,0,1001);
173+
159174
DROP EXTENSION pg_pathman;
160175

161176
/* Test that everithing works fine without schemas*/

0 commit comments

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