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

Commitfea036c

Browse files
committed
documentation extended, bugs fixed
1 parent2765916 commitfea036c

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-
SELECTappend_partition('range_rel');
162+
SELECTprepend_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_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,','),

‎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 (

‎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;

‎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