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

Commit66f10cd

Browse files
committed
pathman: drop hash partitions function updated
1 parentb2af9ac commit66f10cd

File tree

4 files changed

+61
-13
lines changed

4 files changed

+61
-13
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -554,9 +554,41 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' A
554554
SELECT pathman.drop_hash_partitions('test.hash_rel');
555555
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
556556
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
557+
NOTICE: 2 rows copied from test.hash_rel_2
558+
NOTICE: 3 rows copied from test.hash_rel_1
559+
NOTICE: 1 rows copied from test.hash_rel_0
557560
drop_hash_partitions
558561
----------------------
559-
562+
3
563+
(1 row)
564+
565+
SELECT COUNT(*) FROM ONLY test.hash_rel;
566+
count
567+
-------
568+
6
569+
(1 row)
570+
571+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
572+
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
573+
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
574+
NOTICE: Copying data to partitions...
575+
create_hash_partitions
576+
------------------------
577+
3
578+
(1 row)
579+
580+
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
581+
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
582+
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
583+
drop_hash_partitions
584+
----------------------
585+
3
586+
(1 row)
587+
588+
SELECT COUNT(*) FROM ONLY test.hash_rel;
589+
count
590+
-------
591+
0
560592
(1 row)
561593

562594
DROP TABLE test.hash_rel CASCADE;

‎contrib/pg_pathman/hash.sql‎

Lines changed: 21 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -111,28 +111,41 @@ $$ LANGUAGE plpgsql;
111111
/*
112112
* Drops all partitions for specified relation
113113
*/
114-
CREATEOR REPLACE FUNCTION @extschema@.drop_hash_partitions(IN relationTEXT)
115-
RETURNS VOIDAS
114+
CREATEOR REPLACE FUNCTION @extschema@.drop_hash_partitions(
115+
IN relationTEXT
116+
, delete_dataBOOLEAN DEFAULT FALSE)
117+
RETURNSINTEGERAS
116118
$$
117119
DECLARE
118-
relidINTEGER;
119-
partitions_countINTEGER;
120-
rec RECORD;
121-
numINTEGER :=0;
120+
v_rec RECORD;
121+
v_rowsINTEGER;
122+
v_part_countINTEGER :=0;
122123
BEGIN
123124
relation := @extschema@.validate_relname(relation);
124125

125126
/* Drop trigger first*/
126127
PERFORM @extschema@.drop_hash_triggers(relation);
127128
DELETEFROM @extschema@.pathman_configWHERE relname= relation;
128129

129-
FOR recin (SELECT*FROM pg_inheritsWHERE inhparent= relation::regclass::oid)
130+
FOR v_recin (SELECT inhrelid::regclass::textAS tbl
131+
FROM pg_inheritsWHERE inhparent= relation::regclass::oid)
130132
LOOP
131-
EXECUTE format('DROP TABLE %s',rec.inhrelid::regclass::text);
133+
IF NOT delete_data THEN
134+
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
135+
INSERT INTO %s SELECT * FROM part_data'
136+
,v_rec.tbl
137+
, relation);
138+
GET DIAGNOSTICS v_rows= ROW_COUNT;
139+
RAISE NOTICE'% rows copied from %', v_rows,v_rec.tbl;
140+
END IF;
141+
EXECUTE format('DROP TABLE %s',v_rec.tbl);
142+
v_part_count := v_part_count+1;
132143
END LOOP;
133144

134145
/* Notify backend about changes*/
135146
PERFORM @extschema@.on_remove_partitions(relation::regclass::oid);
147+
148+
RETURN v_part_count;
136149
END
137150
$$ LANGUAGE plpgsql;
138151

‎contrib/pg_pathman/range.sql‎

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -949,7 +949,7 @@ BEGIN
949949

950950
/* Invalidate cache*/
951951
PERFORM @extschema@.on_update_partitions(v_parent::regclass::oid);
952-
952+
953953
/* Release lock*/
954954
PERFORM @extschema@.release_partitions_lock();
955955
RETURN p_partition;
@@ -1082,13 +1082,12 @@ $$ LANGUAGE plpgsql;
10821082
* If delete_data set to TRUE then partitions will be dropped with all the data
10831083
*/
10841084
CREATEOR REPLACE FUNCTION @extschema@.drop_range_partitions(
1085-
relationTEXT,
1086-
delete_dataBOOLEAN DEFAULT FALSE)
1085+
relationTEXT
1086+
,delete_dataBOOLEAN DEFAULT FALSE)
10871087
RETURNSINTEGERAS
10881088
$$
10891089
DECLARE
10901090
v_rec RECORD;
1091-
-- v_total_rows INTEGER;
10921091
v_rowsINTEGER;
10931092
v_part_countINTEGER :=0;
10941093
BEGIN

‎contrib/pg_pathman/sql/pg_pathman.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' A
126126
* Clean up
127127
*/
128128
SELECTpathman.drop_hash_partitions('test.hash_rel');
129+
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
130+
SELECTpathman.create_hash_partitions('test.hash_rel','value',3);
131+
SELECTpathman.drop_hash_partitions('test.hash_rel', TRUE);
132+
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
129133
DROPTABLEtest.hash_rel CASCADE;
130134

131135
SELECTpathman.drop_range_partitions('test.num_range_rel');

0 commit comments

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