Andres Freund <and...@2ndquadrant.com> wrote:
> On 2013-04-30 07:33:05 -0700, Kevin Grittner wrote:
>> Andres Freund <and...@2ndquadrant.com> wrote:
>>> 1) vacuum can truncate the table to an empty relation already
>>> if there is no data returned by the view's query which then
>>> leads to the wrong scannability state.
>>> So we need to make vacuum skip cleaning out the last page.
>>> Once we get incrementally updated matviews there are more
>>> situations to get into this than just a query not returning
>>> anything.
>> Yeah, I posted a short patch earlier on this thread that fixes
>> that. Nobody has commented on it, and so far I have not
>> committed anything related to this without posting details and
>> giving ample opportunity for anyone to comment. If nobody
>> objects, I can push that, and this issue is gone.
>
> Well, this bug is gone, but the multiple layer violations aren't.
Attached is a patch with regression test if we don't obviate the
need for it by tracking the populated status in a different way or
allowing unpopulated matviews to be used in queries. I'll hold off
on pushing it until we decide.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
*** a/src/backend/commands/vacuumlazy.c
--- b/src/backend/commands/vacuumlazy.c
***************
*** 230,236 **** lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
--- 230,242 ----
*
* 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 ||
*** a/src/test/regress/expected/matview.out
--- b/src/test/regress/expected/matview.out
***************
*** 430,432 **** SELECT * FROM matview_unit_false;
--- 430,452 ----
(0 rows)
DROP MATERIALIZED VIEW matview_unit_false;
+ -- 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
*** a/src/test/regress/sql/matview.sql
--- b/src/test/regress/sql/matview.sql
***************
*** 136,138 **** SELECT * FROM matview_unit_false;
--- 136,150 ----
REFRESH MATERIALIZED VIEW matview_unit_false;
SELECT * FROM matview_unit_false;
DROP MATERIALIZED VIEW matview_unit_false;
+
+ -- 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
