Skip to content

Commit 859d155

Browse files
zilderzilder
committed
pathman: documentation extended, schema related bugs fixed
1 parent 7445663 commit 859d155

File tree

8 files changed

+268
-14
lines changed

8 files changed

+268
-14
lines changed

‎contrib/pg_pathman/README.md

Lines changed: 41 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -153,12 +153,51 @@ To split partition use split_range_partition() function:
153153
```
154154
SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
155155
```
156-
Now let's create new partition. You can use append_partition() or prepend_partition() functions:
156+
Now let's create new partitions. You can use append_partition() or prepend_partition() functions:
157157
```
158158
SELECT append_partition('range_rel');
159-
SELECT append_partition('range_rel');
159+
SELECT prepend_partition('range_rel');
160+
```
161+
### Disable pg_pathman
162+
To disable pg_pathman for some previously partitioned table use disable_pathman() function:
163+
```
164+
SELECT disable_pathman('range_rel');
165+
```
166+
All sections and data will stay available and will be handled by standard PostgreSQL partitioning mechanism.
167+
### Manual partitions management
168+
It is possible to manage partitions manually. After creating or removing child tables it's necessary to invoke function:
169+
```
170+
on_update_partitions(oid),
171+
```
172+
which updates internal structures in memory of `pg_pathman module`. For example, let's create new section for the `range_rel` from above:
173+
```
174+
CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2010-01-01')) INHERITS (range_rel);
175+
SELECT on_update_partitions('range_rel'::regclass::oid);
176+
```
177+
CHECK CONSTRAINT must have the exact format:
178+
* (VARIABLE >= CONST AND VARIABLE < CONST) for RANGE partitioned tables;
179+
* (VARIABLE % CONST = CONST) for HASH partitioned tables.
180+
181+
It is possible to create partition from foreign table as well:
182+
```
183+
CREATE FOREIGN TABLE range_rel_archive (
184+
id INTEGER NOT NULL,
185+
dt TIMESTAMP)
186+
SERVER archive_server;
187+
ALTER TABLE range_rel_archive INHERIT range_rel;
188+
ALTER TABLE range_rel_archive ADD CHECK (dt >= '2000-01-01' AND dt < '2010-01-01');
189+
SELECT on_update_partitions('range_rel'::regclass::oid);
190+
```
191+
Foreign table structure must exactly match the parent table.
192+
193+
In case when parent table is being dropped by DROP TABLE, you should invoke on_remove_partitions() function and delete particular entry from `pathman_config` table:
194+
```
195+
SELECT on_remove_partitions('range_rel'::regclass::oid);
196+
DROP TABLE range_rel CASCADE;
197+
DELETE FROM pathman_config WHERE relname = 'public.range_rel';
160198
```
161199

162200
## Author
163201
Ildar Musin <i.musin@postgrespro.ru> Postgres Professional Ltd., Russia
202+
164203
This module is sponsored by Postgres Professional Ltd., Russia

‎contrib/pg_pathman/README.rus.md

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,3 +161,41 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
161161
```
162162
SELECT append_partition('range_rel')
163163
```
164+
### Деакцивация pathman
165+
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командой disable_pathman():
166+
```
167+
SELECT disable_pathman('range_rel');
168+
```
169+
Все созданные секции и данные останутся по прежнему доступны и будут обрабатываться стандартным планировщиком PostgreSQL.
170+
### Ручное управление секциями
171+
Когда набора функций pg_pathman недостаточно для управления секциями, предусмотрено ручное управление. Можно создавать или удалять дочерние таблицы вручную, но после этого необходимо вызывать функцию:
172+
```
173+
on_update_partitions(oid),
174+
```
175+
которая обновит внутреннее представление структуры секций в памяти pg_pathman. Например, добавим новую секцию к ранее созданной range_rel:
176+
```
177+
CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2010-01-01')) INHERITS (range_rel);
178+
SELECT on_update_partitions('range_rel'::regclass::oid);
179+
```
180+
CHECK CONSTRAINT должен иметь строго определенный формат:
181+
* (VARIABLE >= CONST AND VARIABLE < CONST) для RANGE секционированных таблиц;
182+
* (VARIABLE % CONST = CONST) для HASH секционированных таблиц.
183+
184+
Также можно добавить секцию, расположенную на удаленном сервере:
185+
```
186+
CREATE FOREIGN TABLE range_rel_archive (
187+
id INTEGER NOT NULL,
188+
dt TIMESTAMP)
189+
SERVER archive_server;
190+
ALTER TABLE range_rel_archive INHERIT range_rel;
191+
ALTER TABLE range_rel_archive ADD CHECK (dt >= '2000-01-01' AND dt < '2010-01-01');
192+
SELECT on_update_partitions('range_rel'::regclass::oid);
193+
```
194+
Структура таблицы должна полностью совпадать с родительской.
195+
196+
В случае, если родительская таблица была удалена вручную с использованием инструкции DROP TABLE, необходимо удалить соответствующую строку из таблицы pathman_config и вызывать on_remove_partitions():
197+
```
198+
SELECT on_remove_partitions('range_rel'::regclass::oid);
199+
DROP TABLE range_rel CASCADE;
200+
DELETE FROM pathman_config WHERE relname = 'public.range_rel';
201+
```

‎contrib/pg_pathman/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;

‎contrib/pg_pathman/init.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -379,21 +379,23 @@ validate_hash_constraint(Expr *expr, PartRelationInfo *prel, int *hash)
379379
{
380380
OpExpr *eqexpr;
381381
OpExpr *modexpr;
382+
TypeCacheEntry *tce;
382383

383384
if (!IsA(expr, OpExpr))
384385
return false;
385386
eqexpr = (OpExpr *) expr;
386387

387388
/* Is this an equality operator? */
388-
if (eqexpr->opno != Int4EqualOperator)
389+
tce = lookup_type_cache(prel->atttype, TYPECACHE_EQ_OPR);
390+
if (get_op_opfamily_strategy(eqexpr->opno, tce->btree_opf) != BTEqualStrategyNumber)
389391
return false;
390392

391393
if (!IsA(linitial(eqexpr->args), OpExpr))
392394
return false;
393395

394396
/* Is this a modulus operator? */
395397
modexpr = (OpExpr *) linitial(eqexpr->args);
396-
if (modexpr->opno != 530)
398+
if (modexpr->opno != 530 && modexpr->opno != 439 && modexpr->opno && modexpr->opno != 529)
397399
return false;
398400

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

‎contrib/pg_pathman/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, ', '),

‎contrib/pg_pathman/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 (

‎contrib/pg_pathman/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;

‎contrib/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)