Skip to content

Commit 4299f24

Browse files
zilderzilder
committed
pathman: extra constraints check
1 parent bac8bf6 commit 4299f24

File tree

5 files changed

+120
-102
lines changed

5 files changed

+120
-102
lines changed

‎contrib/pathman/README.md

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,8 +13,9 @@ Partitioning refers to splitting one large table into smaller pieces. Each row i
1313
PostgreSQL supports partitioning via table inheritance. Each partition must be created as child table with CHECK CONSTRAINT. For example:
1414

1515
```
16-
CHECK ( id >= 100 AND id < 200 )
17-
CHECK ( id >= 200 AND id < 300 )
16+
CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT);
17+
CREATE TABLE test_1 (CHECK ( id >= 100 AND id < 200 )) INHERITS (test);
18+
CREATE TABLE test_2 (CHECK ( id >= 200 AND id < 300 )) INHERITS (test);
1819
```
1920

2021
Despite the flexibility of this approach it has weakness. If query uses filtering the optimizer forced to perform an exhaustive search and check constraints for each partition to determine partitions from which it should select data. If the number of partitions is large the overhead may be significant.

‎contrib/pathman/README.rus.md

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,8 +13,9 @@
1313
Секционирование в postgres основано на механизме наследования. Каждому наследнику задается условие CHECK CONSTRAINT. Например:
1414

1515
```
16-
CHECK ( id >= 100 AND id < 200 )
17-
CHECK ( id >= 200 AND id < 300 )
16+
CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT);
17+
CREATE TABLE test_1 (CHECK ( id >= 100 AND id < 200 )) INHERITS (test);
18+
CREATE TABLE test_2 (CHECK ( id >= 200 AND id < 300 )) INHERITS (test);
1819
```
1920

2021
Несмотря на гибкость, этот механизм обладает недостатками. Так при фильтрации данных оптимизатор вынужден перебирать все дочерние секции и сравнивать условие запроса с CHECK CONSTRAINT-ами секции, чтобы определить из каких секций ему следует загружать данные. При большом количестве секций это создает дополнительные накладные расходы, которые могут свести на нет выигрыш в производительности от применения секционирования.

‎contrib/pathman/init.c

Lines changed: 42 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,9 @@ get_extension_schema()
6464
return NULL;
6565
}
6666

67+
/*
68+
* Loads partitioned tables structure to hashtable
69+
*/
6770
void
6871
load_relations_hashtable(bool reinitialize)
6972
{
@@ -85,14 +88,14 @@ load_relations_hashtable(bool reinitialize)
8588
SPI_connect();
8689
schema = get_extension_schema();
8790

88-
/* if extension doesn't exist then just quit */
91+
/* If extension isn't exist then just quit */
8992
if (!schema)
9093
{
9194
SPI_finish();
9295
return;
9396
}
9497

95-
/* put schema name to the query */
98+
/* Put schema name to the query */
9699
query = psprintf(sql, schema);
97100
ret = SPI_exec(query, 0);
98101
proc = SPI_processed;
@@ -115,27 +118,21 @@ load_relations_hashtable(bool reinitialize)
115118
prel->atttype = DatumGetObjectId(SPI_getbinval(tuple, tupdesc, 4, &isnull));
116119

117120
part_oids = lappend_int(part_oids, oid);
118-
119-
/* children will be filled in later */
120-
// prinfo->children = NIL;
121121
}
122122
}
123123
pfree(query);
124124

125-
/* load children information */
125+
/* Load children information */
126126
foreach(lc, part_oids)
127127
{
128128
Oid oid = (int) lfirst_int(lc);
129129

130130
prel = (PartRelationInfo*)
131131
hash_search(relations, (const void *)&oid, HASH_FIND, NULL);
132132

133-
// load_check_constraints(oid);
134-
135133
switch(prel->parttype)
136134
{
137135
case PT_RANGE:
138-
// load_range_restrictions(oid);
139136
if (reinitialize && prel->children.length > 0)
140137
{
141138
RangeRelation *rangerel = (RangeRelation *)
@@ -168,7 +165,7 @@ create_relations_hashtable()
168165
ctl.keysize = sizeof(int);
169166
ctl.entrysize = sizeof(PartRelationInfo);
170167

171-
/* already exists, recreate */
168+
/* Already exists, recreate */
172169
if (relations != NULL)
173170
hash_destroy(relations);
174171

@@ -196,7 +193,7 @@ load_check_constraints(Oid parent_oid)
196193
prel = (PartRelationInfo*)
197194
hash_search(relations, (const void *) &parent_oid, HASH_FIND, &found);
198195

199-
/* skip if already loaded */
196+
/* Skip if already loaded */
200197
if (prel->children.length > 0)
201198
return;
202199

@@ -237,8 +234,6 @@ load_check_constraints(Oid parent_oid)
237234
char *conbin;
238235
Expr *expr;
239236

240-
// HeapTuple reltuple;
241-
// Form_pg_class pg_class_tuple;
242237
Form_pg_constraint con;
243238

244239
con = (Form_pg_constraint) GETSTRUCT(tuple);
@@ -255,8 +250,12 @@ load_check_constraints(Oid parent_oid)
255250
{
256251
case PT_RANGE:
257252
if (!validate_range_constraint(expr, prel, &min, &max))
258-
/* TODO: elog() */
253+
{
254+
elog(WARNING, "Range constraint for relation %u MUST have exact format: "
255+
"VARIABLE >= CONST AND VARIABLE < CONST. Skipping...",
256+
(Oid) con->conrelid);
259257
continue;
258+
}
260259

261260
re.child_oid = con->conrelid;
262261
re.min = min;
@@ -267,24 +266,37 @@ load_check_constraints(Oid parent_oid)
267266

268267
case PT_HASH:
269268
if (!validate_hash_constraint(expr, prel, &hash))
270-
/* TODO: elog() */
269+
{
270+
elog(WARNING, "Hash constraint for relation %u MUST have exact format: "
271+
"VARIABLE %% CONST = CONST. Skipping...",
272+
(Oid) con->conrelid);
271273
continue;
274+
}
272275
children[hash] = con->conrelid;
273276
}
274277
}
275278
prel->children_count = proc;
276279

277280
if (prel->parttype == PT_RANGE)
278281
{
279-
/* sort ascending */
282+
/* Sort ascending */
280283
qsort(ranges, proc, sizeof(RangeEntry), cmp_range_entries);
281284

282-
/* copy oids to prel */
285+
/* Copy oids to prel */
283286
for(i=0; i < proc; i++)
284287
children[i] = ranges[i].child_oid;
285288
}
286289

287-
/* TODO: check if some ranges overlap! */
290+
/* Check if some ranges overlap */
291+
for(i=0; i < proc-1; i++)
292+
{
293+
if (ranges[i].max > ranges[i+1].min)
294+
{
295+
elog(WARNING, "Partitions %u and %u overlap. Disabling pathman for relation %u..",
296+
ranges[i].child_oid, ranges[i+1].child_oid, parent_oid);
297+
hash_search(relations, (const void *) &parent_oid, HASH_REMOVE, &found);
298+
}
299+
}
288300
}
289301
}
290302

@@ -303,7 +315,10 @@ cmp_range_entries(const void *p1, const void *p2)
303315
return 0;
304316
}
305317

306-
318+
/*
319+
* Validates range constraint. It MUST have the exact format:
320+
* VARIABLE >= CONST AND VARIABLE < CONST
321+
*/
307322
static bool
308323
validate_range_constraint(Expr *expr, PartRelationInfo *prel, Datum *min, Datum *max)
309324
{
@@ -349,12 +364,15 @@ validate_range_constraint(Expr *expr, PartRelationInfo *prel, Datum *min, Datum
349364
return false;
350365
*max = ((Const*) right)->constvalue;
351366
}
367+
else
368+
return false;
352369

353370
return true;
354371
}
355372

356373
/*
357-
* Validate hash constraint. It should look like "Var % Const = Const"
374+
* Validate hash constraint. It MUST have the exact format
375+
* VARIABLE % CONST = CONST
358376
*/
359377
static bool
360378
validate_hash_constraint(Expr *expr, PartRelationInfo *prel, int *hash)
@@ -366,14 +384,14 @@ validate_hash_constraint(Expr *expr, PartRelationInfo *prel, int *hash)
366384
return false;
367385
eqexpr = (OpExpr *) expr;
368386

369-
/* is this an equality operator? */
387+
/* Is this an equality operator? */
370388
if (eqexpr->opno != Int4EqualOperator)
371389
return false;
372390

373391
if (!IsA(linitial(eqexpr->args), OpExpr))
374392
return false;
375393

376-
/* is this a modulus operator? */
394+
/* Is this a modulus operator? */
377395
modexpr = (OpExpr *) linitial(eqexpr->args);
378396
if (modexpr->opno != 530)
379397
return false;
@@ -424,25 +442,19 @@ void
424442
remove_relation_info(Oid relid)
425443
{
426444
PartRelationInfo *prel;
427-
// HashRelationKey key;
428445
RangeRelation *rangerel;
429446

430447
prel = (PartRelationInfo *)
431448
hash_search(relations, (const void *) &relid, HASH_FIND, 0);
432449

433-
/* if there is nothing to remove then just return */
450+
/* If there is nothing to remove then just return */
434451
if (!prel)
435452
return;
436453

437-
/* remove children relations */
454+
/* Remove children relations */
438455
switch (prel->parttype)
439456
{
440457
case PT_HASH:
441-
// for (i=0; i<prel->children_count; i++)
442-
// {
443-
// key.parent_oid = relid;
444-
// key.hash = i;
445-
// }
446458
free_dsm_array(&prel->children);
447459
break;
448460
case PT_RANGE:

0 commit comments

Comments
 (0)