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

Commiteb38a47

Browse files
committed
pathman:
* merge partitions function
1 parente39af82 commiteb38a47

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
idSERIALPRIMARY KEY,
66
relnameVARCHAR(127),
77
attnameVARCHAR(127),
8-
-- atttype INTEGER,
98
parttypeINTEGER
109
);
1110

1211
/*
1312
* Relations using hash strategy
1413
*/
15-
CREATETABLEIF NOT EXISTS @extschema@.pg_pathman_hash_rels (
16-
idSERIALPRIMARY KEY,
17-
parentVARCHAR(127),
18-
hashINTEGER,
19-
childVARCHAR(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 REPLACEFUNCTIONdisable_partitioning(IN relationTEXT)
213+
RETURNS VOIDAS
214+
$$
215+
BEGIN
216+
DELETEFROM pg_pathman_relsWHERE 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_relidOID;
252252
v_child_relidOID := p_partition::regclass::oid;
253-
v_atttypeINT;
254253
v_attnameTEXT;
255-
-- v_range ANYARRAY;
256-
-- v_min ANYELEMENT;
257-
-- v_max ANYELEMENT;
258254
v_condTEXT;
259255
v_new_partitionTEXT;
256+
v_part_typeINTEGER;
260257
BEGIN
261258
v_parent_relid := inhparent
262259
FROM pg_inherits
263260
WHERE inhrelid= v_child_relid;
264261

265-
SELECT attnameINTO v_attname
262+
SELECT attname, parttypeINTO 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 ISNULL 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_valueOR 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 REPLACEFUNCTIONmerge_range_partitions(
325+
p_partition1TEXT
326+
, p_partition2TEXT)
327+
RETURNS VOIDAS
328+
$$
329+
DECLARE
330+
v_parent_relid1OID;
331+
v_parent_relid2OID;
332+
v_part1_relidOID := p_partition1::regclass::oid;
333+
v_part2_relidOID := p_partition2::regclass::oid;
334+
v_attnameTEXT;
335+
v_part_typeINTEGER;
336+
v_atttypeTEXT;
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 := inhparentFROM pg_inheritsWHERE inhrelid= v_part1_relid;
343+
v_parent_relid2 := inhparentFROM pg_inheritsWHERE 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_attributeon atttypid="oid"
361+
WHERE attrelid='num_range_rel'::regclass::oidand 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 REPLACEFUNCTIONmerge_range_partitions_internal(
378+
p_parent_relidOID
379+
, p_part1_relidOID
380+
, p_part2_relidOID
381+
, dummy ANYELEMENT
382+
, OUT p_range ANYARRAY)
383+
RETURNS ANYARRAYAS
384+
$$
385+
DECLARE
386+
v_attnameTEXT;
387+
v_condTEXT;
388+
BEGIN
389+
SELECT attname INTO v_attnameFROM 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)
[8]ページ先頭
©2009-2025 Movatter.jp