Skip to content

Commit 38acbd0

Browse files
zilderzilder
committed
concurrent partitioning integration
1 parent f5ba660 commit 38acbd0

File tree

11 files changed

+236
-82
lines changed

11 files changed

+236
-82
lines changed

‎expected/pg_pathman.out

Lines changed: 85 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,84 @@ INSERT INTO test.hash_rel VALUES (3, 3);
1111
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1212
ERROR: Partitioning key 'value' must be NOT NULL
1313
ALTER TABLE test.hash_rel ALTER COLUMN value SET NOT NULL;
14+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3, partition_data:=false);
15+
create_hash_partitions
16+
------------------------
17+
3
18+
(1 row)
19+
20+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
21+
QUERY PLAN
22+
------------------------------
23+
Append
24+
-> Seq Scan on hash_rel
25+
-> Seq Scan on hash_rel_0
26+
-> Seq Scan on hash_rel_1
27+
-> Seq Scan on hash_rel_2
28+
(5 rows)
29+
30+
SELECT * FROM test.hash_rel;
31+
id | value
32+
----+-------
33+
1 | 1
34+
2 | 2
35+
3 | 3
36+
(3 rows)
37+
38+
SELECT pathman.disable_parent('test.hash_rel');
39+
disable_parent
40+
----------------
41+
42+
(1 row)
43+
44+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
45+
QUERY PLAN
46+
------------------------------
47+
Append
48+
-> Seq Scan on hash_rel_0
49+
-> Seq Scan on hash_rel_1
50+
-> Seq Scan on hash_rel_2
51+
(4 rows)
52+
53+
SELECT * FROM test.hash_rel;
54+
id | value
55+
----+-------
56+
(0 rows)
57+
58+
SELECT pathman.enable_parent('test.hash_rel');
59+
enable_parent
60+
---------------
61+
62+
(1 row)
63+
64+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
65+
QUERY PLAN
66+
------------------------------
67+
Append
68+
-> Seq Scan on hash_rel
69+
-> Seq Scan on hash_rel_0
70+
-> Seq Scan on hash_rel_1
71+
-> Seq Scan on hash_rel_2
72+
(5 rows)
73+
74+
SELECT * FROM test.hash_rel;
75+
id | value
76+
----+-------
77+
1 | 1
78+
2 | 2
79+
3 | 3
80+
(3 rows)
81+
82+
SELECT pathman.drop_partitions('test.hash_rel');
83+
NOTICE: function test.hash_rel_upd_trig_func() does not exist, skipping
84+
NOTICE: 0 rows copied from test.hash_rel_0
85+
NOTICE: 0 rows copied from test.hash_rel_1
86+
NOTICE: 0 rows copied from test.hash_rel_2
87+
drop_partitions
88+
-----------------
89+
3
90+
(1 row)
91+
1492
SELECT pathman.create_hash_partitions('test.hash_rel', 'Value', 3);
1593
create_hash_partitions
1694
------------------------
@@ -1140,9 +1218,9 @@ SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
11401218

11411219
DROP TABLE test.range_rel CASCADE;
11421220
NOTICE: drop cascades to 16 other objects
1143-
SELECT * FROM pathman.pathman_config;
1144-
id | partrel | attname | parttype | range_interval
1145-
----+---------+---------+----------+----------------
1221+
SELECT partrel, attname, parttype, range_interval FROM pathman.pathman_config;
1222+
partrel | attname | parttype | range_interval
1223+
---------+---------+----------+----------------
11461224
(0 rows)
11471225

11481226
/* Check overlaps */
@@ -1324,10 +1402,10 @@ SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01
13241402

13251403
DROP TABLE test."RangeRel" CASCADE;
13261404
NOTICE: drop cascades to 5 other objects
1327-
SELECT * FROM pathman.pathman_config;
1328-
id | partrel | attname | parttype | range_interval
1329-
----+--------------------+---------+----------+----------------
1330-
9 | test.num_range_rel | id | 2 | 1000
1405+
SELECT partrel, attname, parttype, range_interval FROM pathman.pathman_config;
1406+
partrel | attname | parttype | range_interval
1407+
--------------------+---------+----------+----------------
1408+
test.num_range_rel | id | 2 | 1000
13311409
(1 row)
13321410

13331411
CREATE TABLE test."RangeRel" (

‎hash.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,8 @@ BEGIN
7070
IF partition_data = true THEN
7171
PERFORM @extschema@.disable_parent(parent_relid);
7272
PERFORM @extschema@.partition_data(parent_relid);
73+
ELSE
74+
PERFORM @extschema@.enable_parent(parent_relid);
7375
END IF;
7476

7577
RETURN partitions_count;

‎init.sql

Lines changed: 80 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -34,15 +34,43 @@ CREATE TABLE IF NOT EXISTS @extschema@.pathman_config_params (
3434
CREATE UNIQUE INDEX i_pathman_config_params
3535
ON @extschema@.pathman_config_params(partrel);
3636

37+
/*
38+
* Invalidate relcache every time someone changes parameters config
39+
*/
40+
CREATE OR REPLACE FUNCTION @extschema@.pathman_config_params_trigger_func()
41+
RETURNS TRIGGER AS
42+
$$
43+
BEGIN
44+
IF TG_OP IN ('INSERT', 'UPDATE') THEN
45+
PERFORM @extschema@.invalidate_relcache(NEW.partrel);
46+
END IF;
47+
48+
IF TG_OP IN ('UPDATE', 'DELETE') THEN
49+
PERFORM @extschema@.invalidate_relcache(OLD.partrel);
50+
END IF;
51+
52+
IF TG_OP = 'DELETE' THEN
53+
RETURN OLD;
54+
ELSE
55+
RETURN NEW;
56+
END IF;
57+
END
58+
$$
59+
LANGUAGE plpgsql;
60+
61+
CREATE TRIGGER pathman_config_params_trigger
62+
BEFORE INSERT OR UPDATE OR DELETE ON @extschema@.pathman_config_params
63+
FOR EACH ROW EXECUTE PROCEDURE @extschema@.pathman_config_params_trigger_func();
64+
65+
/*
66+
* Enable dump of config tables with pg_dump
67+
*/
3768
SELECT pg_catalog.pg_extension_config_dump('@extschema@.pathman_config', '');
3869
SELECT pg_catalog.pg_extension_config_dump('@extschema@.pathman_config_params', '');
3970

4071

41-
CREATE OR REPLACE FUNCTION @extschema@.on_enable_parent(relid OID)
42-
RETURNS OID AS 'pg_pathman' LANGUAGE C STRICT;
43-
44-
CREATE OR REPLACE FUNCTION @extschema@.on_disable_parent(relid OID)
45-
RETURNS OID AS 'pg_pathman' LANGUAGE C STRICT;
72+
CREATE OR REPLACE FUNCTION @extschema@.invalidate_relcache(relid OID)
73+
RETURNS VOID AS 'pg_pathman' LANGUAGE C STRICT;
4674

4775
/* Include parent relation into query plan's for specified relation */
4876
CREATE OR REPLACE FUNCTION @extschema@.enable_parent(relation REGCLASS)
@@ -53,7 +81,8 @@ BEGIN
5381
ON CONFLICT (partrel) DO
5482
UPDATE SET enable_parent = True;
5583

56-
PERFORM @extschema@.on_enable_parent(relation::oid);
84+
-- PERFORM @extschema@.invalidate_relcache(relation::oid);
85+
-- PERFORM @extschema@.on_enable_parent(relation::oid);
5786
END
5887
$$
5988
LANGUAGE plpgsql;
@@ -67,7 +96,8 @@ BEGIN
6796
ON CONFLICT (partrel) DO
6897
UPDATE SET enable_parent = False;
6998

70-
PERFORM @extschema@.on_disable_parent(relation::oid);
99+
-- PERFORM @extschema@.invalidate_relcache(relation::oid);
100+
-- PERFORM @extschema@.on_disable_parent(relation::oid);
71101
END
72102
$$
73103
LANGUAGE plpgsql;
@@ -143,12 +173,12 @@ CREATE TYPE @extschema@.PathmanRange (
143173
/*
144174
* Copy rows to partitions
145175
*/
146-
CREATE OR REPLACE FUNCTION @extschema@.partition_data(
147-
p_relation regclass
148-
, p_min ANYELEMENT DEFAULT NULL::text
149-
, p_max ANYELEMENT DEFAULT NULL::text
150-
, p_limit INT DEFAULT NULL
151-
, OUT p_total BIGINT)
176+
CREATE OR REPLACE FUNCTION @extschema@._partition_data_concurrent(
177+
p_relation regclass,
178+
p_min ANYELEMENT DEFAULT NULL::text,
179+
p_max ANYELEMENT DEFAULT NULL::text,
180+
p_limit INT DEFAULT NULL,
181+
OUT p_total BIGINT)
152182
AS
153183
$$
154184
DECLARE
@@ -201,33 +231,30 @@ END
201231
$$
202232
LANGUAGE plpgsql;
203233

204-
/*
205-
* Copy rows to partitions
206-
*/
207-
-- CREATE OR REPLACE FUNCTION @extschema@.partition_data(
208-
-- parent_relid REGCLASS,
209-
-- OUT p_total BIGINT)
210-
-- AS
211-
-- $$
212-
-- DECLARE
213-
-- relname TEXT;
214-
-- rec RECORD;
215-
-- cnt BIGINT := 0;
216-
217-
-- BEGIN
218-
-- p_total := 0;
219-
220-
-- /* Create partitions and copy rest of the data */
221-
-- EXECUTE format('WITH part_data AS (DELETE FROM ONLY %1$s RETURNING *)
222-
-- INSERT INTO %1$s SELECT * FROM part_data',
223-
-- @extschema@.get_schema_qualified_name(parent_relid));
224-
225-
-- /* Get number of inserted rows */
226-
-- GET DIAGNOSTICS p_total = ROW_COUNT;
227-
-- RETURN;
228-
-- END
229-
-- $$
230-
-- LANGUAGE plpgsql;
234+
CREATE OR REPLACE FUNCTION @extschema@.partition_data(
235+
parent_relid REGCLASS,
236+
OUT p_total BIGINT)
237+
AS
238+
$$
239+
DECLARE
240+
relname TEXT;
241+
rec RECORD;
242+
cnt BIGINT := 0;
243+
244+
BEGIN
245+
p_total := 0;
246+
247+
/* Create partitions and copy rest of the data */
248+
EXECUTE format('WITH part_data AS (DELETE FROM ONLY %1$s RETURNING *)
249+
INSERT INTO %1$s SELECT * FROM part_data',
250+
@extschema@.get_schema_qualified_name(parent_relid));
251+
252+
/* Get number of inserted rows */
253+
GET DIAGNOSTICS p_total = ROW_COUNT;
254+
RETURN;
255+
END
256+
$$
257+
LANGUAGE plpgsql;
231258

232259
/*
233260
* Disable pathman partitioning for specified relation
@@ -388,20 +415,26 @@ $$
388415
DECLARE
389416
obj record;
390417
pg_class_oid oid;
391-
392418
BEGIN
393419
pg_class_oid = 'pg_catalog.pg_class'::regclass;
394420

395421
/* Handle 'DROP TABLE' events */
396422
WITH to_be_deleted AS (
397-
SELECT cfg.partrel AS rel
398-
FROM pg_event_trigger_dropped_objects() AS events
399-
JOIN @extschema@.pathman_config AS cfg
400-
ON cfg.partrel::oid = events.objid
423+
SELECT cfg.partrel AS rel FROM pg_event_trigger_dropped_objects() AS events
424+
JOIN @extschema@.pathman_config AS cfg ON cfg.partrel::oid = events.objid
401425
WHERE events.classid = pg_class_oid
402426
)
403427
DELETE FROM @extschema@.pathman_config
404428
WHERE partrel IN (SELECT rel FROM to_be_deleted);
429+
430+
/* Cleanup params table too */
431+
WITH to_be_deleted AS (
432+
SELECT cfg.partrel AS rel FROM pg_event_trigger_dropped_objects() AS events
433+
JOIN @extschema@.pathman_config_params AS cfg ON cfg.partrel::oid = events.objid
434+
WHERE events.classid = pg_class_oid
435+
)
436+
DELETE FROM @extschema@.pathman_config_params
437+
WHERE partrel IN (SELECT rel FROM to_be_deleted);
405438
END
406439
$$
407440
LANGUAGE plpgsql;
@@ -448,6 +481,8 @@ BEGIN
448481
RETURNING *)
449482
SELECT count(*) from config_num_deleted INTO conf_num_del;
450483

484+
DELETE FROM @extschema@.pathman_config_params WHERE partrel = parent_relid;
485+
451486
IF conf_num_del = 0 THEN
452487
RAISE EXCEPTION 'table % has no partitions', parent_relid::text;
453488
END IF;

‎range.sql

Lines changed: 17 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
4343
p_start_value ANYELEMENT,
4444
p_interval INTERVAL,
4545
p_count INTEGER DEFAULT NULL,
46-
p_partition_data BOOLEAN DEFAULT true)
46+
partition_data BOOLEAN DEFAULT true)
4747
RETURNS INTEGER AS
4848
$$
4949
DECLARE
@@ -107,10 +107,12 @@ BEGIN
107107
/* Notify backend about changes */
108108
PERFORM @extschema@.on_create_partitions(parent_relid);
109109

110-
/* Copy data */
111-
IF p_partition_data = true THEN
110+
/* Copy data */
111+
IF partition_data = true THEN
112112
PERFORM @extschema@.disable_parent(parent_relid);
113113
PERFORM @extschema@.partition_data(parent_relid);
114+
ELSE
115+
PERFORM @extschema@.enable_parent(parent_relid);
114116
END IF;
115117

116118
RETURN p_count;
@@ -129,7 +131,7 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
129131
p_start_value ANYELEMENT,
130132
p_interval ANYELEMENT,
131133
p_count INTEGER DEFAULT NULL,
132-
p_partition_data BOOLEAN DEFAULT true)
134+
partition_data BOOLEAN DEFAULT true)
133135
RETURNS INTEGER AS
134136
$$
135137
DECLARE
@@ -199,9 +201,11 @@ BEGIN
199201
PERFORM @extschema@.on_create_partitions(parent_relid);
200202

201203
/* Copy data */
202-
IF p_partition_data = true THEN
204+
IF partition_data = true THEN
203205
PERFORM @extschema@.disable_parent(parent_relid);
204206
PERFORM @extschema@.partition_data(parent_relid);
207+
ELSE
208+
PERFORM @extschema@.enable_parent(parent_relid);
205209
END IF;
206210

207211
RETURN p_count;
@@ -220,7 +224,7 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
220224
p_start_value ANYELEMENT,
221225
p_end_value ANYELEMENT,
222226
p_interval ANYELEMENT,
223-
p_partition_data BOOLEAN DEFAULT true)
227+
partition_data BOOLEAN DEFAULT true)
224228
RETURNS INTEGER AS
225229
$$
226230
DECLARE
@@ -266,9 +270,11 @@ BEGIN
266270
PERFORM @extschema@.on_create_partitions(parent_relid);
267271

268272
/* Copy data */
269-
IF p_partition_data = true THEN
273+
IF partition_data = true THEN
270274
PERFORM @extschema@.disable_parent(parent_relid);
271275
PERFORM @extschema@.partition_data(parent_relid);
276+
ELSE
277+
PERFORM @extschema@.enable_parent(parent_relid);
272278
END IF;
273279

274280
RETURN part_count; /* number of created partitions */
@@ -287,7 +293,7 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
287293
p_start_value ANYELEMENT,
288294
p_end_value ANYELEMENT,
289295
p_interval INTERVAL,
290-
p_partition_data BOOLEAN DEFAULT true)
296+
partition_data BOOLEAN DEFAULT true)
291297
RETURNS INTEGER AS
292298
$$
293299
DECLARE
@@ -330,9 +336,11 @@ BEGIN
330336
PERFORM @extschema@.on_create_partitions(parent_relid);
331337

332338
/* Copy data */
333-
IF p_partition_data = true THEN
339+
IF partition_data = true THEN
334340
PERFORM @extschema@.disable_parent(parent_relid);
335341
PERFORM @extschema@.partition_data(parent_relid);
342+
ELSE
343+
PERFORM @extschema@.enable_parent(parent_relid);
336344
END IF;
337345

338346
RETURN part_count; /* number of created partitions */

0 commit comments

Comments
 (0)