Skip to content

Commit eb38a47

Browse files
zilderzilder
committed
pathman:
* merge partitions function
1 parent e39af82 commit eb38a47

File tree

2 files changed

+145
-13
lines changed

2 files changed

+145
-13
lines changed

‎contrib/pathman/sql/init.sql

Lines changed: 21 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -5,19 +5,18 @@ CREATE TABLE IF NOT EXISTS @extschema@.pg_pathman_rels (
55
id SERIAL PRIMARY KEY,
66
relname VARCHAR(127),
77
attname VARCHAR(127),
8-
-- atttype INTEGER,
98
parttype INTEGER
109
);
1110

1211
/*
1312
* Relations using hash strategy
1413
*/
15-
CREATE TABLE IF NOT EXISTS @extschema@.pg_pathman_hash_rels (
16-
id SERIAL PRIMARY KEY,
17-
parent VARCHAR(127),
18-
hash INTEGER,
19-
child VARCHAR(127)
20-
);
14+
-- CREATE TABLE IF NOT EXISTS @extschema@.pg_pathman_hash_rels (
15+
-- id SERIAL PRIMARY KEY,
16+
-- parent VARCHAR(127),
17+
-- hash INTEGER,
18+
-- child VARCHAR(127)
19+
-- );
2120

2221
/*
2322
* Relations using range strategy
@@ -207,6 +206,21 @@ END
207206
$$ LANGUAGE plpgsql;
208207

209208

209+
/*
210+
* Disable pathman partitioning for specified relation
211+
*/
212+
CREATE OR REPLACE FUNCTION disable_partitioning(IN relation TEXT)
213+
RETURNS VOID AS
214+
$$
215+
BEGIN
216+
DELETE FROM pg_pathman_rels WHERE relname = relation;
217+
218+
/* Notify backend about changes */
219+
PERFORM pg_pathman_on_remove_partitions(relation::regclass::oid);
220+
END
221+
$$ LANGUAGE plpgsql;
222+
223+
210224
-- CREATE OR REPLACE FUNCTION sample_rel_trigger_func()
211225
-- RETURNS TRIGGER AS $$
212226
-- DECLARE

‎contrib/pathman/sql/range.sql

Lines changed: 124 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -250,28 +250,29 @@ $$
250250
DECLARE
251251
v_parent_relid OID;
252252
v_child_relid OID := p_partition::regclass::oid;
253-
v_atttype INT;
254253
v_attname TEXT;
255-
-- v_range ANYARRAY;
256-
-- v_min ANYELEMENT;
257-
-- v_max ANYELEMENT;
258254
v_cond TEXT;
259255
v_new_partition TEXT;
256+
v_part_type INTEGER;
260257
BEGIN
261258
v_parent_relid := inhparent
262259
FROM pg_inherits
263260
WHERE inhrelid = v_child_relid;
264261

265-
SELECT attname INTO v_attname
262+
SELECT attname, parttype INTO v_attname, v_part_type
266263
FROM pg_pathman_rels
267264
WHERE relname = v_parent_relid::regclass::text;
268265

266+
/* Check if this is RANGE partition */
267+
IF v_part_type != 2 THEN
268+
RAISE EXCEPTION 'Specified partition isn''t RANGE partition';
269+
END IF;
270+
269271
/* Get partition values range */
270272
p_range := get_partition_range(v_parent_relid, v_child_relid);
271273
IF p_range IS NULL THEN
272274
RAISE EXCEPTION 'Could not find specified partition';
273275
END IF;
274-
RAISE NOTICE 'range: % - %', p_range[1], p_range[2];
275276

276277
/* Check if value fit into the range */
277278
IF p_range[1] > p_value OR p_range[2] <= p_value
@@ -314,6 +315,123 @@ $$
314315
LANGUAGE plpgsql;
315316

316317

318+
/*
319+
* Merge RANGE partitions
320+
*
321+
* Note: we had to have at least one argument of type
322+
*/
323+
-- , OUT p_range1 ANYARRAY
324+
CREATE OR REPLACE FUNCTION merge_range_partitions(
325+
p_partition1 TEXT
326+
, p_partition2 TEXT)
327+
RETURNS VOID AS
328+
$$
329+
DECLARE
330+
v_parent_relid1 OID;
331+
v_parent_relid2 OID;
332+
v_part1_relid OID := p_partition1::regclass::oid;
333+
v_part2_relid OID := p_partition2::regclass::oid;
334+
v_attname TEXT;
335+
v_part_type INTEGER;
336+
v_atttype TEXT;
337+
BEGIN
338+
IF v_part1_relid = v_part2_relid THEN
339+
RAISE EXCEPTION 'Cannot merge partition with itself';
340+
END IF;
341+
342+
v_parent_relid1 := inhparent FROM pg_inherits WHERE inhrelid = v_part1_relid;
343+
v_parent_relid2 := inhparent FROM pg_inherits WHERE inhrelid = v_part2_relid;
344+
345+
IF v_parent_relid1 != v_parent_relid2 THEN
346+
RAISE EXCEPTION 'Cannot merge partitions having different parents';
347+
END IF;
348+
349+
SELECT attname, parttype INTO v_attname, v_part_type
350+
FROM pg_pathman_rels
351+
WHERE relname = v_parent_relid1::regclass::text;
352+
353+
/* Check if this is RANGE partition */
354+
IF v_part_type != 2 THEN
355+
RAISE EXCEPTION 'Specified partitions aren''t RANGE partitions';
356+
END IF;
357+
358+
SELECT typname INTO v_atttype
359+
FROM pg_type
360+
JOIN pg_attribute on atttypid = "oid"
361+
WHERE attrelid = 'num_range_rel'::regclass::oid and attname = lower(v_attname);
362+
363+
EXECUTE format('SELECT merge_range_partitions_internal($1, $2 , $3, NULL::%s)', v_atttype)
364+
USING v_parent_relid1, v_part1_relid , v_part2_relid;
365+
END
366+
$$
367+
LANGUAGE plpgsql;
368+
369+
370+
/*
371+
* Merge two partitions. All data will be copied to the first one. Second
372+
* partition will be destroyed.
373+
*
374+
* Notes: dummy field is used to pass the element type to the function
375+
* (it is neccessary because of pseudo-types used in function)
376+
*/
377+
CREATE OR REPLACE FUNCTION merge_range_partitions_internal(
378+
p_parent_relid OID
379+
, p_part1_relid OID
380+
, p_part2_relid OID
381+
, dummy ANYELEMENT
382+
, OUT p_range ANYARRAY)
383+
RETURNS ANYARRAY AS
384+
$$
385+
DECLARE
386+
v_attname TEXT;
387+
v_cond TEXT;
388+
BEGIN
389+
SELECT attname INTO v_attname FROM pg_pathman_rels
390+
WHERE relname = p_parent_relid::regclass::text;
391+
392+
/*
393+
* Get ranges
394+
* first and second elements of array are MIN and MAX of partition1
395+
* third and forth elements are MIN and MAX of partition2
396+
*/
397+
p_range := get_partition_range(p_parent_relid, p_part1_relid) ||
398+
get_partition_range(p_parent_relid, p_part2_relid);
399+
RAISE NOTICE 'type: %', pg_typeof(p_range[1]);
400+
RAISE NOTICE 'min %, max %', pg_typeof(least(p_range[1], p_range[3])),
401+
pg_typeof(greatest(p_range[2], p_range[4]));
402+
403+
/* Check if ranges are adjacent */
404+
IF p_range[1] != p_range[4] AND p_range[2] != p_range[3] THEN
405+
RAISE EXCEPTION 'Merge failed. Partitions must be adjacent';
406+
END IF;
407+
408+
/* Extend first partition */
409+
v_cond := get_range_condition(v_attname
410+
, least(p_range[1], p_range[3])
411+
, greatest(p_range[2], p_range[4]) - least(p_range[1], p_range[3]));
412+
RAISE NOTICE 'cond: %', v_cond;
413+
414+
/* Alter first table */
415+
EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %s_%s_check'
416+
, p_part1_relid::regclass::text
417+
, p_part1_relid::regclass::text
418+
, v_attname);
419+
EXECUTE format('ALTER TABLE %s ADD CHECK (%s)'
420+
, p_part1_relid::regclass::text
421+
, v_cond);
422+
423+
/* Copy data from second partition to the first one */
424+
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
425+
INSERT INTO %s SELECT * FROM part_data'
426+
, p_part2_relid::regclass::text
427+
, p_part1_relid::regclass::text);
428+
429+
/* Remove second partition */
430+
EXECUTE format('DROP TABLE %s', p_part2_relid::regclass::text);
431+
END
432+
$$ LANGUAGE plpgsql;
433+
434+
317435
/*
318436
* Creates range partitioning insert trigger
319437
*/

0 commit comments

Comments
 (0)