Skip to content

Commit 92e46ac

Browse files
funbringerfunbringer
committed
introduce fixed function drop_partitions()
1 parent 32d7514 commit 92e46ac

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-
CREATE OR REPLACE FUNCTION @extschema@.drop_hash_partitions(
73-
IN relation REGCLASS
74-
, delete_data BOOLEAN DEFAULT FALSE)
75-
RETURNS INTEGER AS
76-
$$
77-
DECLARE
78-
v_relname TEXT;
79-
v_rec RECORD;
80-
v_rows INTEGER;
81-
v_part_count INTEGER := 0;
82-
BEGIN
83-
v_relname := @extschema@.validate_relname(relation);
84-
85-
/* Drop trigger first */
86-
PERFORM @extschema@.drop_triggers(relation);
87-
DELETE FROM @extschema@.pathman_config WHERE relname::regclass = relation;
88-
89-
FOR v_rec in (SELECT inhrelid::regclass::text AS tbl
90-
FROM pg_inherits WHERE 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+
CREATE OR REPLACE FUNCTION @extschema@.drop_partitions(
361+
relation REGCLASS
362+
, delete_data BOOLEAN DEFAULT FALSE)
363+
RETURNS INTEGER AS
364+
$$
365+
DECLARE
366+
v_rec RECORD;
367+
v_rows INTEGER;
368+
v_part_count INTEGER := 0;
369+
v_relname TEXT;
370+
conf_num_del INTEGER;
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_deleted AS (DELETE FROM @extschema@.pathman_config
378+
WHERE relname::regclass = relation
379+
RETURNING *)
380+
SELECT count(*) 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_rec IN (SELECT inhrelid::regclass::text AS tbl
387+
FROM pg_inherits WHERE 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+
SET pg_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-
CREATE OR REPLACE FUNCTION @extschema@.drop_range_partitions(
1084-
relation REGCLASS
1085-
, delete_data BOOLEAN DEFAULT FALSE)
1086-
RETURNS INTEGER AS
1087-
$$
1088-
DECLARE
1089-
v_rec RECORD;
1090-
v_rows INTEGER;
1091-
v_part_count INTEGER := 0;
1092-
v_relname TEXT;
1093-
BEGIN
1094-
v_relname := @extschema@.validate_relname(relation);
1095-
1096-
/* Drop trigger first */
1097-
PERFORM @extschema@.drop_triggers(relation);
1098-
1099-
FOR v_rec IN (SELECT inhrelid::regclass::text AS tbl
1100-
FROM pg_inherits WHERE 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-
DELETE FROM @extschema@.pathman_config WHERE 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-
SELECT pathman.drop_hash_partitions('test.hash_rel');
399+
SELECT pathman.drop_partitions('test.hash_rel');
400400
SELECT COUNT(*) FROM ONLY test.hash_rel;
401401
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
402-
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
402+
SELECT pathman.drop_partitions('test.hash_rel', TRUE);
403403
SELECT COUNT(*) FROM ONLY test.hash_rel;
404404
DROP TABLE test.hash_rel CASCADE;
405405

406-
SELECT pathman.drop_range_partitions('test.num_range_rel');
406+
SELECT pathman.drop_partitions('test.num_range_rel');
407407
DROP TABLE test.num_range_rel CASCADE;
408408

409409
DROP TABLE test.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-
SELECT pathman.drop_hash_partitions('test."TeSt"');
456+
SELECT pathman.drop_partitions('test."TeSt"');
457457
SELECT * FROM test."TeSt";
458458

459459
CREATE TABLE test."RangeRel" (
@@ -467,7 +467,7 @@ SELECT pathman.append_range_partition('test."RangeRel"');
467467
SELECT pathman.prepend_range_partition('test."RangeRel"');
468468
SELECT pathman.merge_range_partitions('test."RangeRel_1"', 'test."RangeRel_' || currval('test."RangeRel_seq"') || '"');
469469
SELECT pathman.split_range_partition('test."RangeRel_1"', '2015-01-01'::DATE);
470-
SELECT pathman.drop_range_partitions('test."RangeRel"');
470+
SELECT pathman.drop_partitions('test."RangeRel"');
471471
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01'::DATE, '2015-01-05'::DATE, '1 day'::INTERVAL);
472472
DROP TABLE test."RangeRel" CASCADE;
473473
SELECT * FROM pathman.pathman_config;
@@ -476,7 +476,7 @@ CREATE TABLE test."RangeRel" (
476476
dt TIMESTAMP NOT NULL,
477477
txt TEXT);
478478
SELECT pathman.create_range_partitions('test."RangeRel"', 'id', 1, 100, 3);
479-
SELECT pathman.drop_range_partitions('test."RangeRel"');
479+
SELECT pathman.drop_partitions('test."RangeRel"');
480480
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'id', 1, 300, 100);
481481
DROP TABLE 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
DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
525525

526526
/* Create range partitions from whole range */
527-
SELECT drop_range_partitions('range_rel');
527+
SELECT drop_partitions('range_rel');
528528
SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
529-
SELECT drop_range_partitions('range_rel', TRUE);
529+
SELECT drop_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_rel WHERE dt = '2015-12-15';
532532

0 commit comments

Comments
 (0)