Skip to content

Commit c980be9

Browse files
zilderzilder
committed
pathman: overlap check added for some functions; fixed issue with inserting to missing partitions (holes)
1 parent 45b88a5 commit c980be9

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 * FROM hash_rel WHERE value = 1234;
176-
QUERY PLAN
177-
-----------------------------------------------------------------
178-
Append (cost=0.00..2.00 rows=0 width=0)
179-
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
180-
Filter: (value = 1234)
180+
EXPLAIN SELECT * FROM items WHERE id = 1234;
181+
QUERY PLAN
182+
------------------------------------------------------------------------------------
183+
Append (cost=0.28..8.29 rows=0 width=0)
184+
-> Index Scan using items_34_pkey on items_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 ONLY hash_rel;
185-
QUERY PLAN
186-
--------------------------------------------------------
187-
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
189+
EXPLAIN SELECT * FROM ONLY items;
190+
QUERY PLAN
191+
------------------------------------------------------
192+
Seq Scan on items (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, '1 month'::interval, 60);
212+
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::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
Datum cur_upper = PATHMAN_GET_DATUM(ranges[i].max, byVal);
344344
Datum next_lower = PATHMAN_GET_DATUM(ranges[i+1].min, byVal);
345-
bool overlap = FunctionCall2(qsort_type_cmp_func, next_lower, cur_upper) > 0;
345+
bool overlap = 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_relid OID, dummy ANYELEMENT)
5959
RETURNS ANYELEMENT AS '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+
CREATE OR REPLACE FUNCTION @extschema@.check_overlap(
66+
parent_relid OID, range_min ANYELEMENT, range_max ANYELEMENT)
67+
RETURNS BOOLEAN AS '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(const RangeRelation *rangerel, FmgrInfo *cmp_func, Datum value, 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-
return i;
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
return i;
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
Oid child_oid;
@@ -290,8 +296,8 @@ get_max_range_value(PG_FUNCTION_ARGS)
290296
{
291297
int parent_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+
int parent_oid = DatumGetInt32(PG_GETARG_DATUM(0));
320+
Datum p1 = PG_GETARG_DATUM(1);
321+
Oid p1_type = get_fn_expr_argtype(fcinfo->flinfo, 1);
322+
Datum p2 = PG_GETARG_DATUM(2);
323+
Oid p2_type = get_fn_expr_argtype(fcinfo->flinfo, 2);
324+
PartRelationInfo *prel;
325+
RangeRelation *rangerel;
326+
RangeEntry *ranges;
327+
FmgrInfo cmp_func_1;
328+
FmgrInfo cmp_func_2;
329+
int i;
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+
bool c1 = FunctionCall2(&cmp_func_1, p1, ranges[i].max);
345+
bool c2 = 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 * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';
115115
SELECT pathman.drop_range_partition('test.range_rel_7');
116116
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';
117+
SELECT pathman.add_range_partition('test.range_rel', '2014-12-01'::DATE, '2015-01-02'::DATE);
117118
SELECT pathman.add_range_partition('test.range_rel', '2014-12-01'::DATE, '2015-01-01'::DATE);
118119
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';
119120
CREATE TABLE test.range_rel_archive (LIKE test.range_rel INCLUDING ALL);
121+
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_archive', '2014-01-01'::DATE, '2015-01-01'::DATE);
120122
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_archive', '2014-01-01'::DATE, '2014-12-01'::DATE);
121123
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' AND '2015-01-15';
122124
SELECT pathman.detach_range_partition('test.range_rel_archive');
@@ -156,6 +158,19 @@ SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
156158
DROP TABLE test.range_rel CASCADE;
157159
SELECT * FROM pathman.pathman_config;
158160

161+
/* Check overlaps */
162+
CREATE TABLE test.num_range_rel (
163+
id SERIAL PRIMARY KEY,
164+
txt TEXT);
165+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 1000, 1000, 4);
166+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 4001, 5000);
167+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 4000, 5000);
168+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 3999, 5000);
169+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 3000, 3500);
170+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 0, 999);
171+
SELECT pathman.check_overlap('test.num_range_rel'::regclass::oid, 0, 1000);
172+
SELECT pathman.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)