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

Commit859d155

Browse files
committed
pathman: documentation extended, schema related bugs fixed
1 parent7445663 commit859d155

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 newpartition. You can use append_partition() or prepend_partition() functions:
156+
Now let's create newpartitions. 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_countINTEGER;
174174
attrTEXT;
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 recIN (SELECTchild.relname,pg_constraint.consrc
51-
FROM @extschema@.pathman_config
52-
JOIN pg_classAS parentONparent.relname=@extschema@.pathman_config.relname
52+
FROM @extschema@.pathman_configas cfg
53+
JOIN pg_classAS parentONparent.relfilenode=cfg.relname::regclass::oid
5354
JOIN pg_inheritsON inhparent=parent.relfilenode
5455
JOIN pg_constraintON conrelid= inhrelidAND contype='c'
5556
JOIN pg_classAS childONchild.relfilenode= inhrelid
56-
WHERE@extschema@.pathman_config.relname= p_parent)
57+
WHEREcfg.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*FROMpathman.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+
CREATETABLEhash_rel (
127+
idSERIALPRIMARY KEY,
128+
valueINTEGER);
129+
INSERT INTO hash_rel (value)SELECT gFROM 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_relWHERE id=1234;
133+
134+
/* Range*/
135+
CREATETABLErange_rel (
136+
idSERIALPRIMARY KEY,
137+
dtTIMESTAMP);
138+
INSERT INTO range_rel (dt)SELECT gFROM 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_relWHERE dt<'2010-03-01';
146+
EXPLAIN (COSTS OFF)SELECT*FROM range_relWHERE dt>'2010-12-15';
147+
148+
/* Manual partitions creation*/
149+
CREATETABLErange_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_relWHERE 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_condTEXT;
328329
BEGIN
329330
SELECT attname INTO v_attnameFROM @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)
[8]ページ先頭
©2009-2025 Movatter.jp