Skip to content

Commit bb6883d

Browse files
zilderzilder
committed
pathman: detach function fixed and documentation updated
1 parent 647fda5 commit bb6883d

File tree

5 files changed

+110
-17
lines changed

5 files changed

+110
-17
lines changed

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -533,6 +533,21 @@ 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
*/

‎contrib/pg_pathman/range.sql

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -923,14 +923,33 @@ LANGUAGE plpgsql;
923923
/*
924924
* Detach range partition
925925
*/
926-
CREATE OR REPLACE FUNCTION @extschema@.attach_range_partition(
926+
CREATE OR REPLACE FUNCTION @extschema@.detach_range_partition(
927927
p_partition TEXT)
928928
RETURNS TEXT AS
929929
$$
930+
DECLARE
931+
v_parent TEXT;
930932
BEGIN
931933
/* Prevent concurrent partition management */
932934
PERFORM @extschema@.acquire_partitions_lock();
933935

936+
/* Parent table */
937+
SELECT inhparent::regclass INTO v_parent
938+
FROM pg_inherits WHERE inhrelid = p_partition::regclass::oid;
939+
940+
/* Remove inheritance */
941+
EXECUTE format('ALTER TABLE %s NO INHERIT %s'
942+
, p_partition
943+
, v_parent);
944+
945+
/* Remove check constraint */
946+
EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %s_check'
947+
, p_partition
948+
, @extschema@.get_schema_qualified_name(p_partition::regclass));
949+
950+
/* Invalidate cache */
951+
PERFORM @extschema@.on_update_partitions(v_parent::regclass::oid);
952+
934953
/* Release lock */
935954
PERFORM @extschema@.release_partitions_lock();
936955
RETURN p_partition;

‎contrib/pg_pathman/sql/pg_pathman.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -119,6 +119,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' A
119119
CREATE TABLE test.range_rel_archive (LIKE test.range_rel INCLUDING ALL);
120120
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_archive', '2014-01-01'::DATE, '2014-12-01'::DATE);
121121
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' AND '2015-01-15';
122+
SELECT pathman.detach_range_partition('test.range_rel_archive');
123+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' AND '2015-01-15';
122124

123125
/*
124126
* Clean up

0 commit comments

Comments
 (0)