Skip to content

Commit fea036c

Browse files
zilderzilder
committed
documentation extended, bugs fixed
1 parent 2765916 commit fea036c

File tree

8 files changed

+230
-15
lines changed

8 files changed

+230
-15
lines changed

‎README.md

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -138,7 +138,7 @@ Consider an example of RANGE partitioning. Create a table with numerical or date
138138
CREATE TABLE range_rel (
139139
id SERIAL PRIMARY KEY,
140140
dt TIMESTAMP);
141-
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2015-12-31'::date, '1 day') as g;
141+
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2014-12-31'::date, '1 day') as g;
142142
```
143143
Run create_range_partitions() function to create partitions so that each partition would contain data for one month:
144144
```
@@ -159,7 +159,7 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
159159
Now let's create new partition. You can use append_partition() or prepend_partition() functions:
160160
```
161161
SELECT append_partition('range_rel');
162-
SELECT append_partition('range_rel');
162+
SELECT prepend_partition('range_rel');
163163
```
164164

165165
## Author

‎README.rus.md

Lines changed: 39 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -139,7 +139,7 @@ SELECT partition_data('hash_rel');
139139
CREATE TABLE range_rel (
140140
id SERIAL PRIMARY KEY,
141141
dt TIMESTAMP);
142-
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2015-12-31'::date, '1 day') as g;
142+
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2014-12-31'::date, '1 day') as g;
143143
```
144144
Разобьем таблицу на 60 секций так, чтобы каждая секция содержала данные за один месяц:
145145
```
@@ -163,3 +163,41 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
163163
```
164164
SELECT append_partition('range_rel')
165165
```
166+
### Деакцивация pathman
167+
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командой disable_pathman():
168+
```
169+
SELECT disable_pathman('range_rel');
170+
```
171+
Все созданные секции и данные останутся по прежнему доступны и будут обрабатываться стандартным планировщиком PostgreSQL.
172+
### Ручное управление секциями
173+
Когда набора функций pg_pathman недостаточно для управления секциями, предусмотрено ручное управление. Можно создавать или удалять дочерние таблицы вручную, но после этого необходимо вызывать функцию:
174+
```
175+
on_update_partitions(oid),
176+
```
177+
которая обновит внутреннее представление структуры секций в памяти pg_pathman. Например, добавим новую секцию к ранее созданной range_rel:
178+
```
179+
CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2010-01-01')) INHERITS (range_rel);
180+
SELECT on_update_partitions('range_rel'::regclass::oid);
181+
```
182+
CHECK CONSTRAINT должен иметь строго определенный формат:
183+
* (VARIABLE >= CONST AND VARIABLE < CONST) для RANGE секционированных таблиц;
184+
* (VARIABLE % CONST = CONST) для HASH секционированных таблиц.
185+
186+
Также можно добавить секцию, расположенную на удаленном сервере:
187+
```
188+
CREATE FOREIGN TABLE range_rel_archive (
189+
id INTEGER NOT NULL,
190+
dt TIMESTAMP)
191+
SERVER archive_server;
192+
ALTER TABLE range_rel_archive INHERIT range_rel;
193+
ALTER TABLE range_rel_archive ADD CHECK (dt >= '2000-01-01' AND dt < '2010-01-01');
194+
SELECT on_update_partitions('range_rel'::regclass::oid);
195+
```
196+
Структура таблицы должна полностью совпадать с родительской.
197+
198+
В случае, если родительская таблица была удалена вручную с использованием инструкции DROP TABLE, необходимо удалить соответствующую строку из таблицы pathman_config и вызывать on_remove_partitions():
199+
```
200+
SELECT on_remove_partitions('range_rel'::regclass::oid);
201+
DROP TABLE range_rel CASCADE;
202+
DELETE FROM pathman_config WHERE relname = 'public.range_rel';
203+
```

‎expected/pg_pathman.out

Lines changed: 139 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -437,3 +437,142 @@ SELECT * FROM pathman.pathman_config;
437437
(0 rows)
438438

439439
DROP EXTENSION pg_pathman;
440+
/* Test that everithing works fine without schemas */
441+
CREATE EXTENSION pg_pathman;
442+
/* Hash */
443+
CREATE TABLE hash_rel (
444+
id SERIAL PRIMARY KEY,
445+
value INTEGER);
446+
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
447+
SELECT create_hash_partitions('hash_rel', 'value', 3);
448+
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
449+
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
450+
create_hash_partitions
451+
------------------------
452+
453+
(1 row)
454+
455+
SELECT partition_data('hash_rel');
456+
NOTICE: Copying data to hash_rel_0 (condition: ((value % 3) = 0))
457+
NOTICE: Copying data to hash_rel_1 (condition: ((value % 3) = 1))
458+
NOTICE: Copying data to hash_rel_2 (condition: ((value % 3) = 2))
459+
partition_data
460+
----------------
461+
0
462+
(1 row)
463+
464+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
465+
QUERY PLAN
466+
------------------------------------------------------
467+
Append
468+
-> Index Scan using hash_rel_0_pkey on hash_rel_0
469+
Index Cond: (id = 1234)
470+
-> Index Scan using hash_rel_1_pkey on hash_rel_1
471+
Index Cond: (id = 1234)
472+
-> Index Scan using hash_rel_2_pkey on hash_rel_2
473+
Index Cond: (id = 1234)
474+
(7 rows)
475+
476+
/* Range */
477+
CREATE TABLE range_rel (
478+
id SERIAL PRIMARY KEY,
479+
dt TIMESTAMP);
480+
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
481+
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
482+
NOTICE: sequence "range_rel_seq" does not exist, skipping
483+
create_range_partitions
484+
-------------------------
485+
486+
(1 row)
487+
488+
SELECT partition_data('range_rel');
489+
NOTICE: Copying data to range_rel_1 (condition: ((dt >= 'Fri Jan 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Mon Feb 01 00:00:00 2010'::timestamp without time zone)))
490+
NOTICE: Copying data to range_rel_2 (condition: ((dt >= 'Mon Feb 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Mon Mar 01 00:00:00 2010'::timestamp without time zone)))
491+
NOTICE: Copying data to range_rel_3 (condition: ((dt >= 'Mon Mar 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Thu Apr 01 00:00:00 2010'::timestamp without time zone)))
492+
NOTICE: Copying data to range_rel_4 (condition: ((dt >= 'Thu Apr 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Sat May 01 00:00:00 2010'::timestamp without time zone)))
493+
NOTICE: Copying data to range_rel_5 (condition: ((dt >= 'Sat May 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Tue Jun 01 00:00:00 2010'::timestamp without time zone)))
494+
NOTICE: Copying data to range_rel_6 (condition: ((dt >= 'Tue Jun 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Thu Jul 01 00:00:00 2010'::timestamp without time zone)))
495+
NOTICE: Copying data to range_rel_7 (condition: ((dt >= 'Thu Jul 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Sun Aug 01 00:00:00 2010'::timestamp without time zone)))
496+
NOTICE: Copying data to range_rel_8 (condition: ((dt >= 'Sun Aug 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Wed Sep 01 00:00:00 2010'::timestamp without time zone)))
497+
NOTICE: Copying data to range_rel_9 (condition: ((dt >= 'Wed Sep 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Fri Oct 01 00:00:00 2010'::timestamp without time zone)))
498+
NOTICE: Copying data to range_rel_10 (condition: ((dt >= 'Fri Oct 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Mon Nov 01 00:00:00 2010'::timestamp without time zone)))
499+
NOTICE: Copying data to range_rel_11 (condition: ((dt >= 'Mon Nov 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Wed Dec 01 00:00:00 2010'::timestamp without time zone)))
500+
NOTICE: Copying data to range_rel_12 (condition: ((dt >= 'Wed Dec 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Sat Jan 01 00:00:00 2011'::timestamp without time zone)))
501+
NOTICE: Copying data to range_rel_13 (condition: ((dt >= 'Sat Jan 01 00:00:00 2011'::timestamp without time zone) AND (dt < 'Tue Feb 01 00:00:00 2011'::timestamp without time zone)))
502+
partition_data
503+
----------------
504+
0
505+
(1 row)
506+
507+
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');
508+
NOTICE: Altering first partition...
509+
NOTICE: Copying data...
510+
NOTICE: Dropping second partition...
511+
NOTICE: Done!
512+
merge_range_partitions
513+
------------------------
514+
515+
(1 row)
516+
517+
SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
518+
NOTICE: Creating new partition...
519+
NOTICE: Copying data to new partition...
520+
NOTICE: Altering original partition...
521+
NOTICE: Done!
522+
split_range_partition
523+
-------------------------
524+
{01-01-2010,03-01-2010}
525+
(1 row)
526+
527+
SELECT append_partition('range_rel');
528+
NOTICE: Appending new partition...
529+
NOTICE: Done!
530+
append_partition
531+
------------------
532+
533+
(1 row)
534+
535+
SELECT prepend_partition('range_rel');
536+
NOTICE: Prepending new partition...
537+
NOTICE: Done!
538+
prepend_partition
539+
-------------------
540+
541+
(1 row)
542+
543+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
544+
QUERY PLAN
545+
--------------------------------
546+
Append
547+
-> Seq Scan on range_rel_1
548+
-> Seq Scan on range_rel_14
549+
(3 rows)
550+
551+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';
552+
QUERY PLAN
553+
--------------------------------------------------------------------------------
554+
Append
555+
-> Seq Scan on range_rel_12
556+
Filter: (dt > 'Wed Dec 15 00:00:00 2010'::timestamp without time zone)
557+
-> Seq Scan on range_rel_13
558+
(4 rows)
559+
560+
/* Manual partitions creation */
561+
CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2005-01-01')) INHERITS (range_rel);
562+
SELECT on_update_partitions('range_rel'::regclass::oid);
563+
on_update_partitions
564+
----------------------
565+
566+
(1 row)
567+
568+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
569+
QUERY PLAN
570+
-------------------------------------
571+
Append
572+
-> Seq Scan on range_rel_archive
573+
-> Seq Scan on range_rel_16
574+
-> Seq Scan on range_rel_1
575+
-> Seq Scan on range_rel_14
576+
(5 rows)
577+
578+
DROP EXTENSION pg_pathman;

‎init.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -382,21 +382,23 @@ validate_hash_constraint(Expr *expr, PartRelationInfo *prel, int *hash)
382382
{
383383
OpExpr *eqexpr;
384384
OpExpr *modexpr;
385+
TypeCacheEntry *tce;
385386

386387
if (!IsA(expr, OpExpr))
387388
return false;
388389
eqexpr = (OpExpr *) expr;
389390

390391
/* Is this an equality operator? */
391-
if (eqexpr->opno != Int4EqualOperator)
392+
tce = lookup_type_cache(prel->atttype, TYPECACHE_EQ_OPR);
393+
if (get_op_opfamily_strategy(eqexpr->opno, tce->btree_opf) != BTEqualStrategyNumber)
392394
return false;
393395

394396
if (!IsA(linitial(eqexpr->args), OpExpr))
395397
return false;
396398

397399
/* Is this a modulus operator? */
398400
modexpr = (OpExpr *) linitial(eqexpr->args);
399-
if (modexpr->opno != 530)
401+
if (modexpr->opno != 530 && modexpr->opno != 439 && modexpr->opno && modexpr->opno != 529)
400402
return false;
401403

402404
if (list_length(modexpr->args) == 2)

‎sql/hash.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,7 @@ DECLARE
173173
partitions_count INTEGER;
174174
attr TEXT;
175175
BEGIN
176+
relation := @extschema@.validate_relname(relation);
176177
relid := relation::regclass::oid;
177178
SELECT string_agg(attname, ', '),
178179
string_agg('OLD.' || attname, ', '),

‎sql/init.sql

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -47,13 +47,14 @@ $$
4747
DECLARE
4848
rec RECORD;
4949
BEGIN
50+
p_parent := @extschema@.validate_relname(p_parent);
5051
FOR rec IN (SELECT child.relname, pg_constraint.consrc
51-
FROM @extschema@.pathman_config
52-
JOIN pg_class AS parent ON parent.relname = @extschema@.pathman_config.relname
52+
FROM @extschema@.pathman_config as cfg
53+
JOIN pg_class AS parent ON parent.relfilenode = cfg.relname::regclass::oid
5354
JOIN pg_inherits ON inhparent = parent.relfilenode
5455
JOIN pg_constraint ON conrelid = inhrelid AND contype='c'
5556
JOIN pg_class AS child ON child.relfilenode = inhrelid
56-
WHERE @extschema@.pathman_config.relname = p_parent)
57+
WHERE cfg.relname = p_parent)
5758
LOOP
5859
RAISE NOTICE 'Copying data to % (condition: %)', rec.relname, rec.consrc;
5960
EXECUTE format('WITH part_data AS (

‎sql/pg_pathman.sql

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -118,3 +118,36 @@ DROP TABLE test.range_rel CASCADE;
118118
SELECT * FROM pathman.pathman_config;
119119

120120
DROP EXTENSION pg_pathman;
121+
122+
/* Test that everithing works fine without schemas */
123+
CREATE EXTENSION pg_pathman;
124+
125+
/* Hash */
126+
CREATE TABLE hash_rel (
127+
id SERIAL PRIMARY KEY,
128+
value INTEGER);
129+
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
130+
SELECT create_hash_partitions('hash_rel', 'value', 3);
131+
SELECT partition_data('hash_rel');
132+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
133+
134+
/* Range */
135+
CREATE TABLE range_rel (
136+
id SERIAL PRIMARY KEY,
137+
dt TIMESTAMP);
138+
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
139+
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
140+
SELECT partition_data('range_rel');
141+
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');
142+
SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
143+
SELECT append_partition('range_rel');
144+
SELECT prepend_partition('range_rel');
145+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
146+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';
147+
148+
/* Manual partitions creation */
149+
CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2005-01-01')) INHERITS (range_rel);
150+
SELECT on_update_partitions('range_rel'::regclass::oid);
151+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
152+
153+
DROP EXTENSION pg_pathman;

‎sql/range.sql

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -196,7 +196,7 @@ BEGIN
196196

197197
SELECT attname, parttype INTO v_attname, v_part_type
198198
FROM @extschema@.pathman_config
199-
WHERE relname = v_parent_relid::regclass::text;
199+
WHERE relname = @extschema@.get_schema_qualified_name(v_parent_relid::regclass, '.');
200200

201201
/* Check if this is RANGE partition */
202202
IF v_part_type != 2 THEN
@@ -218,9 +218,10 @@ BEGIN
218218

219219
/* Create new partition */
220220
RAISE NOTICE 'Creating new partition...';
221-
v_new_partition := @extschema@.create_single_range_partition(v_parent_relid::regclass::text,
222-
p_value,
223-
p_range[2]);
221+
v_new_partition := @extschema@.create_single_range_partition(
222+
@extschema@.get_schema_qualified_name(v_parent_relid::regclass, '.'),
223+
p_value,
224+
p_range[2]);
224225

225226
/* Copy data */
226227
RAISE NOTICE 'Copying data to new partition...';
@@ -286,14 +287,14 @@ BEGIN
286287

287288
SELECT attname, parttype INTO v_attname, v_part_type
288289
FROM @extschema@.pathman_config
289-
WHERE relname = v_parent_relid1::regclass::text;
290+
WHERE relname = @extschema@.get_schema_qualified_name(v_parent_relid1::regclass, '.');
290291

291292
/* Check if this is RANGE partition */
292293
IF v_part_type != 2 THEN
293294
RAISE EXCEPTION 'Specified partitions aren''t RANGE partitions';
294295
END IF;
295296

296-
v_atttype := @extschema@.get_attribute_type_name(v_parent_relid1::regclass::text, v_attname);
297+
v_atttype := @extschema@.get_attribute_type_name(p_partition1, v_attname);
297298

298299
EXECUTE format('SELECT @extschema@.merge_range_partitions_internal($1, $2 , $3, NULL::%s)', v_atttype)
299300
USING v_parent_relid1, v_part1_relid , v_part2_relid;
@@ -327,7 +328,7 @@ DECLARE
327328
v_cond TEXT;
328329
BEGIN
329330
SELECT attname INTO v_attname FROM @extschema@.pathman_config
330-
WHERE relname = p_parent_relid::regclass::text;
331+
WHERE relname = @extschema@.get_schema_qualified_name(p_parent_relid::regclass, '.');
331332

332333
/*
333334
* Get ranges

0 commit comments

Comments
 (0)