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

Commitf85909e

Browse files
committed
Merge branch 'pathman_pgpro9_5' of gitlab.postgrespro.ru:pgpro-dev/postgrespro into pathman_pgpro9_5
2 parentsdb3e0bc +1e0e9b5 commitf85909e

File tree

5 files changed

+37
-55
lines changed

5 files changed

+37
-55
lines changed

‎contrib/pg_pathman/README.md

Lines changed: 6 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,7 @@ create_hash_partitions(
5454
attribute TEXT,
5555
partitions_count INTEGER)
5656
```
57-
Performs HASH partitioning for`relation` by integer key`attribute`. Creates`partitions_count` partitions and trigger on INSERT.Data doesn'tautomatically copied from parenttableto partitions. Use`partition_data()` function (see below) to migrate data.
57+
Performs HASH partitioning for`relation` by integer key`attribute`. Creates`partitions_count` partitions and trigger on INSERT.All the data will beautomatically copied fromtheparent to partitions.
5858

5959
```
6060
create_range_partitions(
@@ -71,7 +71,7 @@ create_range_partitions(
7171
interval INTERVAL,
7272
premake INTEGER)
7373
```
74-
Performs RANGE partitioning for`relation` by partitioning key`attribute`.`start_value` argument specifies initial value,`interval` sets the range of values in a single partition,`premake` is the number of premade partitions.
74+
Performs RANGE partitioning for`relation` by partitioning key`attribute`.`start_value` argument specifies initial value,`interval` sets the range of values in a single partition,`premake` is the number of premade partitions. All the data will be automatically copied from the parent to partitions.
7575

7676
```
7777
create_partitions_from_range(
@@ -88,19 +88,15 @@ create_partitions_from_range(
8888
end_value ANYELEMENT,
8989
interval INTERVAL)
9090
```
91-
Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`.
91+
Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`. Data will be copied to partitions as well.
9292

9393
###Utilities
9494
```
95-
partition_data(parent text)
96-
```
97-
Copies data from parent table to its partitions.
98-
```
9995
create_hash_update_trigger(parent TEXT)
10096
```
10197
Creates the trigger on UPDATE for HASH partitions. The UPDATE trigger isn't created by default because of overhead. It is useful in cases when key attribute could be changed.
10298
```
103-
create_hash_update_trigger(parent TEXT)
99+
create_range_update_trigger(parent TEXT)
104100
```
105101
Same as above for RANGE sections.
106102

@@ -139,10 +135,7 @@ If partitions are supposed to have indexes, then they should be created for pare
139135
```
140136
SELECT create_hash_partitions('hash_rel', 'value', 100);
141137
```
142-
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:
143-
```
144-
SELECT partition_data('hash_rel');
145-
```
138+
This will create new partitions and move the data from parent to partitions.
146139
Here is an example of the query with filtering by partitioning key and its plan:
147140
```
148141
SELECT * FROM hash_rel WHERE value = 1234;
@@ -176,10 +169,7 @@ Run create_range_partitions() function to create partitions so that each partiti
176169
```
177170
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 60);
178171
```
179-
It will create 60 partitions. Now let's move data from the parent to partitions.
180-
```
181-
SELECT partition_data('range_rel');
182-
```
172+
It will create 60 partitions and move the data from parent to partitions.
183173
To merge to adjacent partitions run merge_range_partitions() function:
184174
```
185175
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');

‎contrib/pg_pathman/README.rus.md

Lines changed: 4 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -56,7 +56,7 @@ create_hash_partitions(
5656
attribute TEXT,
5757
partitions_count INTEGER)
5858
```
59-
Выполняет HASH-секционирование таблицы`relation` по целочисленному полю`attribute`. Создает`partitions_count` дочерних секций, а также триггер на вставку. Данные из родительской таблицыне копируютсяавтоматически в дочерние. Миграцию данных можно выполнить с помощью функции`partition_data()` (см. ниже), либо вручную.
59+
Выполняет HASH-секционирование таблицы`relation` по целочисленному полю`attribute`. Создает`partitions_count` дочерних секций, а также триггер на вставку. Данные из родительской таблицыбудутавтоматическископированыв дочерние.
6060

6161
```
6262
create_range_partitions(
@@ -73,7 +73,7 @@ create_range_partitions(
7373
interval INTERVAL,
7474
premake INTEGER)
7575
```
76-
Выполняет RANGE-секционирование таблицы`relation` по полю`attribute`. Аргумент`start_value` задает начальное значение,`interval` -- диапазон значений внутри одной секции,`premake` -- количество заранее создаваемых секций.
76+
Выполняет RANGE-секционирование таблицы`relation` по полю`attribute`. Аргумент`start_value` задает начальное значение,`interval` -- диапазон значений внутри одной секции,`premake` -- количество заранее создаваемых секций. Данные из родительской таблицы будут автоматически скопированы в дочерние.
7777

7878
```
7979
create_partitions_from_range(
@@ -90,19 +90,15 @@ create_partitions_from_range(
9090
end_value ANYELEMENT,
9191
interval INTERVAL)
9292
```
93-
Выполняет RANGE-секционирование для заданного диапазона таблицы`relation` по полю`attribute`.
93+
Выполняет RANGE-секционирование для заданного диапазона таблицы`relation` по полю`attribute`. Данные также будут скопированы в дочерние секции.
9494

9595
###Утилиты
9696
```
97-
partition_data(parent text)
98-
```
99-
Копирует данные из родительской таблицы`parent` в дочерние секции.
100-
```
10197
create_hash_update_trigger(parent TEXT)
10298
```
10399
Создает триггер на UPDATE для HASH секций. По-умолчанию триггер на обновление данных не создается, т.к. это создает дополнительные накладные расходы. Триггер полезен только в том случае, когда меняется значение ключевого аттрибута.
104100
```
105-
create_hash_update_trigger(parent TEXT)
101+
create_range_update_trigger(parent TEXT)
106102
```
107103
Аналогично предыдущей, но для RANGE секций.
108104

@@ -141,10 +137,6 @@ INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
141137
```
142138
SELECT create_hash_partitions('hash_rel', 'value', 100);
143139
```
144-
Перенесем данные из родительской таблицы в дочерние секции.
145-
```
146-
SELECT partition_data('hash_rel');
147-
```
148140
Пример построения плана для запроса с фильтрацией по ключевому полю:
149141
```
150142
SELECT * FROM hash_rel WHERE value = 1234;
@@ -179,11 +171,6 @@ INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '20
179171
```
180172
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 60);
181173
```
182-
183-
Перенесем данные из родительской таблицы в дочерние секции.
184-
```
185-
SELECT partition_data('range_rel');
186-
```
187174
Объединим секции первые две секции:
188175
```
189176
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 18 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -5,22 +5,39 @@ CREATE SCHEMA test;
55
CREATE TABLE test.hash_rel (
66
id SERIAL PRIMARY KEY,
77
value INTEGER);
8+
INSERT INTO test.hash_rel VALUES (1, 1);
9+
INSERT INTO test.hash_rel VALUES (2, 2);
10+
INSERT INTO test.hash_rel VALUES (3, 3);
811
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
912
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
1013
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
1114
NOTICE: Copying data to partitions...
12-
NOTICE: 0 rows have been copied
1315
create_hash_partitions
1416
------------------------
1517
3
1618
(1 row)
1719

1820
SELECT COUNT(*) FROM test.hash_rel;
1921
count
22+
-------
23+
3
24+
(1 row)
25+
26+
SELECT COUNT(*) FROM ONLY test.hash_rel;
27+
count
2028
-------
2129
0
2230
(1 row)
2331

32+
INSERT INTO test.hash_rel VALUES (4, 4);
33+
INSERT INTO test.hash_rel VALUES (5, 5);
34+
INSERT INTO test.hash_rel VALUES (6, 6);
35+
SELECT COUNT(*) FROM test.hash_rel;
36+
count
37+
-------
38+
6
39+
(1 row)
40+
2441
SELECT COUNT(*) FROM ONLY test.hash_rel;
2542
count
2643
-------
@@ -37,7 +54,6 @@ SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day':
3754
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 4);
3855
NOTICE: sequence "range_rel_seq" does not exist, skipping
3956
NOTICE: Copying data to partitions...
40-
NOTICE: 0 rows have been copied
4157
create_range_partitions
4258
-------------------------
4359
4
@@ -61,7 +77,6 @@ CREATE TABLE test.num_range_rel (
6177
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
6278
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
6379
NOTICE: Copying data to partitions...
64-
NOTICE: 0 rows have been copied
6580
create_range_partitions
6681
-------------------------
6782
4
@@ -93,12 +108,6 @@ SELECT COUNT(*) FROM ONLY test.num_range_rel;
93108
0
94109
(1 row)
95110

96-
INSERT INTO test.hash_rel VALUES (1, 1);
97-
INSERT INTO test.hash_rel VALUES (2, 2);
98-
INSERT INTO test.hash_rel VALUES (3, 3);
99-
INSERT INTO test.hash_rel VALUES (4, 4);
100-
INSERT INTO test.hash_rel VALUES (5, 5);
101-
INSERT INTO test.hash_rel VALUES (6, 6);
102111
VACUUM;
103112
/* update triggers test */
104113
SELECT pathman.create_hash_update_trigger('test.hash_rel');
@@ -486,7 +495,6 @@ CREATE TABLE test.range_rel (
486495
dt TIMESTAMP);
487496
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
488497
NOTICE: Copying data to partitions...
489-
NOTICE: 0 rows have been copied
490498
create_range_partitions
491499
-------------------------
492500
1
@@ -558,7 +566,6 @@ SELECT create_hash_partitions('hash_rel', 'value', 3);
558566
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
559567
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
560568
NOTICE: Copying data to partitions...
561-
NOTICE: 0 rows have been copied
562569
create_hash_partitions
563570
------------------------
564571
3
@@ -584,7 +591,6 @@ INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '20
584591
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
585592
NOTICE: sequence "range_rel_seq" does not exist, skipping
586593
NOTICE: Copying data to partitions...
587-
NOTICE: 0 rows have been copied
588594
create_range_partitions
589595
-------------------------
590596
12
@@ -669,7 +675,6 @@ SELECT drop_range_partitions('range_rel');
669675

670676
SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
671677
NOTICE: Copying data to partitions...
672-
NOTICE: 0 rows have been copied
673678
create_partitions_from_range
674679
------------------------------
675680
10
@@ -683,7 +688,6 @@ SELECT drop_range_partitions('range_rel');
683688

684689
SELECT create_partitions_from_range('range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);
685690
NOTICE: Copying data to partitions...
686-
NOTICE: 0 rows have been copied
687691
create_partitions_from_range
688692
------------------------------
689693
12

‎contrib/pg_pathman/init.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -64,7 +64,7 @@ BEGIN
6464
, p_parent
6565
, p_parent);
6666
GET DIAGNOSTICS p_total= ROW_COUNT;
67-
RAISE NOTICE'% rows have been copied', p_total;
67+
--RAISE NOTICE '% rows have been copied', p_total;
6868
RETURN;
6969

7070
EXCEPTION WHEN others THEN

‎contrib/pg_pathman/sql/pg_pathman.sql

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -7,9 +7,17 @@ CREATE SCHEMA test;
77
CREATETABLEtest.hash_rel (
88
idSERIALPRIMARY KEY,
99
valueINTEGER);
10+
INSERT INTOtest.hash_relVALUES (1,1);
11+
INSERT INTOtest.hash_relVALUES (2,2);
12+
INSERT INTOtest.hash_relVALUES (3,3);
1013
SELECTpathman.create_hash_partitions('test.hash_rel','value',3);
1114
SELECTCOUNT(*)FROMtest.hash_rel;
1215
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
16+
INSERT INTOtest.hash_relVALUES (4,4);
17+
INSERT INTOtest.hash_relVALUES (5,5);
18+
INSERT INTOtest.hash_relVALUES (6,6);
19+
SELECTCOUNT(*)FROMtest.hash_rel;
20+
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
1321

1422
CREATETABLEtest.range_rel (
1523
idSERIALPRIMARY KEY,
@@ -33,13 +41,6 @@ INSERT INTO test.num_range_rel
3341
SELECTCOUNT(*)FROMtest.num_range_rel;
3442
SELECTCOUNT(*)FROM ONLYtest.num_range_rel;
3543

36-
INSERT INTOtest.hash_relVALUES (1,1);
37-
INSERT INTOtest.hash_relVALUES (2,2);
38-
INSERT INTOtest.hash_relVALUES (3,3);
39-
INSERT INTOtest.hash_relVALUES (4,4);
40-
INSERT INTOtest.hash_relVALUES (5,5);
41-
INSERT INTOtest.hash_relVALUES (6,6);
42-
4344
VACUUM;
4445

4546
/* update triggers test*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp