From b69ec7cc990fd8da75ed4c232899503217d7b9ae Mon Sep 17 00:00:00 2001 From: Kevin Grittner Date: Thu, 2 May 2013 17:33:03 -0500 Subject: [PATCH] Prevent (auto)vacuum from truncating first page of populated matview. Per report from Fujii Masao, with regression test using his example. --- src/backend/commands/vacuumlazy.c | 6 ++++++ src/test/regress/expected/matview.out | 20 ++++++++++++++++++++ src/test/regress/sql/matview.sql | 12 ++++++++++++ 3 files changed, 38 insertions(+) diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index 9d304153b8..02f3cf3c20 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -230,7 +230,13 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, * * Don't even think about it unless we have a shot at releasing a goodly * number of pages. Otherwise, the time taken isn't worth it. + * + * Leave a populated materialized view with at least one page. */ + if (onerel->rd_rel->relkind == RELKIND_MATVIEW && + vacrelstats->nonempty_pages == 0) + vacrelstats->nonempty_pages = 1; + possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; if (possibly_freeable > 0 && (possibly_freeable >= REL_TRUNCATE_MINIMUM || diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 5a53f84c5b..e87775679a 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -418,3 +418,23 @@ NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to view v_test2 drop cascades to materialized view mv_test2 drop cascades to materialized view mv_test3 +-- test that vacuum does not make empty matview look unpopulated +CREATE TABLE hoge (i int); +INSERT INTO hoge VALUES (generate_series(1,100000)); +CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge WHERE i % 2 = 0; +CREATE INDEX hogeviewidx ON hogeview (i); +DELETE FROM hoge; +REFRESH MATERIALIZED VIEW hogeview; +SELECT * FROM hogeview WHERE i < 10; + i +--- +(0 rows) + +VACUUM ANALYZE; +SELECT * FROM hogeview WHERE i < 10; + i +--- +(0 rows) + +DROP TABLE hoge CASCADE; +NOTICE: drop cascades to materialized view hogeview diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 08b48188c3..9fbaafac6d 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -129,3 +129,15 @@ CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345; SELECT pg_relation_is_scannable('mv_test3'::regclass); DROP VIEW v_test1 CASCADE; + +-- test that vacuum does not make empty matview look unpopulated +CREATE TABLE hoge (i int); +INSERT INTO hoge VALUES (generate_series(1,100000)); +CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge WHERE i % 2 = 0; +CREATE INDEX hogeviewidx ON hogeview (i); +DELETE FROM hoge; +REFRESH MATERIALIZED VIEW hogeview; +SELECT * FROM hogeview WHERE i < 10; +VACUUM ANALYZE; +SELECT * FROM hogeview WHERE i < 10; +DROP TABLE hoge CASCADE; -- 2.11.4.GIT