Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit38acbd0

Browse files
committed
concurrent partitioning integration
1 parentf5ba660 commit38acbd0

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+
SELECTpartrel, 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+
SELECTpartrel, 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
CREATEUNIQUE INDEXi_pathman_config_params
3535
ON @extschema@.pathman_config_params(partrel);
3636

37+
/*
38+
* Invalidate relcache every time someone changes parameters config
39+
*/
40+
CREATEOR REPLACE FUNCTION @extschema@.pathman_config_params_trigger_func()
41+
RETURNS TRIGGERAS
42+
$$
43+
BEGIN
44+
IF TG_OPIN ('INSERT','UPDATE') THEN
45+
PERFORM @extschema@.invalidate_relcache(NEW.partrel);
46+
END IF;
47+
48+
IF TG_OPIN ('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+
CREATETRIGGERpathman_config_params_trigger
62+
BEFORE INSERTORUPDATEORDELETEON @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
SELECTpg_catalog.pg_extension_config_dump('@extschema@.pathman_config','');
3869
SELECTpg_catalog.pg_extension_config_dump('@extschema@.pathman_config_params','');
3970

4071

41-
CREATEOR REPLACE FUNCTION @extschema@.on_enable_parent(relidOID)
42-
RETURNSOIDAS'pg_pathman' LANGUAGE C STRICT;
43-
44-
CREATEOR REPLACE FUNCTION @extschema@.on_disable_parent(relidOID)
45-
RETURNSOIDAS'pg_pathman' LANGUAGE C STRICT;
72+
CREATEOR REPLACE FUNCTION @extschema@.invalidate_relcache(relidOID)
73+
RETURNS VOIDAS'pg_pathman' LANGUAGE C STRICT;
4674

4775
/* Include parent relation into query plan's for specified relation*/
4876
CREATEOR REPLACE FUNCTION @extschema@.enable_parent(relation REGCLASS)
@@ -53,7 +81,8 @@ BEGIN
5381
ON CONFLICT (partrel) DO
5482
UPDATESET 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
UPDATESET 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-
CREATEOR REPLACE FUNCTION @extschema@.partition_data(
147-
p_relation regclass
148-
,p_min ANYELEMENT DEFAULTNULL::text
149-
,p_max ANYELEMENT DEFAULTNULL::text
150-
,p_limitINT DEFAULTNULL
151-
,OUT p_totalBIGINT)
176+
CREATEOR REPLACE FUNCTION @extschema@._partition_data_concurrent(
177+
p_relation regclass,
178+
p_min ANYELEMENT DEFAULTNULL::text,
179+
p_max ANYELEMENT DEFAULTNULL::text,
180+
p_limitINT DEFAULTNULL,
181+
OUT p_totalBIGINT)
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_relidREGCLASS,
209-
-- OUT p_totalBIGINT)
210-
-- AS
211-
-- $$
212-
-- DECLARE
213-
-- relnameTEXT;
214-
-- recRECORD;
215-
-- cntBIGINT := 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+
CREATEOR REPLACE FUNCTION @extschema@.partition_data(
235+
parent_relidREGCLASS,
236+
OUT p_totalBIGINT)
237+
AS
238+
$$
239+
DECLARE
240+
relnameTEXT;
241+
recRECORD;
242+
cntBIGINT :=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
objrecord;
390417
pg_class_oidoid;
391-
392418
BEGIN
393419
pg_class_oid='pg_catalog.pg_class'::regclass;
394420

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

484+
DELETEFROM @extschema@.pathman_config_paramsWHERE 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_valueANYELEMENT,
4444
p_intervalINTERVAL,
4545
p_countINTEGER DEFAULTNULL,
46-
p_partition_dataBOOLEAN DEFAULT true)
46+
partition_dataBOOLEAN DEFAULT true)
4747
RETURNSINTEGERAS
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-
IFp_partition_data= true THEN
110+
/* Copy data*/
111+
IFpartition_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_valueANYELEMENT,
130132
p_intervalANYELEMENT,
131133
p_countINTEGER DEFAULTNULL,
132-
p_partition_dataBOOLEAN DEFAULT true)
134+
partition_dataBOOLEAN DEFAULT true)
133135
RETURNSINTEGERAS
134136
$$
135137
DECLARE
@@ -199,9 +201,11 @@ BEGIN
199201
PERFORM @extschema@.on_create_partitions(parent_relid);
200202

201203
/* Copy data*/
202-
IFp_partition_data= true THEN
204+
IFpartition_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_valueANYELEMENT,
221225
p_end_valueANYELEMENT,
222226
p_intervalANYELEMENT,
223-
p_partition_dataBOOLEAN DEFAULT true)
227+
partition_dataBOOLEAN DEFAULT true)
224228
RETURNSINTEGERAS
225229
$$
226230
DECLARE
@@ -266,9 +270,11 @@ BEGIN
266270
PERFORM @extschema@.on_create_partitions(parent_relid);
267271

268272
/* Copy data*/
269-
IFp_partition_data= true THEN
273+
IFpartition_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_valueANYELEMENT,
288294
p_end_valueANYELEMENT,
289295
p_intervalINTERVAL,
290-
p_partition_dataBOOLEAN DEFAULT true)
296+
partition_dataBOOLEAN DEFAULT true)
291297
RETURNSINTEGERAS
292298
$$
293299
DECLARE
@@ -330,9 +336,11 @@ BEGIN
330336
PERFORM @extschema@.on_create_partitions(parent_relid);
331337

332338
/* Copy data*/
333-
IFp_partition_data= true THEN
339+
IFpartition_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)
[8]ページ先頭
©2009-2025 Movatter.jp