Skip to content

Commit 35b741a

Browse files
zilderzilder
committed
Merge branch 'pathman_pgpro9_5' of gitlab.postgrespro.ru:pgpro-dev/postgrespro into pathman_pgpro9_5
2 parents c47d28e + 66f10cd commit 35b741a

File tree

10 files changed

+286
-41
lines changed

10 files changed

+286
-41
lines changed

‎contrib/pg_pathman/Makefile

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,10 @@ $(EXTENSION)--$(EXTVERSION).sql: init.sql hash.sql range.sql
2828

2929
ISOLATIONCHECKS=insert_trigger
3030

31-
isolationcheck:
31+
submake-isolation:
32+
$(MAKE) -C $(top_builddir)/src/test/isolation all
33+
34+
isolationcheck: | submake-isolation
3235
$(MKDIR_P) isolation_output
3336
$(pg_isolation_regress_check) \
3437
--temp-config=$(top_srcdir)/$(subdir)/conf.add \

‎contrib/pg_pathman/README.md

Lines changed: 38 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -62,16 +62,16 @@ create_range_partitions(
6262
attribute TEXT,
6363
start_value ANYELEMENT,
6464
interval ANYELEMENT,
65-
premake INTEGER)
65+
premake INTEGER DEFAULT NULL)
6666
6767
create_range_partitions(
6868
relation TEXT,
6969
attribute TEXT,
7070
start_value ANYELEMENT,
7171
interval INTERVAL,
72-
premake INTEGER)
72+
premake INTEGER DEFAULT NULL)
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. All the data will be automatically copied from the parent to 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 (if not set then pathman tries to determine it based on attribute values). All the data will be automatically copied from the parent to partitions.
7575

7676
```
7777
create_partitions_from_range(
@@ -90,15 +90,15 @@ create_partitions_from_range(
9090
```
9191
Performs RANGE-partitioning from specified range for `relation` by partitioning key `attribute`. Data will be copied to partitions as well.
9292

93-
### Utilities
93+
### Triggers
9494
```
9595
create_hash_update_trigger(parent TEXT)
9696
```
9797
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.
9898
```
9999
create_range_update_trigger(parent TEXT)
100100
```
101-
Same as above for RANGE sections.
101+
Same as above for RANGE partitioned table.
102102

103103
### Partitions management
104104
```
@@ -110,13 +110,42 @@ merge_range_partitions(partition1 TEXT, partition2 TEXT)
110110
```
111111
Merge two adjacent RANGE partitions. Data from `partition2` is copied to `partition1`. Then the `partition2` is removed.
112112
```
113-
append_partition(p_relation TEXT)
113+
append_range_partition(p_relation TEXT)
114114
```
115-
Appends new partition with the range equal to the range of the previous partition.
115+
Appends new RANGE partition and returns
116116
```
117-
prepend_partition(p_relation TEXT)
117+
prepend_range_partition(p_relation TEXT)
118118
```
119-
Prepends new partition with the range equal to the range of the first partition.
119+
Prepends new RANGE partition.
120+
121+
```
122+
add_range_partition(
123+
relation TEXT,
124+
start_value ANYELEMENT,
125+
end_value ANYELEMENT)
126+
```
127+
Creates new RANGE partition for `relation` with specified values range.
128+
129+
```
130+
drop_range_partition(partition TEXT)
131+
```
132+
Drops RANGE partition and all its data.
133+
134+
```
135+
attach_range_partition(
136+
relation TEXT,
137+
partition TEXT,
138+
start_value ANYELEMENT,
139+
end_value ANYELEMENT)
140+
```
141+
Attaches partition to existing RANGE partitioned relation. The table being attached must have exact same structure as the parent one.
142+
143+
```
144+
detach_range_partition(partition TEXT)
145+
```
146+
Detaches partition from existing RANGE partitioned relation.
147+
148+
120149
```
121150
disable_partitioning(relation TEXT)
122151
```

‎contrib/pg_pathman/README.rus.md

Lines changed: 35 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -64,16 +64,16 @@ create_range_partitions(
6464
attribute TEXT,
6565
start_value ANYELEMENT,
6666
interval ANYELEMENT,
67-
premake INTEGER)
67+
premake INTEGER DEFAULT NULL)
6868
6969
create_range_partitions(
7070
relation TEXT,
7171
attribute TEXT,
7272
start_value ANYELEMENT,
7373
interval INTERVAL,
74-
premake INTEGER)
74+
premake INTEGER DEFAULT NULL)
7575
```
76-
Выполняет RANGE-секционирование таблицы `relation` по полю `attribute`. Аргумент `start_value` задает начальное значение, `interval` -- диапазон значений внутри одной секции, `premake` -- количество заранее создаваемых секций. Данные из родительской таблицы будут автоматически скопированы в дочерние.
76+
Выполняет RANGE-секционирование таблицы `relation` по полю `attribute`. Аргумент `start_value` задает начальное значение, `interval` -- диапазон значений внутри одной секции, `premake` -- количество заранее создаваемых секций (если не задано, то pathman попытается определить количество секций на основе значений аттрибута). Данные из родительской таблицы будут автоматически скопированы в дочерние.
7777

7878
```
7979
create_partitions_from_range(
@@ -112,13 +112,41 @@ merge_range_partitions(partition1 TEXT, partition2 TEXT)
112112
```
113113
Объединяет две смежные RANGE секции. Данные из `partition2` копируются в `partition1`, после чего секция `partition2` удаляется.
114114
```
115-
append_partition(p_relation TEXT)
115+
append_range_partition(p_relation TEXT)
116116
```
117-
Добавляет новую секцию в конец списка секций. Диапазон значений устанавливается равным последней секции.
117+
Добавляет новую RANGE секцию в конец списка секций.
118118
```
119-
prepend_partition(p_relation TEXT)
119+
prepend_range_partition(p_relation TEXT)
120120
```
121-
Добавляет новую секцию в начало списка секций.
121+
Добавляет новую RANGE секцию в начало списка секций.
122+
123+
```
124+
add_range_partition(
125+
relation TEXT,
126+
start_value ANYELEMENT,
127+
end_value ANYELEMENT)
128+
```
129+
Добавляет новую RANGE секцию с заданным диапазоном к секционированной таблице `relation`.
130+
131+
```
132+
drop_range_partition(partition TEXT)
133+
```
134+
Удаляет RANGE секцию вместе с содержащимися в ней данными.
135+
136+
```
137+
attach_range_partition(
138+
relation TEXT,
139+
partition TEXT,
140+
start_value ANYELEMENT,
141+
end_value ANYELEMENT)
142+
```
143+
Присоединяет существующую таблицу `partition` в качестве секции к ранее секционированной таблице `relation`. Структура присоединяемой таблицы должна в точности повторять структуру родительской.
144+
145+
```
146+
detach_range_partition(partition TEXT)
147+
```
148+
Отсоединяет секцию `partition`, после чего она становится независимой таблицей.
149+
122150
```
123151
disable_partitioning(relation TEXT)
124152
```

‎contrib/pg_pathman/expected/insert_trigger.out

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: s1b s1_insert_150 s1r s1_show_partitions s2b s2_insert_150 s2c s2_show_partitions
4+
create_range_partitions
5+
6+
1
7+
step s1b: BEGIN;
8+
step s1_insert_150: INSERT INTO range_rel SELECT generate_series(1, 150);
9+
step s1r: ROLLBACK;
10+
step s1_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
11+
inhrelid inhparent inhseqno
12+
13+
16453 16445 1
14+
16462 16445 1
15+
step s2b: BEGIN;
16+
step s2_insert_150: INSERT INTO range_rel SELECT generate_series(1, 150);
17+
step s2c: COMMIT;
18+
step s2_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
19+
inhrelid inhparent inhseqno
20+
21+
16453 16445 1
22+
16462 16445 1
23+
24+
starting permutation: s1b s1_insert_150 s1r s1_show_partitions s2b s2_insert_300 s2c s2_show_partitions
25+
create_range_partitions
26+
27+
1
28+
step s1b: BEGIN;
29+
step s1_insert_150: INSERT INTO range_rel SELECT generate_series(1, 150);
30+
step s1r: ROLLBACK;
31+
step s1_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
32+
inhrelid inhparent inhseqno
33+
34+
16537 16529 1
35+
16545 16529 1
36+
step s2b: BEGIN;
37+
step s2_insert_300: INSERT INTO range_rel SELECT generate_series(151, 300);
38+
step s2c: COMMIT;
39+
step s2_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
40+
inhrelid inhparent inhseqno
41+
42+
16537 16529 1
43+
16545 16529 1
44+
16552 16529 1
45+
46+
starting permutation: s1b s1_insert_300 s1r s1_show_partitions s2b s2_insert_150 s2c s2_show_partitions
47+
create_range_partitions
48+
49+
1
50+
step s1b: BEGIN;
51+
step s1_insert_300: INSERT INTO range_rel SELECT generate_series(151, 300);
52+
step s1r: ROLLBACK;
53+
step s1_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
54+
inhrelid inhparent inhseqno
55+
56+
16627 16619 1
57+
16635 16619 1
58+
16642 16619 1
59+
step s2b: BEGIN;
60+
step s2_insert_150: INSERT INTO range_rel SELECT generate_series(1, 150);
61+
step s2c: COMMIT;
62+
step s2_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
63+
inhrelid inhparent inhseqno
64+
65+
16627 16619 1
66+
16635 16619 1
67+
16642 16619 1
68+
69+
starting permutation: s1b s1_insert_150 s2b s2_insert_300 s1r s2r s2_show_partitions
70+
create_range_partitions
71+
72+
1
73+
step s1b: BEGIN;
74+
step s1_insert_150: INSERT INTO range_rel SELECT generate_series(1, 150);
75+
step s2b: BEGIN;
76+
step s2_insert_300: INSERT INTO range_rel SELECT generate_series(151, 300);
77+
step s1r: ROLLBACK;
78+
step s2r: ROLLBACK;
79+
step s2_show_partitions: SELECT * FROM pg_inherits WHERE inhparent = 'range_rel'::regclass::oid;
80+
inhrelid inhparent inhseqno
81+
82+
16717 16709 1
83+
16725 16709 1
84+
16732 16709 1

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 48 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -533,15 +533,62 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' A
533533
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
534534
(6 rows)
535535

536+
SELECT pathman.detach_range_partition('test.range_rel_archive');
537+
detach_range_partition
538+
------------------------
539+
test.range_rel_archive
540+
(1 row)
541+
542+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' AND '2015-01-15';
543+
QUERY PLAN
544+
-------------------------------------------------------------------------------------
545+
Append
546+
-> Seq Scan on range_rel_8
547+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
548+
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
549+
(4 rows)
550+
536551
/*
537552
* Clean up
538553
*/
539554
SELECT pathman.drop_hash_partitions('test.hash_rel');
540555
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
541556
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
557+
NOTICE: 2 rows copied from test.hash_rel_2
558+
NOTICE: 3 rows copied from test.hash_rel_1
559+
NOTICE: 1 rows copied from test.hash_rel_0
542560
drop_hash_partitions
543561
----------------------
544-
562+
3
563+
(1 row)
564+
565+
SELECT COUNT(*) FROM ONLY test.hash_rel;
566+
count
567+
-------
568+
6
569+
(1 row)
570+
571+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
572+
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
573+
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
574+
NOTICE: Copying data to partitions...
575+
create_hash_partitions
576+
------------------------
577+
3
578+
(1 row)
579+
580+
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
581+
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
582+
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
583+
drop_hash_partitions
584+
----------------------
585+
3
586+
(1 row)
587+
588+
SELECT COUNT(*) FROM ONLY test.hash_rel;
589+
count
590+
-------
591+
0
545592
(1 row)
546593

547594
DROP TABLE test.hash_rel CASCADE;

‎contrib/pg_pathman/hash.sql

Lines changed: 21 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -111,28 +111,41 @@ $$ LANGUAGE plpgsql;
111111
/*
112112
* Drops all partitions for specified relation
113113
*/
114-
CREATE OR REPLACE FUNCTION @extschema@.drop_hash_partitions(IN relation TEXT)
115-
RETURNS VOID AS
114+
CREATE OR REPLACE FUNCTION @extschema@.drop_hash_partitions(
115+
IN relation TEXT
116+
, delete_data BOOLEAN DEFAULT FALSE)
117+
RETURNS INTEGER AS
116118
$$
117119
DECLARE
118-
relid INTEGER;
119-
partitions_count INTEGER;
120-
rec RECORD;
121-
num INTEGER := 0;
120+
v_rec RECORD;
121+
v_rows INTEGER;
122+
v_part_count INTEGER := 0;
122123
BEGIN
123124
relation := @extschema@.validate_relname(relation);
124125

125126
/* Drop trigger first */
126127
PERFORM @extschema@.drop_hash_triggers(relation);
127128
DELETE FROM @extschema@.pathman_config WHERE relname = relation;
128129

129-
FOR rec in (SELECT * FROM pg_inherits WHERE inhparent = relation::regclass::oid)
130+
FOR v_rec in (SELECT inhrelid::regclass::text AS tbl
131+
FROM pg_inherits WHERE inhparent = relation::regclass::oid)
130132
LOOP
131-
EXECUTE format('DROP TABLE %s', rec.inhrelid::regclass::text);
133+
IF NOT delete_data THEN
134+
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
135+
INSERT INTO %s SELECT * FROM part_data'
136+
, v_rec.tbl
137+
, relation);
138+
GET DIAGNOSTICS v_rows = ROW_COUNT;
139+
RAISE NOTICE '% rows copied from %', v_rows, v_rec.tbl;
140+
END IF;
141+
EXECUTE format('DROP TABLE %s', v_rec.tbl);
142+
v_part_count := v_part_count + 1;
132143
END LOOP;
133144

134145
/* Notify backend about changes */
135146
PERFORM @extschema@.on_remove_partitions(relation::regclass::oid);
147+
148+
RETURN v_part_count;
136149
END
137150
$$ LANGUAGE plpgsql;
138151

‎contrib/pg_pathman/pl_funcs.c

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -99,17 +99,17 @@ find_or_create_range_partition(PG_FUNCTION_ARGS)
9999
TYPECACHE_CMP_PROC | TYPECACHE_CMP_PROC_FINFO);
100100

101101
prel = get_pathman_relation_info(relid, NULL);
102+
rangerel = get_pathman_range_relation(relid, NULL);
103+
104+
if (!prel || !rangerel || rangerel->ranges.length == 0)
105+
PG_RETURN_NULL();
106+
102107
cmp_proc_oid = get_opfamily_proc(tce->btree_opf,
103108
value_type,
104109
prel->atttype,
105110
BTORDER_PROC);
106111
fmgr_info(cmp_proc_oid, &cmp_func);
107112

108-
rangerel = get_pathman_range_relation(relid, NULL);
109-
110-
if (!rangerel || rangerel->ranges.length == 0)
111-
PG_RETURN_NULL();
112-
113113
ranges = dsm_array_get_pointer(&rangerel->ranges);
114114
pos = range_binary_search(rangerel, &cmp_func, value, &found);
115115

0 commit comments

Comments
 (0)