Skip to content

Commit 639c3b2

Browse files
vbwagnervbwagner
committed
Merge branch 'pathman_pgpro9_5' into PGPRO9_5
Few bug fixes
2 parents 3c9a086 + 801a2e8 commit 639c3b2

File tree

10 files changed

+276
-22
lines changed

10 files changed

+276
-22
lines changed

‎contrib/pg_pathman/README.md

Lines changed: 41 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -135,7 +135,7 @@ Consider an example of RANGE partitioning. Create a table with numerical or date
135135
CREATE TABLE range_rel (
136136
id SERIAL PRIMARY KEY,
137137
dt TIMESTAMP);
138-
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2015-12-31'::date, '1 day') as g;
138+
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2014-12-31'::date, '1 day') as g;
139139
```
140140
Run create_range_partitions() function to create partitions so that each partition would contain data for one month:
141141
```
@@ -156,9 +156,48 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
156156
Now let's create new partition. 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: 39 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -137,7 +137,7 @@ SELECT partition_data('hash_rel');
137137
CREATE TABLE range_rel (
138138
id SERIAL PRIMARY KEY,
139139
dt TIMESTAMP);
140-
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2015-12-31'::date, '1 day') as g;
140+
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2014-12-31'::date, '1 day') as g;
141141
```
142142
Разобьем таблицу на 60 секций так, чтобы каждая секция содержала данные за один месяц:
143143
```
@@ -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/dsm_array.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
#include "storage/shmem.h"
33
#include "storage/dsm.h"
44
#include "storage/lwlock.h"
5-
5+
#include <stdint.h>
66

77
static Table *table;
88
static dsm_segment *segment = NULL;

‎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/pg_pathman.c

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -552,18 +552,18 @@ change_varnos_in_restrinct_info(RestrictInfo *rinfo, change_varno_context *conte
552552
/* TODO: find some elegant way to do this */
553553
if (bms_is_member(context->old_varno, rinfo->clause_relids))
554554
{
555-
bms_del_member(rinfo->clause_relids, context->old_varno);
556-
bms_add_member(rinfo->clause_relids, context->new_varno);
555+
rinfo->clause_relids = bms_del_member(rinfo->clause_relids, context->old_varno);
556+
rinfo->clause_relids = bms_add_member(rinfo->clause_relids, context->new_varno);
557557
}
558558
if (bms_is_member(context->old_varno, rinfo->left_relids))
559559
{
560-
bms_del_member(rinfo->left_relids, context->old_varno);
561-
bms_add_member(rinfo->left_relids, context->new_varno);
560+
rinfo->left_relids = bms_del_member(rinfo->left_relids, context->old_varno);
561+
rinfo->left_relids = bms_add_member(rinfo->left_relids, context->new_varno);
562562
}
563563
if (bms_is_member(context->old_varno, rinfo->right_relids))
564564
{
565-
bms_del_member(rinfo->right_relids, context->old_varno);
566-
bms_add_member(rinfo->right_relids, context->new_varno);
565+
rinfo->right_relids = bms_del_member(rinfo->right_relids, context->old_varno);
566+
rinfo->right_relids = bms_add_member(rinfo->right_relids, context->new_varno);
567567
}
568568
}
569569

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

0 commit comments

Comments
 (0)