Skip to content

Commit f1dc4ec

Browse files
author
Alexander Korotkov
committed
Merge remote-tracking branch 'pgpro/PGPROEE9_6-15-64-xid' into PGPROEE9_6-15-64-xid
2 parents a2f01b9 + 1c38453 commit f1dc4ec

38 files changed

+6965
-665
lines changed

‎contrib/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ SUBDIRS = \
3232
pg_buffercache \
3333
pg_freespacemap \
3434
pg_prewarm \
35+
pg_query_state \
3536
pg_standby \
3637
pg_stat_statements \
3738
pg_trgm \

‎contrib/pg_pathman/.gitignore

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,4 +6,6 @@ regression.out
66
*.o
77
*.so
88
*.pyc
9-
pg_pathman--*.sql
9+
*.gcda
10+
*.gcno
11+
pg_pathman--1.1.sql

‎contrib/pg_pathman/Makefile

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,17 +8,20 @@ OBJS = src/init.o src/relation_info.o src/utils.o src/partition_filter.o \
88
src/pg_compat.o $(WIN32RES)
99

1010
EXTENSION = pg_pathman
11-
EXTVERSION = 1.0
12-
DATA_built = $(EXTENSION)--$(EXTVERSION).sql
11+
EXTVERSION = 1.1
12+
DATA_built = pg_pathman--$(EXTVERSION).sql
13+
DATA = pg_pathman--1.0.sql pg_pathman--1.0--1.1.sql
1314
PGFILEDESC = "pg_pathman - partitioning tool"
1415

1516
REGRESS = pathman_basic \
1617
pathman_runtime_nodes \
1718
pathman_callbacks \
1819
pathman_domains \
19-
pathman_foreign_keys
20+
pathman_foreign_keys \
21+
pathman_permissions \
22+
pathman_rowmarks
2023
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
21-
EXTRA_CLEAN = $(EXTENSION)--$(EXTVERSION).sql ./isolation_output
24+
EXTRA_CLEAN = pg_pathman--$(EXTVERSION).sql ./isolation_output
2225

2326
ifdef USE_PGXS
2427
PG_CONFIG = pg_config

‎contrib/pg_pathman/README.md

Lines changed: 81 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -5,12 +5,12 @@
55

66
The `pg_pathman` module provides optimized partitioning mechanism and functions to manage partitions.
77

8-
The extension is compatible with PostgreSQL 9.5 (9.6 support is coming soon).
8+
The extension is compatible with PostgreSQL 9.5+.
99

1010
## Overview
1111
**Partitioning** means splitting one large table into smaller pieces. Each row in such table is moved to a single partition according to the partitioning key. PostgreSQL supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT. For example:
1212

13-
```
13+
```plpgsql
1414
CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT);
1515
CREATE TABLE test_1 (CHECK ( id >= 100 AND id < 200 )) INHERITS (test);
1616
CREATE TABLE test_2 (CHECK ( id >= 200 AND id < 300 )) INHERITS (test);
@@ -25,7 +25,7 @@ VARIABLE OP CONST
2525
```
2626
where `VARIABLE` is a partitioning key, `OP` is a comparison operator (supported operators are =, <, <=, >, >=), `CONST` is a scalar value. For example:
2727

28-
```
28+
```plpgsql
2929
WHERE id = 150
3030
```
3131

@@ -36,9 +36,24 @@ Based on the partitioning type and condition's operator, `pg_pathman` searches f
3636

3737
More interesting features are yet to come. Stay tuned!
3838

39+
## Feature highlights
40+
41+
* HASH and RANGE partitioning schemes;
42+
* Both automatic and manual partition management;
43+
* Support for integer, floating point, date and other types, including domains;
44+
* Effective query planning for partitioned tables (JOINs, subselects etc);
45+
* `RuntimeAppend` & `RuntimeMergeAppend` custom plan nodes to pick partitions at runtime;
46+
* `PartitionFilter`: an efficient drop-in replacement for INSERT triggers;
47+
* Automatic partition creation for new INSERTed data (only for RANGE partitioning);
48+
* Improved `COPY FROM\TO` statement that is able to insert rows directly into partitions;
49+
* UPDATE triggers generation out of the box (will be replaced with custom nodes too);
50+
* User-defined callbacks for partition creation event handling;
51+
* Non-blocking concurrent table partitioning;
52+
* FDW support (foreign partitions);
53+
* Various GUC toggles and configurable settings.
54+
3955
## Roadmap
4056

41-
* Provide a way to create user-defined partition creation\destruction callbacks (issue [#22](https://github.com/postgrespro/pg_pathman/issues/22))
4257
* Implement LIST partitioning scheme;
4358
* Optimize hash join (both tables are partitioned by join key).
4459

@@ -76,15 +91,15 @@ Performs HASH partitioning for `relation` by integer key `attribute`. The `parti
7691
create_range_partitions(relation REGCLASS,
7792
attribute TEXT,
7893
start_value ANYELEMENT,
79-
interval ANYELEMENT,
80-
count INTEGER DEFAULT NULL
94+
p_interval ANYELEMENT,
95+
p_count INTEGER DEFAULT NULL
8196
partition_data BOOLEAN DEFAULT TRUE)
8297

8398
create_range_partitions(relation REGCLASS,
8499
attribute TEXT,
85100
start_value ANYELEMENT,
86-
interval INTERVAL,
87-
count INTEGER DEFAULT NULL,
101+
p_interval INTERVAL,
102+
p_count INTEGER DEFAULT NULL,
88103
partition_data BOOLEAN DEFAULT TRUE)
89104
```
90105
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, `count` is the number of premade partitions (if not set then pathman tries to determine it based on attribute values). Partition creation callback is invoked for each partition if set beforehand.
@@ -94,24 +109,26 @@ create_partitions_from_range(relation REGCLASS,
94109
attribute TEXT,
95110
start_value ANYELEMENT,
96111
end_value ANYELEMENT,
97-
interval ANYELEMENT,
112+
p_interval ANYELEMENT,
98113
partition_data BOOLEAN DEFAULT TRUE)
99114

100115
create_partitions_from_range(relation REGCLASS,
101116
attribute TEXT,
102117
start_value ANYELEMENT,
103118
end_value ANYELEMENT,
104-
interval INTERVAL,
119+
p_interval INTERVAL,
105120
partition_data BOOLEAN DEFAULT TRUE)
106121
```
107122
Performs RANGE-partitioning from specified range for `relation` by partitioning key `attribute`. Partition creation callback is invoked for each partition if set beforehand.
108123

109124
### Data migration
110125

111126
```plpgsql
112-
partition_table_concurrently(relation REGCLASS)
127+
partition_table_concurrently(relation REGCLASS,
128+
batch_size INTEGER DEFAULT 1000,
129+
sleep_time FLOAT8 DEFAULT 1.0)
113130
```
114-
Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with user's activity.
131+
Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with user's activity. If the worker is unable to lock rows of a batch, it sleeps for `sleep_time` seconds before the next attempt and tries again up to 60 times, and quits if it's still unable to lock the batch.
115132

116133
```plpgsql
117134
stop_concurrent_part_task(relation REGCLASS)
@@ -131,25 +148,25 @@ Same as above, but for a RANGE-partitioned table.
131148
### Post-creation partition management
132149
```plpgsql
133150
split_range_partition(partition REGCLASS,
134-
value ANYELEMENT,
151+
split_value ANYELEMENT,
135152
partition_name TEXT DEFAULT NULL)
136153
```
137-
Split RANGE `partition` in two by `value`. Partition creation callback is invoked for a new partition if available.
154+
Split RANGE `partition` in two by `split_value`. Partition creation callback is invoked for a new partition if available.
138155

139156
```plpgsql
140157
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)
141158
```
142159
Merge two adjacent RANGE partitions. First, data from `partition2` is copied to `partition1`, then `partition2` is removed.
143160

144161
```plpgsql
145-
append_range_partition(p_relation REGCLASS,
162+
append_range_partition(parent REGCLASS,
146163
partition_name TEXT DEFAULT NULL,
147164
tablespace TEXT DEFAULT NULL)
148165
```
149166
Append new RANGE partition with `pathman_config.range_interval` as interval.
150167

151168
```plpgsql
152-
prepend_range_partition(p_relation REGCLASS,
169+
prepend_range_partition(parent REGCLASS,
153170
partition_name TEXT DEFAULT NULL,
154171
tablespace TEXT DEFAULT NULL)
155172
```
@@ -165,9 +182,9 @@ add_range_partition(relation REGCLASS,
165182
Create new RANGE partition for `relation` with specified range bounds.
166183

167184
```plpgsql
168-
drop_range_partition(partition TEXT)
185+
drop_range_partition(partition TEXT, delete_data BOOLEAN DEFAULT TRUE)
169186
```
170-
Drop RANGE partition and all its data.
187+
Drop RANGE partition and all of its data if `delete_data` is true.
171188

172189
```plpgsql
173190
attach_range_partition(relation REGCLASS,
@@ -209,7 +226,24 @@ Enable/disable auto partition propagation (only for RANGE partitioning). It is e
209226
```plpgsql
210227
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
211228
```
212-
Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE).
229+
Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE). The callback must have the following signature: `part_init_callback(args JSONB) RETURNS VOID`. Parameter `arg` consists of several fields whose presence depends on partitioning type:
230+
```json
231+
/* RANGE-partitioned table abc (child abc_4) */
232+
{
233+
"parent": "abc",
234+
"parttype": "2",
235+
"partition": "abc_4",
236+
"range_max": "401",
237+
"range_min": "301"
238+
}
239+
240+
/* HASH-partitioned table abc (child abc_0) */
241+
{
242+
"parent": "abc",
243+
"parttype": "1",
244+
"partition": "abc_0"
245+
}
246+
```
213247

214248
## Views and tables
215249

@@ -221,8 +255,7 @@ CREATE TABLE IF NOT EXISTS pathman_config (
221255
parttype INTEGER NOT NULL,
222256
range_interval TEXT,
223257

224-
CHECK (parttype IN (1, 2)) /* check for allowed part types */
225-
);
258+
CHECK (parttype IN (1, 2)) /* check for allowed part types */ );
226259
```
227260
This table stores a list of partitioned tables.
228261

@@ -232,8 +265,7 @@ CREATE TABLE IF NOT EXISTS pathman_config_params (
232265
partrel REGCLASS NOT NULL PRIMARY KEY,
233266
enable_parent BOOLEAN NOT NULL DEFAULT TRUE,
234267
auto BOOLEAN NOT NULL DEFAULT TRUE,
235-
init_callback REGPROCEDURE NOT NULL DEFAULT 0
236-
);
268+
init_callback REGPROCEDURE NOT NULL DEFAULT 0);
237269
```
238270
This table stores optional parameters which override standard behavior.
239271

@@ -259,7 +291,7 @@ This view lists all currently running concurrent partitioning tasks.
259291
#### `pathman_partition_list` --- list of all existing partitions
260292
```plpgsql
261293
-- helper SRF function
262-
CREATE OR REPLACE FUNCTION @extschema@.show_partition_list()
294+
CREATE OR REPLACE FUNCTION show_partition_list()
263295
RETURNS TABLE (
264296
parent REGCLASS,
265297
partition REGCLASS,
@@ -432,6 +464,24 @@ SELECT * FROM pathman_concurrent_part_tasks;
432464
(1 row)
433465
```
434466

467+
- `pathman_partition_list` in conjunction with `drop_range_partition()` can be used to drop RANGE partitions in a more flexible way compared to good old `DROP TABLE`:
468+
```plpgsql
469+
SELECT drop_range_partition(partition, false) /* move data to parent */
470+
FROM pathman_partition_list
471+
WHERE parent = 'part_test'::regclass AND range_min::int < 500;
472+
NOTICE: 1 rows copied from part_test_11
473+
NOTICE: 100 rows copied from part_test_1
474+
NOTICE: 100 rows copied from part_test_2
475+
drop_range_partition
476+
----------------------
477+
dummy_test_11
478+
dummy_test_1
479+
dummy_test_2
480+
(3 rows)
481+
```
482+
483+
- You can turn foreign tables into partitions using the `attach_range_partition()` function. Rows that were meant to be inserted into parent will be redirected to foreign partitions (as usual, PartitionFilter will be involved), though by default it is prohibited to insert rows into partitions provided not by `postgres_fdw`. Only superuser is allowed to set `pg_pathman.insert_into_fdw` GUC variable.
484+
435485
### HASH partitioning
436486
Consider an example of HASH partitioning. First create a table with some integer column:
437487
```plpgsql
@@ -471,7 +521,7 @@ Notice that the `Append` node contains only one child scan which corresponds to
471521
> **Important:** pay attention to the fact that `pg_pathman` excludes the parent table from the query plan.
472522
473523
To access parent table use ONLY modifier:
474-
```
524+
```plpgsql
475525
EXPLAIN SELECT * FROM ONLY items;
476526
QUERY PLAN
477527
------------------------------------------------------
@@ -484,8 +534,7 @@ CREATE TABLE journal (
484534
id SERIAL,
485535
dt TIMESTAMP NOT NULL,
486536
level INTEGER,
487-
msg TEXT
488-
);
537+
msg TEXT);
489538

490539
-- similar index will also be created for each partition
491540
CREATE INDEX ON journal(dt);
@@ -515,8 +564,8 @@ CREATE FOREIGN TABLE journal_archive (
515564
id INTEGER NOT NULL,
516565
dt TIMESTAMP NOT NULL,
517566
level INTEGER,
518-
msg TEXT
519-
) SERVER archive_server;
567+
msg TEXT)
568+
SERVER archive_server;
520569

521570
SELECT attach_range_partition('journal', 'journal_archive', '2014-01-01'::date, '2015-01-01'::date);
522571
```
@@ -536,7 +585,7 @@ SELECT detach_range_partition('journal_archive');
536585
```
537586

538587
Here's an example of the query performing filtering by partitioning key:
539-
```
588+
```plpgsql
540589
SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
541590
id | dt | level | msg
542591
--------+---------------------+-------+----------------------------------
@@ -558,7 +607,7 @@ EXPLAIN SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
558607
### Disabling `pg_pathman`
559608
There are several user-accessible [GUC](https://www.postgresql.org/docs/9.5/static/config-setting.html) variables designed to toggle the whole module or specific custom nodes on and off:
560609

561-
- `pg_pathman.enable` --- disable (or enable) `pg_pathman` completely
610+
- `pg_pathman.enable` --- disable (or enable) `pg_pathman` **completely**
562611
- `pg_pathman.enable_runtimeappend` --- toggle `RuntimeAppend` custom node on\off
563612
- `pg_pathman.enable_runtimemergeappend` --- toggle `RuntimeMergeAppend` custom node on\off
564613
- `pg_pathman.enable_partitionfilter` --- toggle `PartitionFilter` custom node on\off
@@ -567,7 +616,7 @@ There are several user-accessible [GUC](https://www.postgresql.org/docs/9.5/stat
567616
- `pg_pathman.override_copy` --- toggle COPY statement hooking on\off
568617

569618
To **permanently** disable `pg_pathman` for some previously partitioned table, use the `disable_pathman_for()` function:
570-
```
619+
```plpgsql
571620
SELECT disable_pathman_for('range_rel');
572621
```
573622
All sections and data will remain unchanged and will be handled by the standard PostgreSQL inheritance mechanism.

‎contrib/pg_pathman/expected/pathman_basic.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1409,7 +1409,7 @@ CREATE TABLE replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCES messag
14091409
INSERT INTO messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;
14101410
INSERT INTO replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;
14111411
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
1412-
WARNING: foreign key 'replies_message_id_fkey' references relation 'messages'
1412+
WARNING: foreign key "replies_message_id_fkey" references relation "messages"
14131413
ERROR: relation "messages" is referenced from other relations
14141414
ALTER TABLE replies DROP CONSTRAINT replies_message_id_fkey;
14151415
SELECT create_range_partitions('messages', 'id', 1, 100, 2);

0 commit comments

Comments
 (0)