Commita400a91

committed

pathman: option allowing to drop data with partitions

1 parent5381eb3 commita400a91Copy full SHA for a400a91

File tree

+14

-19

lines changed

+14

-19

lines changed

Lines changed: 1 addition & 11 deletions

Original file line number	Diff line number	Diff line change
`@@ -688,17 +688,7 @@ NOTICE: Copying data to partitions...`
`688`	`688`	`10`
`689`	`689`	`(1 row)`
`690`	`690`
`691`		`-SELECT drop_range_partitions('range_rel');`
`692`		`-NOTICE: 0 rows copied from range_rel_10`
`693`		`-NOTICE: 0 rows copied from range_rel_9`
`694`		`-NOTICE: 0 rows copied from range_rel_8`
`695`		`-NOTICE: 0 rows copied from range_rel_7`
`696`		`-NOTICE: 0 rows copied from range_rel_6`
`697`		`-NOTICE: 0 rows copied from range_rel_5`
`698`		`-NOTICE: 65 rows copied from range_rel_4`
`699`		`-NOTICE: 100 rows copied from range_rel_3`
`700`		`-NOTICE: 100 rows copied from range_rel_2`
`701`		`-NOTICE: 100 rows copied from range_rel_1`
	`691`	`+SELECT drop_range_partitions('range_rel', TRUE);`
`702`	`692`	`drop_range_partitions`
`703`	`693`	`-----------------------`
`704`	`694`	`10`

Lines changed: 12 additions & 7 deletions

Original file line number	Diff line number	Diff line change
`@@ -886,8 +886,11 @@ $$ LANGUAGE plpgsql;`
`886`	`886`
`887`	`887`	`/*`
`888`	`888`	`* Drop partitions`
	`889`	`+ * If delete_data set to TRUE then partitions will be dropped with all the data`
`889`	`890`	`*/`
`890`		`-CREATEOR REPLACE FUNCTION @extschema@.drop_range_partitions(IN relationTEXT)`
	`891`	`+CREATEOR REPLACE FUNCTION @extschema@.drop_range_partitions(`
	`892`	`+ relationTEXT,`
	`893`	`+ delete_dataBOOLEAN DEFAULT FALSE)`
`891`	`894`	`RETURNSINTEGERAS`
`892`	`895`	`$$`
`893`	`896`	`DECLARE`
`@@ -904,13 +907,15 @@ BEGIN`
`904`	`907`	`FOR v_recIN (SELECT inhrelid::regclass::textAS tbl`
`905`	`908`	`FROM pg_inheritsWHERE inhparent= relation::regclass::oid)`
`906`	`909`	`LOOP`
`907`		`- EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)`
`908`		`- INSERT INTO %s SELECT * FROM part_data'`
`909`		`- ,v_rec.tbl`
`910`		`- , relation);`
`911`		`- GET DIAGNOSTICS v_rows= ROW_COUNT;`
	`910`	`+ IF NOT delete_data THEN`
	`911`	`+ EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)`
	`912`	`+ INSERT INTO %s SELECT * FROM part_data'`
	`913`	`+ ,v_rec.tbl`
	`914`	`+ , relation);`
	`915`	`+ GET DIAGNOSTICS v_rows= ROW_COUNT;`
	`916`	`+ RAISE NOTICE'% rows copied from %', v_rows,v_rec.tbl;`
	`917`	`+ END IF;`
`912`	`918`	`EXECUTE format('DROP TABLE %s',v_rec.tbl);`
`913`		`- RAISE NOTICE'% rows copied from %', v_rows,v_rec.tbl;`
`914`	`919`	`v_part_count := v_part_count+1;`
`915`	`920`	`END LOOP;`
`916`	`921`

Lines changed: 1 addition & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -175,7 +175,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';`
`175`	`175`	`/* Create range partitions from whole range*/`
`176`	`176`	`SELECT drop_range_partitions('range_rel');`
`177`	`177`	`SELECT create_partitions_from_range('range_rel','id',1,1000,100);`
`178`		`-SELECT drop_range_partitions('range_rel');`
	`178`	`+SELECT drop_range_partitions('range_rel', TRUE);`
`179`	`179`	`SELECT create_partitions_from_range('range_rel','dt','2015-01-01'::date,'2015-12-01'::date,'1 month'::interval);`
`180`	`180`	`EXPLAIN (COSTS OFF)SELECT*FROM range_relWHERE dt='2015-12-15';`
`181`	`181`

Comments

(0)