Re: GSoC - proposal - Materialized Views in PostgreSQL

2010/4/9 pavelbaros <baros(dot)p(at)seznam(dot)cz>:
> Implementation:  could be divided to few steps:
>
> 1) create materialized view
> - modify grammar (parser): CREATE MATERIALIZED VIEW mv_name AS SELECT ...
> - change executor, so that it will create physical table defined by select
> statement

This basically needs to work the same was as CREATE TABLE ... AS
SELECT ... - save that it should also stuff the rewritten query
someplace, so that it can be re-executed. I think one of the
important design questions here is figuring out exactly where that
"someplace" should be.

I also suspect that we want to block any write access to the relation
except for view refreshes. IOW, INSERT, UPDATE, and DELETE on the
underlying relation should be rejected (though perhaps rewrite rules
redirecting such operations to other tables could be allowed).

> 2) change rewriter
> - usually, view is relation with defined rule and when rewriting, rule is
> fired and relation (view) is replaced by definition of view. If relation do
> not have rule, planner and executor behave to it as physical table
> (relation). In case of materialized view we want to rewrite select statement
> only in case when we refreshing MV. In other cases rewriter should skip
> rewriting and pick up physical relation. Exclude situation when other
> rewrite rules which are not related to MV definition are specified.
>
> 3) create command that takes snapshot (refresh MV)
> - modify grammar: ALTER MATERIALIZED VIEW mv_name REFRESH;
> - taking snapshot (refreshing) is similar to command "SELECT INTO ..." and I
> decided to follow the way it works. After parsing query and before
> transformation is MANUALLY created tree representation of "SELECT * INTO
> ..." with flag IntoClause->isrefresh set true, indicating it is refreshing
> materialized view. Everithing acts as it would be regular "SELECT INTO ..."
> except functions OpenIntoRel() and CloseIntoRel(). In function OpenIntoRel
> is created temp table (without catalog) and set as destination for result of
> select. In function CloseIntoRel executor swap relfilenode's of temp table
> and original table and finally delete temp table. Behavior of CloseIntoRel
> function is inspired by CLUSTER statement.

I'll have to read the code before I can comment on the rest of this in detail.

...Robert