Commitb69ec7c

committed

Prevent (auto)vacuum from truncating first page of populated matview.

Per report from Fujii Masao, with regression test using his example.

1 parent095018b commitb69ec7cCopy full SHA for b69ec7c

File tree

+38

-0

lines changed

+38

-0

lines changed

Lines changed: 6 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -230,7 +230,13 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,`
`230`	`230`	`*`
`231`	`231`	`* Don't even think about it unless we have a shot at releasing a goodly`
`232`	`232`	`* number of pages. Otherwise, the time taken isn't worth it.`
	`233`	`+ *`
	`234`	`+ * Leave a populated materialized view with at least one page.`
`233`	`235`	`*/`
	`236`	`+if (onerel->rd_rel->relkind==RELKIND_MATVIEW&&`
	`237`	`+vacrelstats->nonempty_pages==0)`
	`238`	`+vacrelstats->nonempty_pages=1;`
	`239`	`+`
`234`	`240`	`possibly_freeable=vacrelstats->rel_pages-vacrelstats->nonempty_pages;`
`235`	`241`	`if (possibly_freeable>0&&`
`236`	`242`	`(possibly_freeable >=REL_TRUNCATE_MINIMUM\|\|`

Lines changed: 20 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -418,3 +418,23 @@ NOTICE: drop cascades to 3 other objects`
`418`	`418`	`DETAIL: drop cascades to view v_test2`
`419`	`419`	`drop cascades to materialized view mv_test2`
`420`	`420`	`drop cascades to materialized view mv_test3`
	`421`	`+-- test that vacuum does not make empty matview look unpopulated`
	`422`	`+CREATE TABLE hoge (i int);`
	`423`	`+INSERT INTO hoge VALUES (generate_series(1,100000));`
	`424`	`+CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge WHERE i % 2 = 0;`
	`425`	`+CREATE INDEX hogeviewidx ON hogeview (i);`
	`426`	`+DELETE FROM hoge;`
	`427`	`+REFRESH MATERIALIZED VIEW hogeview;`
	`428`	`+SELECT * FROM hogeview WHERE i < 10;`
	`429`	`+ i`
	`430`	`+---`
	`431`	`+(0 rows)`
	`432`	`+`
	`433`	`+VACUUM ANALYZE;`
	`434`	`+SELECT * FROM hogeview WHERE i < 10;`
	`435`	`+ i`
	`436`	`+---`
	`437`	`+(0 rows)`
	`438`	`+`
	`439`	`+DROP TABLE hoge CASCADE;`
	`440`	`+NOTICE: drop cascades to materialized view hogeview`

Lines changed: 12 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -129,3 +129,15 @@ CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;`
`129`	`129`	`SELECT pg_relation_is_scannable('mv_test3'::regclass);`
`130`	`130`
`131`	`131`	`DROPVIEW v_test1 CASCADE;`
	`132`	`+`
	`133`	`+-- test that vacuum does not make empty matview look unpopulated`
	`134`	`+CREATETABLEhoge (iint);`
	`135`	`+INSERT INTO hogeVALUES (generate_series(1,100000));`
	`136`	`+CREATE MATERIALIZED VIEW hogeviewASSELECT*FROM hogeWHERE i %2=0;`
	`137`	`+CREATEINDEXhogeviewidxON hogeview (i);`
	`138`	`+DELETEFROM hoge;`
	`139`	`+REFRESH MATERIALIZED VIEW hogeview;`
	`140`	`+SELECT*FROM hogeviewWHERE i<10;`
	`141`	`+VACUUM ANALYZE;`
	`142`	`+SELECT*FROM hogeviewWHERE i<10;`
	`143`	`+DROPTABLE hoge CASCADE;`

Comments

(0)