Skip to content

Commit 2f9dbb2

Browse files
zilderzilder
committed
Merge branch 'pathman_pgpro9_5' of gitlab.postgrespro.ru:pgpro-dev/postgrespro into pathman_pgpro9_5
2 parents 0de7bbb + b04aa13 commit 2f9dbb2

File tree

8 files changed

+265
-60
lines changed

8 files changed

+265
-60
lines changed

‎contrib/pg_pathman/README.md

Lines changed: 43 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -37,8 +37,7 @@ Based on partitioning type and operator the `pg_pathman` searches corresponding
3737

3838
To install pg_pathman run in psql:
3939
```
40-
CREATE SCHEMA pathman;
41-
CREATE EXTENSION pg_pathman SCHEMA pathman;
40+
CREATE EXTENSION pg_pathman;
4241
```
4342
Then modify shared_preload_libraries parameter in postgres.conf as following:
4443
```
@@ -121,14 +120,35 @@ CREATE TABLE hash_rel (
121120
value INTEGER);
122121
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
123122
```
124-
Then run create_hash_partitions() function with appropriate arguments:
123+
If partitions are supposed to have indexes, then they should be created for parent table before partitioning. In this case pg_pathman will automaticaly create indexes for partitions. Then run create_hash_partitions() function with appropriate arguments:
125124
```
126125
SELECT create_hash_partitions('hash_rel', 'value', 100);
127126
```
128127
This will create new partitions but data will still be in the parent table. To move data to the corresponding partitions use partition_data() function:
129128
```
130129
SELECT partition_data('hash_rel');
131130
```
131+
Here is an example of the query with filtering by partitioning key and its plan:
132+
```
133+
SELECT * FROM hash_rel WHERE value = 1234;
134+
id | value
135+
------+-------
136+
1234 | 1234
137+
138+
EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;
139+
QUERY PLAN
140+
-----------------------------------------------------------------
141+
Append (cost=0.00..2.00 rows=0 width=0)
142+
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
143+
Filter: (value = 1234)
144+
```
145+
Note that pg_pathman exludes parent table from the query plan. To access parent table use ONLY modifier:
146+
```
147+
EXPLAIN SELECT * FROM ONLY hash_rel;
148+
QUERY PLAN
149+
--------------------------------------------------------
150+
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
151+
```
132152
### RANGE
133153
Consider an example of RANGE partitioning. Create a table with numerical or date or timestamp column:
134154
```
@@ -156,12 +176,29 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
156176
Now let's create new partition. You can use append_partition() or prepend_partition() functions:
157177
```
158178
SELECT append_partition('range_rel');
159-
SELECT prepend_partition('range_rel');
160179
```
180+
Here is an example of the query with filtering by partitioning key and its plan:
181+
```
182+
SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
183+
id | dt
184+
-----+---------------------
185+
851 | 2012-04-30 00:00:00
186+
852 | 2012-05-01 00:00:00
187+
188+
EXPLAIN SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
189+
QUERY PLAN
190+
----------------------------------------------------------------------------
191+
Append (cost=0.00..60.80 rows=0 width=0)
192+
-> Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)
193+
Filter: (dt >= '2012-04-30 00:00:00'::timestamp without time zone)
194+
-> Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)
195+
Filter: (dt <= '2012-05-01 00:00:00'::timestamp without time zone)
196+
```
197+
161198
### Disable pg_pathman
162-
To disable pg_pathman for some previously partitioned table use disable_pathman() function:
199+
To disable pg_pathman for some previously partitioned table use disable_partitioning() function:
163200
```
164-
SELECT disable_pathman('range_rel');
201+
SELECT disable_partitioning('range_rel');
165202
```
166203
All sections and data will stay available and will be handled by standard PostgreSQL partitioning mechanism.
167204
### Manual partitions management

‎contrib/pg_pathman/README.rus.md

Lines changed: 45 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -38,8 +38,7 @@ WHERE id = 150
3838

3939
Для установки pg_pathman выполните в командной строке:
4040
```
41-
CREATE SCHEMA pathman;
42-
CREATE EXTENSION pg_pathman SCHEMA pathman;
41+
CREATE EXTENSION pg_pathman;
4342
4443
```
4544
Затем модифицируйте параметр shared_preload_libraries в конфигурационном файле postgres.conf:
@@ -123,14 +122,36 @@ CREATE TABLE hash_rel (
123122
value INTEGER);
124123
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
125124
```
126-
Разобьем таблицу `hash_rel` на 100 секций по полю `value`:
125+
Если дочерние секции подразумевают наличие индексов, то стоит их создать в родительской таблице до разбиения. Тогда при разбиении pg_pathman автоматически создаст соответствующие индексы в дочерних.таблицах. Разобьем таблицу `hash_rel` на 100 секций по полю `value`:
127126
```
128127
SELECT create_hash_partitions('hash_rel', 'value', 100);
129128
```
130129
Перенесем данные из родительской таблицы в дочерние секции.
131130
```
132131
SELECT partition_data('hash_rel');
133132
```
133+
Пример построения плана для запроса с фильтрацией по ключевому полю:
134+
```
135+
SELECT * FROM hash_rel WHERE value = 1234;
136+
id | value
137+
------+-------
138+
1234 | 1234
139+
140+
EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;
141+
QUERY PLAN
142+
-----------------------------------------------------------------
143+
Append (cost=0.00..2.00 rows=0 width=0)
144+
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
145+
Filter: (value = 1234)
146+
```
147+
Стоит отметить, что pg_pathman исключает из плана запроса родительскую таблицу, и чтобы получить данные из нее, следует использовать модификатор ONLY:
148+
```
149+
EXPLAIN SELECT * FROM ONLY hash_rel;
150+
QUERY PLAN
151+
--------------------------------------------------------
152+
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
153+
```
154+
134155
### RANGE
135156
Пример секционирования таблицы с использованием стратегии RANGE.
136157
```
@@ -159,12 +180,30 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
159180
```
160181
Добавим новую секцию в конец списка секций:
161182
```
162-
SELECT append_partition('range_rel')
183+
SELECT append_partition('range_rel');
184+
```
185+
Пример построения плана для запроса с фильтрацией по ключевому полю:
163186
```
187+
SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
188+
id | dt
189+
-----+---------------------
190+
851 | 2012-04-30 00:00:00
191+
852 | 2012-05-01 00:00:00
192+
193+
EXPLAIN SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
194+
QUERY PLAN
195+
----------------------------------------------------------------------------
196+
Append (cost=0.00..60.80 rows=0 width=0)
197+
-> Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)
198+
Filter: (dt >= '2012-04-30 00:00:00'::timestamp without time zone)
199+
-> Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)
200+
Filter: (dt <= '2012-05-01 00:00:00'::timestamp without time zone)
201+
```
202+
164203
### Деакцивация pathman
165-
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командой disable_pathman():
204+
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командой disable_partitioning():
166205
```
167-
SELECT disable_pathman('range_rel');
206+
SELECT disable_partitioning('range_rel');
168207
```
169208
Все созданные секции и данные останутся по прежнему доступны и будут обрабатываться стандартным планировщиком PostgreSQL.
170209
### Ручное управление секциями

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -208,7 +208,7 @@ load_check_constraints(Oid parent_oid)
208208
{
209209
SPITupleTable *tuptable = SPI_tuptable;
210210
Oid *children;
211-
RangeEntry *ranges;
211+
RangeEntry *ranges = NULL;
212212
Datum min;
213213
Datum max;
214214
int hash;

‎contrib/pg_pathman/pg_pathman.c

Lines changed: 14 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@
1010
#include "optimizer/pathnode.h"
1111
#include "optimizer/planner.h"
1212
#include "optimizer/restrictinfo.h"
13+
#include "optimizer/cost.h"
1314
#include "utils/hsearch.h"
1415
#include "utils/tqual.h"
1516
#include "utils/rel.h"
@@ -105,7 +106,7 @@ _PG_init(void)
105106
{
106107
elog(ERROR, "Pathman module must be initialized in postmaster. "
107108
"Put the following line to configuration file: "
108-
"shared_preload_library = 'pg_pathman'");
109+
"shared_preload_libraries='pg_pathman'");
109110
initialization_needed = false;
110111
}
111112

@@ -645,10 +646,12 @@ handle_binary_opexpr(const PartRelationInfo *prel, WrapperNode *result,
645646
if (rangerel != NULL)
646647
{
647648
RangeEntry *re;
648-
bool found = false,
649-
lossy = false;
650-
int counter = 0,
651-
startidx = 0,
649+
bool lossy = false;
650+
#ifdef USE_ASSERT_CHECKING
651+
bool found = false;
652+
int counter = 0;
653+
#endif
654+
int startidx = 0,
652655
cmp_min,
653656
cmp_max,
654657
endidx = rangerel->ranges.length - 1;
@@ -717,7 +720,9 @@ handle_binary_opexpr(const PartRelationInfo *prel, WrapperNode *result,
717720
lossy = false;
718721
else
719722
lossy = true;
723+
#ifdef USE_ASSERT_CHECKING
720724
found = true;
725+
#endif
721726
break;
722727
}
723728

@@ -807,8 +812,10 @@ range_binary_search(const RangeRelation *rangerel, FmgrInfo *cmp_func, Datum val
807812
cmp_max,
808813
i = 0,
809814
startidx = 0,
810-
endidx = rangerel->ranges.length-1,
811-
counter = 0;
815+
endidx = rangerel->ranges.length-1;
816+
#ifdef USE_ASSERT_CHECKING
817+
int counter = 0;
818+
#endif
812819

813820
*foundPtr = false;
814821

‎contrib/pg_pathman/rangeset.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@ irange_list_union(List *a, List *b)
5252

5353
while (ca || cb)
5454
{
55-
IndexRange next;
55+
IndexRange next = 0;
5656

5757
/* Fetch next range with lesser lower bound */
5858
if (ca && cb)

‎contrib/pg_pathman/sql/init.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -78,10 +78,10 @@ RETURNS VOID AS
7878
$$
7979
BEGIN
8080
DELETE FROM @extschema@.pathman_config WHERE relname = relation;
81-
EXECUTE format('DROP TRIGGER %s_insert_trigger_func ON %1$s', relation);
81+
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE', relation);
8282

8383
/* Notify backend about changes */
84-
PERFORM pg_pathman_on_remove_partitions(relation::regclass::integer);
84+
PERFORM on_remove_partitions(relation::regclass::integer);
8585
END
8686
$$
8787
LANGUAGE plpgsql;

0 commit comments

Comments
 (0)