🌐 AI搜索 & 代理 主页
Skip to content

Commit abe9ef9

Browse files
dmpgprodmpgpro
committed
Merge commit 'ad87ba189465db55544905be1faa731e3d8091ae' into PGPRO9_6
2 parents 0916714 + ad87ba1 commit abe9ef9

File tree

14 files changed

+434
-98
lines changed

14 files changed

+434
-98
lines changed

‎contrib/pg_pathman/Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,8 @@ REGRESS = pathman_basic \
1616
pathman_runtime_nodes \
1717
pathman_callbacks \
1818
pathman_domains \
19-
pathman_foreign_keys
19+
pathman_foreign_keys \
20+
pathman_rowmarks
2021
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
2122
EXTRA_CLEAN = $(EXTENSION)--$(EXTVERSION).sql ./isolation_output
2223

‎contrib/pg_pathman/README.md‎

Lines changed: 44 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,6 @@ More interesting features are yet to come. Stay tuned!
3838

3939
## Roadmap
4040

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

@@ -165,9 +164,9 @@ add_range_partition(relation REGCLASS,
165164
Create new RANGE partition for `relation` with specified range bounds.
166165

167166
```plpgsql
168-
drop_range_partition(partition TEXT)
167+
drop_range_partition(partition TEXT, delete_data BOOLEAN DEFAULT TRUE)
169168
```
170-
Drop RANGE partition and all its data.
169+
Drop RANGE partition and all of its data if `delete_data` is true.
171170

172171
```plpgsql
173172
attach_range_partition(relation REGCLASS,
@@ -209,7 +208,24 @@ Enable/disable auto partition propagation (only for RANGE partitioning). It is e
209208
```plpgsql
210209
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
211210
```
212-
Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE).
211+
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:
212+
```json
213+
/* RANGE-partitioned table abc (child abc_4) */
214+
{
215+
"parent": "abc",
216+
"parttype": "2",
217+
"partition": "abc_4",
218+
"range_max": "401",
219+
"range_min": "301"
220+
}
221+
222+
/* HASH-partitioned table abc (child abc_0) */
223+
{
224+
"parent": "abc",
225+
"parttype": "1",
226+
"partition": "abc_0"
227+
}
228+
```
213229

214230
## Views and tables
215231

@@ -221,8 +237,7 @@ CREATE TABLE IF NOT EXISTS pathman_config (
221237
parttype INTEGER NOT NULL,
222238
range_interval TEXT,
223239

224-
CHECK (parttype IN (1, 2)) /* check for allowed part types */
225-
);
240+
CHECK (parttype IN (1, 2)) /* check for allowed part types */ );
226241
```
227242
This table stores a list of partitioned tables.
228243

@@ -232,8 +247,7 @@ CREATE TABLE IF NOT EXISTS pathman_config_params (
232247
partrel REGCLASS NOT NULL PRIMARY KEY,
233248
enable_parent BOOLEAN NOT NULL DEFAULT TRUE,
234249
auto BOOLEAN NOT NULL DEFAULT TRUE,
235-
init_callback REGPROCEDURE NOT NULL DEFAULT 0
236-
);
250+
init_callback REGPROCEDURE NOT NULL DEFAULT 0);
237251
```
238252
This table stores optional parameters which override standard behavior.
239253

@@ -259,7 +273,7 @@ This view lists all currently running concurrent partitioning tasks.
259273
#### `pathman_partition_list` --- list of all existing partitions
260274
```plpgsql
261275
-- helper SRF function
262-
CREATE OR REPLACE FUNCTION @extschema@.show_partition_list()
276+
CREATE OR REPLACE FUNCTION show_partition_list()
263277
RETURNS TABLE (
264278
parent REGCLASS,
265279
partition REGCLASS,
@@ -432,6 +446,22 @@ SELECT * FROM pathman_concurrent_part_tasks;
432446
(1 row)
433447
```
434448

449+
- `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`:
450+
```plpgsql
451+
SELECT drop_range_partition(partition, false) /* move data to parent */
452+
FROM pathman_partition_list
453+
WHERE parent = 'part_test'::regclass AND range_min::int < 500;
454+
NOTICE: 1 rows copied from part_test_11
455+
NOTICE: 100 rows copied from part_test_1
456+
NOTICE: 100 rows copied from part_test_2
457+
drop_range_partition
458+
----------------------
459+
dummy_test_11
460+
dummy_test_1
461+
dummy_test_2
462+
(3 rows)
463+
```
464+
435465
### HASH partitioning
436466
Consider an example of HASH partitioning. First create a table with some integer column:
437467
```plpgsql
@@ -471,7 +501,7 @@ Notice that the `Append` node contains only one child scan which corresponds to
471501
> **Important:** pay attention to the fact that `pg_pathman` excludes the parent table from the query plan.
472502
473503
To access parent table use ONLY modifier:
474-
```
504+
```plpgsql
475505
EXPLAIN SELECT * FROM ONLY items;
476506
QUERY PLAN
477507
------------------------------------------------------
@@ -484,8 +514,7 @@ CREATE TABLE journal (
484514
id SERIAL,
485515
dt TIMESTAMP NOT NULL,
486516
level INTEGER,
487-
msg TEXT
488-
);
517+
msg TEXT);
489518

490519
-- similar index will also be created for each partition
491520
CREATE INDEX ON journal(dt);
@@ -515,8 +544,8 @@ CREATE FOREIGN TABLE journal_archive (
515544
id INTEGER NOT NULL,
516545
dt TIMESTAMP NOT NULL,
517546
level INTEGER,
518-
msg TEXT
519-
) SERVER archive_server;
547+
msg TEXT)
548+
SERVER archive_server;
520549

521550
SELECT attach_range_partition('journal', 'journal_archive', '2014-01-01'::date, '2015-01-01'::date);
522551
```
@@ -536,7 +565,7 @@ SELECT detach_range_partition('journal_archive');
536565
```
537566

538567
Here's an example of the query performing filtering by partitioning key:
539-
```
568+
```plpgsql
540569
SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
541570
id | dt | level | msg
542571
--------+---------------------+-------+----------------------------------

‎contrib/pg_pathman/expected/pathman_rowmarks.out‎

Lines changed: 178 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,178 @@
1+
CREATE EXTENSION pg_pathman;
2+
CREATE SCHEMA rowmarks;
3+
CREATE TABLE rowmarks.first(id int NOT NULL);
4+
CREATE TABLE rowmarks.second(id int NOT NULL);
5+
INSERT INTO rowmarks.first SELECT generate_series(1, 10);
6+
INSERT INTO rowmarks.second SELECT generate_series(1, 10);
7+
SELECT create_hash_partitions('rowmarks.first', 'id', 5);
8+
create_hash_partitions
9+
------------------------
10+
5
11+
(1 row)
12+
13+
/* Not partitioned */
14+
SELECT * FROM rowmarks.second ORDER BY id FOR UPDATE;
15+
id
16+
----
17+
1
18+
2
19+
3
20+
4
21+
5
22+
6
23+
7
24+
8
25+
9
26+
10
27+
(10 rows)
28+
29+
/* Simple case (plan) */
30+
EXPLAIN (COSTS OFF)
31+
SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;
32+
QUERY PLAN
33+
---------------------------------------
34+
LockRows
35+
-> Sort
36+
Sort Key: first_0.id
37+
-> Append
38+
-> Seq Scan on first_0
39+
-> Seq Scan on first_1
40+
-> Seq Scan on first_2
41+
-> Seq Scan on first_3
42+
-> Seq Scan on first_4
43+
(9 rows)
44+
45+
/* Simple case (execution) */
46+
SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;
47+
id
48+
----
49+
1
50+
2
51+
3
52+
4
53+
5
54+
6
55+
7
56+
8
57+
9
58+
10
59+
(10 rows)
60+
61+
SELECT FROM rowmarks.first ORDER BY id FOR UPDATE;
62+
--
63+
(10 rows)
64+
65+
SELECT tableoid > 0 FROM rowmarks.first ORDER BY id FOR UPDATE;
66+
?column?
67+
----------
68+
t
69+
t
70+
t
71+
t
72+
t
73+
t
74+
t
75+
t
76+
t
77+
t
78+
(10 rows)
79+
80+
/* A little harder (plan) */
81+
EXPLAIN (COSTS OFF)
82+
SELECT * FROM rowmarks.first
83+
WHERE id = (SELECT id FROM rowmarks.first
84+
ORDER BY id
85+
OFFSET 10 LIMIT 1
86+
FOR UPDATE)
87+
FOR SHARE;
88+
QUERY PLAN
89+
-----------------------------------------------------
90+
LockRows
91+
InitPlan 1 (returns $1)
92+
-> Limit
93+
-> LockRows
94+
-> Sort
95+
Sort Key: first_0.id
96+
-> Append
97+
-> Seq Scan on first_0
98+
-> Seq Scan on first_1
99+
-> Seq Scan on first_2
100+
-> Seq Scan on first_3
101+
-> Seq Scan on first_4
102+
-> Custom Scan (RuntimeAppend)
103+
-> Seq Scan on first_0 first
104+
Filter: (id = $1)
105+
-> Seq Scan on first_1 first
106+
Filter: (id = $1)
107+
-> Seq Scan on first_2 first
108+
Filter: (id = $1)
109+
-> Seq Scan on first_3 first
110+
Filter: (id = $1)
111+
-> Seq Scan on first_4 first
112+
Filter: (id = $1)
113+
(23 rows)
114+
115+
/* A little harder (execution) */
116+
SELECT * FROM rowmarks.first
117+
WHERE id = (SELECT id FROM rowmarks.first
118+
ORDER BY id
119+
OFFSET 5 LIMIT 1
120+
FOR UPDATE)
121+
FOR SHARE;
122+
id
123+
----
124+
6
125+
(1 row)
126+
127+
/* Two tables (plan) */
128+
EXPLAIN (COSTS OFF)
129+
SELECT * FROM rowmarks.first
130+
WHERE id = (SELECT id FROM rowmarks.second
131+
ORDER BY id
132+
OFFSET 5 LIMIT 1
133+
FOR UPDATE)
134+
FOR SHARE;
135+
QUERY PLAN
136+
----------------------------------------------
137+
LockRows
138+
InitPlan 1 (returns $1)
139+
-> Limit
140+
-> LockRows
141+
-> Sort
142+
Sort Key: second.id
143+
-> Seq Scan on second
144+
-> Custom Scan (RuntimeAppend)
145+
-> Seq Scan on first_0 first
146+
Filter: (id = $1)
147+
-> Seq Scan on first_1 first
148+
Filter: (id = $1)
149+
-> Seq Scan on first_2 first
150+
Filter: (id = $1)
151+
-> Seq Scan on first_3 first
152+
Filter: (id = $1)
153+
-> Seq Scan on first_4 first
154+
Filter: (id = $1)
155+
(18 rows)
156+
157+
/* Two tables (execution) */
158+
SELECT * FROM rowmarks.first
159+
WHERE id = (SELECT id FROM rowmarks.second
160+
ORDER BY id
161+
OFFSET 5 LIMIT 1
162+
FOR UPDATE)
163+
FOR SHARE;
164+
id
165+
----
166+
6
167+
(1 row)
168+
169+
DROP SCHEMA rowmarks CASCADE;
170+
NOTICE: drop cascades to 7 other objects
171+
DETAIL: drop cascades to table rowmarks.first
172+
drop cascades to table rowmarks.second
173+
drop cascades to table rowmarks.first_0
174+
drop cascades to table rowmarks.first_1
175+
drop cascades to table rowmarks.first_2
176+
drop cascades to table rowmarks.first_3
177+
drop cascades to table rowmarks.first_4
178+
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/init.sql‎

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -511,7 +511,7 @@ RETURNS INTEGER AS
511511
$$
512512
DECLARE
513513
v_rec RECORD;
514-
v_rows INTEGER;
514+
v_rows BIGINT;
515515
v_part_count INTEGER := 0;
516516
conf_num_del INTEGER;
517517
v_relkind CHAR;
@@ -539,10 +539,9 @@ BEGIN
539539
ORDER BY inhrelid ASC)
540540
LOOP
541541
IF NOT delete_data THEN
542-
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
543-
INSERT INTO %s SELECT * FROM part_data',
544-
v_rec.tbl::TEXT,
545-
parent_relid::text);
542+
EXECUTE format('INSERT INTO %s SELECT * FROM %s',
543+
parent_relid::TEXT,
544+
v_rec.tbl::TEXT);
546545
GET DIAGNOSTICS v_rows = ROW_COUNT;
547546

548547
/* Show number of copied rows */

0 commit comments

Comments
 (0)