Skip to content

Commit 01d667e

Browse files
dmpgprodmpgpro
committed
Merge commit '19051118951b0a9979c367563efc1bb14f27167e' into PGPRO9_6_pathman
2 parents a3b92e3 + 1905111 commit 01d667e

18 files changed

+5055
-274
lines changed

‎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: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,18 +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 \
1920
pathman_foreign_keys \
21+
pathman_permissions \
2022
pathman_rowmarks
2123
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
22-
EXTRA_CLEAN = $(EXTENSION)--$(EXTVERSION).sql ./isolation_output
24+
EXTRA_CLEAN = pg_pathman--$(EXTVERSION).sql ./isolation_output
2325

2426
ifdef USE_PGXS
2527
PG_CONFIG = pg_config

‎contrib/pg_pathman/README.md

Lines changed: 37 additions & 17 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,6 +36,22 @@ 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

4157
* Implement LIST partitioning scheme;
@@ -75,15 +91,15 @@ Performs HASH partitioning for `relation` by integer key `attribute`. The `parti
7591
create_range_partitions(relation REGCLASS,
7692
attribute TEXT,
7793
start_value ANYELEMENT,
78-
interval ANYELEMENT,
79-
count INTEGER DEFAULT NULL
94+
p_interval ANYELEMENT,
95+
p_count INTEGER DEFAULT NULL
8096
partition_data BOOLEAN DEFAULT TRUE)
8197

8298
create_range_partitions(relation REGCLASS,
8399
attribute TEXT,
84100
start_value ANYELEMENT,
85-
interval INTERVAL,
86-
count INTEGER DEFAULT NULL,
101+
p_interval INTERVAL,
102+
p_count INTEGER DEFAULT NULL,
87103
partition_data BOOLEAN DEFAULT TRUE)
88104
```
89105
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.
@@ -93,24 +109,26 @@ create_partitions_from_range(relation REGCLASS,
93109
attribute TEXT,
94110
start_value ANYELEMENT,
95111
end_value ANYELEMENT,
96-
interval ANYELEMENT,
112+
p_interval ANYELEMENT,
97113
partition_data BOOLEAN DEFAULT TRUE)
98114

99115
create_partitions_from_range(relation REGCLASS,
100116
attribute TEXT,
101117
start_value ANYELEMENT,
102118
end_value ANYELEMENT,
103-
interval INTERVAL,
119+
p_interval INTERVAL,
104120
partition_data BOOLEAN DEFAULT TRUE)
105121
```
106122
Performs RANGE-partitioning from specified range for `relation` by partitioning key `attribute`. Partition creation callback is invoked for each partition if set beforehand.
107123

108124
### Data migration
109125

110126
```plpgsql
111-
partition_table_concurrently(relation REGCLASS)
127+
partition_table_concurrently(relation REGCLASS,
128+
batch_size INTEGER DEFAULT 1000,
129+
sleep_time FLOAT8 DEFAULT 1.0)
112130
```
113-
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.
114132

115133
```plpgsql
116134
stop_concurrent_part_task(relation REGCLASS)
@@ -130,25 +148,25 @@ Same as above, but for a RANGE-partitioned table.
130148
### Post-creation partition management
131149
```plpgsql
132150
split_range_partition(partition REGCLASS,
133-
value ANYELEMENT,
151+
split_value ANYELEMENT,
134152
partition_name TEXT DEFAULT NULL)
135153
```
136-
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.
137155

138156
```plpgsql
139157
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)
140158
```
141159
Merge two adjacent RANGE partitions. First, data from `partition2` is copied to `partition1`, then `partition2` is removed.
142160

143161
```plpgsql
144-
append_range_partition(p_relation REGCLASS,
162+
append_range_partition(parent REGCLASS,
145163
partition_name TEXT DEFAULT NULL,
146164
tablespace TEXT DEFAULT NULL)
147165
```
148166
Append new RANGE partition with `pathman_config.range_interval` as interval.
149167

150168
```plpgsql
151-
prepend_range_partition(p_relation REGCLASS,
169+
prepend_range_partition(parent REGCLASS,
152170
partition_name TEXT DEFAULT NULL,
153171
tablespace TEXT DEFAULT NULL)
154172
```
@@ -462,6 +480,8 @@ NOTICE: 100 rows copied from part_test_2
462480
(3 rows)
463481
```
464482

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+
465485
### HASH partitioning
466486
Consider an example of HASH partitioning. First create a table with some integer column:
467487
```plpgsql
@@ -587,7 +607,7 @@ EXPLAIN SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
587607
### Disabling `pg_pathman`
588608
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:
589609

590-
- `pg_pathman.enable` --- disable (or enable) `pg_pathman` completely
610+
- `pg_pathman.enable` --- disable (or enable) `pg_pathman` **completely**
591611
- `pg_pathman.enable_runtimeappend` --- toggle `RuntimeAppend` custom node on\off
592612
- `pg_pathman.enable_runtimemergeappend` --- toggle `RuntimeMergeAppend` custom node on\off
593613
- `pg_pathman.enable_partitionfilter` --- toggle `PartitionFilter` custom node on\off
@@ -596,7 +616,7 @@ There are several user-accessible [GUC](https://www.postgresql.org/docs/9.5/stat
596616
- `pg_pathman.override_copy` --- toggle COPY statement hooking on\off
597617

598618
To **permanently** disable `pg_pathman` for some previously partitioned table, use the `disable_pathman_for()` function:
599-
```
619+
```plpgsql
600620
SELECT disable_pathman_for('range_rel');
601621
```
602622
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);

‎contrib/pg_pathman/expected/pathman_permissions.out

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
1+
\set VERBOSITY terse
2+
CREATE EXTENSION pg_pathman;
3+
CREATE SCHEMA permissions;
4+
CREATE ROLE user1 LOGIN;
5+
CREATE ROLE user2 LOGIN;
6+
GRANT USAGE, CREATE ON SCHEMA permissions TO user1;
7+
GRANT USAGE, CREATE ON SCHEMA permissions TO user2;
8+
ALTER DEFAULT PRIVILEGES FOR ROLE user1
9+
IN SCHEMA permissions
10+
GRANT SELECT, INSERT ON TABLES
11+
TO user2;
12+
/* Switch to #1 */
13+
SET ROLE user1;
14+
CREATE TABLE permissions.user1_table(id serial, a int);
15+
INSERT INTO permissions.user1_table SELECT g, g FROM generate_series(1, 20) as g;
16+
/* Should fail */
17+
SET ROLE user2;
18+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
19+
NOTICE: sequence "user1_table_seq" does not exist, skipping
20+
WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"
21+
ERROR: new row violates row-level security policy for table "pathman_config"
22+
/* Should be ok */
23+
SET ROLE user1;
24+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
25+
NOTICE: sequence "user1_table_seq" does not exist, skipping
26+
create_range_partitions
27+
-------------------------
28+
2
29+
(1 row)
30+
31+
/* Should be able to see */
32+
SET ROLE user2;
33+
SELECT * FROM pathman_config;
34+
partrel | attname | parttype | range_interval
35+
-------------------------+---------+----------+----------------
36+
permissions.user1_table | id | 2 | 10
37+
(1 row)
38+
39+
SELECT * FROM pathman_config_params;
40+
partrel | enable_parent | auto | init_callback
41+
-------------------------+---------------+------+---------------
42+
permissions.user1_table | f | t | -
43+
(1 row)
44+
45+
/* Should fail */
46+
SET ROLE user2;
47+
SELECT set_enable_parent('permissions.user1_table', true);
48+
WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"
49+
ERROR: new row violates row-level security policy for table "pathman_config_params"
50+
SELECT set_auto('permissions.user1_table', false);
51+
WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"
52+
ERROR: new row violates row-level security policy for table "pathman_config_params"
53+
/* Should fail */
54+
SET ROLE user2;
55+
DELETE FROM pathman_config
56+
WHERE partrel = 'permissions.user1_table'::regclass;
57+
WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"
58+
/* No rights to insert, should fail */
59+
SET ROLE user2;
60+
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0);
61+
/* Have rights, should be ok (bgw connects as user1) */
62+
SET ROLE user1;
63+
GRANT INSERT ON permissions.user1_table TO user2;
64+
SET ROLE user2;
65+
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0) RETURNING *;
66+
id | a
67+
----+---
68+
35 | 0
69+
(1 row)
70+
71+
SELECT relacl FROM pg_class WHERE oid = 'permissions.user1_table_4'::regclass;
72+
relacl
73+
--------------------------------------
74+
{user1=arwdDxt/user1,user2=ar/user1}
75+
(1 row)
76+
77+
/* Try to drop partition, should fail */
78+
SELECT drop_range_partition('permissions.user1_table_4');
79+
ERROR: must be owner of relation user1_table_4
80+
/* Disable automatic partition creation */
81+
SET ROLE user1;
82+
SELECT set_auto('permissions.user1_table', false);
83+
set_auto
84+
----------
85+
86+
(1 row)
87+
88+
/* Partition creation, should fail */
89+
SET ROLE user2;
90+
INSERT INTO permissions.user1_table (id, a) VALUES (55, 0) RETURNING *;
91+
ERROR: no suitable partition for key '55'
92+
/* Finally drop partitions */
93+
SET ROLE user1;
94+
SELECT drop_partitions('permissions.user1_table');
95+
NOTICE: function permissions.user1_table_upd_trig_func() does not exist, skipping
96+
NOTICE: 10 rows copied from permissions.user1_table_1
97+
NOTICE: 10 rows copied from permissions.user1_table_2
98+
NOTICE: 0 rows copied from permissions.user1_table_3
99+
NOTICE: 2 rows copied from permissions.user1_table_4
100+
drop_partitions
101+
-----------------
102+
4
103+
(1 row)
104+
105+
/* Switch to #2 */
106+
SET ROLE user2;
107+
/* Test ddl event trigger */
108+
CREATE TABLE permissions.user2_table(id serial);
109+
SELECT create_hash_partitions('permissions.user2_table', 'id', 3);
110+
create_hash_partitions
111+
------------------------
112+
3
113+
(1 row)
114+
115+
INSERT INTO permissions.user2_table SELECT generate_series(1, 30);
116+
SELECT drop_partitions('permissions.user2_table');
117+
NOTICE: function permissions.user2_table_upd_trig_func() does not exist, skipping
118+
NOTICE: 9 rows copied from permissions.user2_table_0
119+
NOTICE: 11 rows copied from permissions.user2_table_1
120+
NOTICE: 10 rows copied from permissions.user2_table_2
121+
drop_partitions
122+
-----------------
123+
3
124+
(1 row)
125+
126+
/* Finally reset user */
127+
RESET ROLE;
128+
DROP OWNED BY user1;
129+
DROP OWNED BY user2;
130+
DROP USER user1;
131+
DROP USER user2;
132+
DROP SCHEMA permissions CASCADE;
133+
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/hash.sql

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,8 @@ DECLARE
2727
v_init_callback REGPROCEDURE;
2828

2929
BEGIN
30+
PERFORM @extschema@.validate_relname(parent_relid);
31+
3032
IF partition_data = true THEN
3133
/* Acquire data modification lock */
3234
PERFORM @extschema@.prevent_relation_modification(parent_relid);
@@ -35,7 +37,6 @@ BEGIN
3537
PERFORM @extschema@.lock_partitioned_relation(parent_relid);
3638
END IF;
3739

38-
PERFORM @extschema@.validate_relname(parent_relid);
3940
attribute := lower(attribute);
4041
PERFORM @extschema@.common_relation_checks(parent_relid, attribute);
4142

0 commit comments

Comments
 (0)