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

Commit92e46ac

Browse files
committed
introduce fixed function drop_partitions()
1 parent32d7514 commit92e46ac

File tree

4 files changed

+62
-95
lines changed

4 files changed

+62
-95
lines changed

‎hash.sql

Lines changed: 0 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -66,48 +66,6 @@ BEGIN
6666
END
6767
$$ LANGUAGE plpgsql;
6868

69-
/*
70-
* Drops all partitions for specified relation
71-
*/
72-
CREATEOR REPLACE FUNCTION @extschema@.drop_hash_partitions(
73-
IN relation REGCLASS
74-
, delete_dataBOOLEAN DEFAULT FALSE)
75-
RETURNSINTEGERAS
76-
$$
77-
DECLARE
78-
v_relnameTEXT;
79-
v_rec RECORD;
80-
v_rowsINTEGER;
81-
v_part_countINTEGER :=0;
82-
BEGIN
83-
v_relname := @extschema@.validate_relname(relation);
84-
85-
/* Drop trigger first*/
86-
PERFORM @extschema@.drop_triggers(relation);
87-
DELETEFROM @extschema@.pathman_configWHERE relname::regclass= relation;
88-
89-
FOR v_recin (SELECT inhrelid::regclass::textAS tbl
90-
FROM pg_inheritsWHERE inhparent= relation::oid)
91-
LOOP
92-
IF NOT delete_data THEN
93-
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
94-
INSERT INTO %s SELECT * FROM part_data'
95-
,v_rec.tbl
96-
, relation::text);
97-
GET DIAGNOSTICS v_rows= ROW_COUNT;
98-
RAISE NOTICE'% rows copied from %', v_rows,v_rec.tbl;
99-
END IF;
100-
EXECUTE format('DROP TABLE %s',v_rec.tbl);
101-
v_part_count := v_part_count+1;
102-
END LOOP;
103-
104-
/* Notify backend about changes*/
105-
PERFORM @extschema@.on_remove_partitions(relation::oid);
106-
107-
RETURN v_part_count;
108-
END
109-
$$ LANGUAGE plpgsql;
110-
11169
/*
11270
* Creates an update trigger
11371
*/

‎init.sql

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -352,3 +352,57 @@ BEGIN
352352
EXECUTE format('DROP FUNCTION IF EXISTS %s() CASCADE', funcname);
353353
END
354354
$$ LANGUAGE plpgsql;
355+
356+
/*
357+
* Drop partitions
358+
* If delete_data set to TRUE then partitions will be dropped with all the data
359+
*/
360+
CREATEOR REPLACE FUNCTION @extschema@.drop_partitions(
361+
relation REGCLASS
362+
, delete_dataBOOLEAN DEFAULT FALSE)
363+
RETURNSINTEGERAS
364+
$$
365+
DECLARE
366+
v_rec RECORD;
367+
v_rowsINTEGER;
368+
v_part_countINTEGER :=0;
369+
v_relnameTEXT;
370+
conf_num_delINTEGER;
371+
BEGIN
372+
v_relname := @extschema@.validate_relname(relation);
373+
374+
/* Drop trigger first*/
375+
PERFORM @extschema@.drop_triggers(relation);
376+
377+
WITH config_num_deletedAS (DELETEFROM @extschema@.pathman_config
378+
WHERE relname::regclass= relation
379+
RETURNING*)
380+
SELECTcount(*)from config_num_deleted INTO conf_num_del;
381+
382+
IF conf_num_del=0 THEN
383+
RAISE EXCEPTION'table % has no partitions', relation::text;
384+
END IF;
385+
386+
FOR v_recIN (SELECT inhrelid::regclass::textAS tbl
387+
FROM pg_inheritsWHERE inhparent::regclass= relation)
388+
LOOP
389+
IF NOT delete_data THEN
390+
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
391+
INSERT INTO %s SELECT * FROM part_data'
392+
,v_rec.tbl
393+
, relation::text);
394+
GET DIAGNOSTICS v_rows= ROW_COUNT;
395+
RAISE NOTICE'% rows copied from %', v_rows,v_rec.tbl;
396+
END IF;
397+
EXECUTE format('DROP TABLE %s',v_rec.tbl);
398+
v_part_count := v_part_count+1;
399+
END LOOP;
400+
401+
/* Notify backend about changes*/
402+
PERFORM @extschema@.on_remove_partitions(relation::oid);
403+
404+
RETURN v_part_count;
405+
END
406+
$$ LANGUAGE plpgsql
407+
SETpg_pathman.enable_partitionfilter= off;
408+

‎range.sql

Lines changed: 0 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -1075,51 +1075,6 @@ BEGIN
10751075
END
10761076
$$ LANGUAGE plpgsql;
10771077

1078-
1079-
/*
1080-
* Drop partitions
1081-
* If delete_data set to TRUE then partitions will be dropped with all the data
1082-
*/
1083-
CREATEOR REPLACE FUNCTION @extschema@.drop_range_partitions(
1084-
relation REGCLASS
1085-
, delete_dataBOOLEAN DEFAULT FALSE)
1086-
RETURNSINTEGERAS
1087-
$$
1088-
DECLARE
1089-
v_rec RECORD;
1090-
v_rowsINTEGER;
1091-
v_part_countINTEGER :=0;
1092-
v_relnameTEXT;
1093-
BEGIN
1094-
v_relname := @extschema@.validate_relname(relation);
1095-
1096-
/* Drop trigger first*/
1097-
PERFORM @extschema@.drop_triggers(relation);
1098-
1099-
FOR v_recIN (SELECT inhrelid::regclass::textAS tbl
1100-
FROM pg_inheritsWHERE inhparent::regclass= relation)
1101-
LOOP
1102-
IF NOT delete_data THEN
1103-
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
1104-
INSERT INTO %s SELECT * FROM part_data'
1105-
,v_rec.tbl
1106-
, relation::text);
1107-
GET DIAGNOSTICS v_rows= ROW_COUNT;
1108-
RAISE NOTICE'% rows copied from %', v_rows,v_rec.tbl;
1109-
END IF;
1110-
EXECUTE format('DROP TABLE %s',v_rec.tbl);
1111-
v_part_count := v_part_count+1;
1112-
END LOOP;
1113-
1114-
DELETEFROM @extschema@.pathman_configWHERE relname::regclass= relation;
1115-
1116-
/* Notify backend about changes*/
1117-
PERFORM @extschema@.on_remove_partitions(relation::oid);
1118-
1119-
RETURN v_part_count;
1120-
END
1121-
$$ LANGUAGE plpgsql;
1122-
11231078
/*
11241079
* Internal function used to create new partitions on insert or update trigger.
11251080
* Invoked from C-function find_or_create_range_partition().

‎sql/pg_pathman.sql

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -396,14 +396,14 @@ SELECT * FROM test.hash_rel WHERE id = 123;
396396
/*
397397
* Clean up
398398
*/
399-
SELECTpathman.drop_hash_partitions('test.hash_rel');
399+
SELECTpathman.drop_partitions('test.hash_rel');
400400
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
401401
SELECTpathman.create_hash_partitions('test.hash_rel','value',3);
402-
SELECTpathman.drop_hash_partitions('test.hash_rel', TRUE);
402+
SELECTpathman.drop_partitions('test.hash_rel', TRUE);
403403
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
404404
DROPTABLEtest.hash_rel CASCADE;
405405

406-
SELECTpathman.drop_range_partitions('test.num_range_rel');
406+
SELECTpathman.drop_partitions('test.num_range_rel');
407407
DROPTABLEtest.num_range_rel CASCADE;
408408

409409
DROPTABLEtest.range_rel CASCADE;
@@ -453,7 +453,7 @@ UPDATE test."TeSt" SET a = 1;
453453
SELECT*FROM test."TeSt";
454454
SELECT*FROM test."TeSt"WHERE a=1;
455455
EXPLAIN (COSTS OFF)SELECT*FROM test."TeSt"WHERE a=1;
456-
SELECTpathman.drop_hash_partitions('test."TeSt"');
456+
SELECTpathman.drop_partitions('test."TeSt"');
457457
SELECT*FROM test."TeSt";
458458

459459
CREATETABLEtest."RangeRel" (
@@ -467,7 +467,7 @@ SELECT pathman.append_range_partition('test."RangeRel"');
467467
SELECTpathman.prepend_range_partition('test."RangeRel"');
468468
SELECTpathman.merge_range_partitions('test."RangeRel_1"','test."RangeRel_'|| currval('test."RangeRel_seq"')||'"');
469469
SELECTpathman.split_range_partition('test."RangeRel_1"','2015-01-01'::DATE);
470-
SELECTpathman.drop_range_partitions('test."RangeRel"');
470+
SELECTpathman.drop_partitions('test."RangeRel"');
471471
SELECTpathman.create_partitions_from_range('test."RangeRel"','dt','2015-01-01'::DATE,'2015-01-05'::DATE,'1 day'::INTERVAL);
472472
DROPTABLE test."RangeRel" CASCADE;
473473
SELECT*FROMpathman.pathman_config;
@@ -476,7 +476,7 @@ CREATE TABLE test."RangeRel" (
476476
dtTIMESTAMPNOT NULL,
477477
txtTEXT);
478478
SELECTpathman.create_range_partitions('test."RangeRel"','id',1,100,3);
479-
SELECTpathman.drop_range_partitions('test."RangeRel"');
479+
SELECTpathman.drop_partitions('test."RangeRel"');
480480
SELECTpathman.create_partitions_from_range('test."RangeRel"','id',1,300,100);
481481
DROPTABLE test."RangeRel" CASCADE;
482482

@@ -524,9 +524,9 @@ EXPLAIN (COSTS OFF) DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02
524524
DELETEFROM range_rel r USING tmp tWHEREr.dt='2010-01-02'ANDr.id=t.id;
525525

526526
/* Create range partitions from whole range*/
527-
SELECTdrop_range_partitions('range_rel');
527+
SELECTdrop_partitions('range_rel');
528528
SELECT create_partitions_from_range('range_rel','id',1,1000,100);
529-
SELECTdrop_range_partitions('range_rel', TRUE);
529+
SELECTdrop_partitions('range_rel', TRUE);
530530
SELECT create_partitions_from_range('range_rel','dt','2015-01-01'::date,'2015-12-01'::date,'1 month'::interval);
531531
EXPLAIN (COSTS OFF)SELECT*FROM range_relWHERE dt='2015-12-15';
532532

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp