Re: Materialized views WIP patch

Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

>> [ ... ] led to this thought:
>>
>> REFRESH MATERIALIZED VIEW name [, ...] WITH [ NO ] DATA
>
> [Sorry to join this discussion so late]
>
> FWIW I had a quick look at other DBs to see if there were any
> other precedents out there. Oracle was the only one I could find
> with anything similar. They use the same creation syntax:
>
>   CREATE MATERIALIZED VIEW name [options] AS SELECT ...

It is a pretty obvious choice when you look at other SQL
statements.

> and they use ALTER for everything else, such as refreshing the
> MV:
>
>   ALTER MATERIALIZED VIEW name REFRESH [options];

No, that is for specifiying when and under what conditions an
automatic refresh is done.  To do an immediate action which is
equivalent to what I have for the REFRESH statement, they use a
REFRESH() function.  That seemed too incompatible with how we've
done everything else in PostgreSQL -- I felt that a statement would
make more sense.  Consider REINDEX, CLUSTER, and VACUUM FULL for
example.

> AFAICT the nearest thing they have to TRUNCATE/DISCARD is:
>
>   ALTER MATERIALIZED VIEW name CONSIDER FRESH;

No, that doesn't rebuild or discard data -- if the MV is
out-of-date and therefore unscannable according to the how the MV
has been set up, this overrides that indication and allows scanning
in spite of that.

> They do also support updateable materialized views with standard
> DML, but it doesn't look as though they allow TRUNCATE to operate
> directly on a MV (although it can be made to propagate from a
> base table to a MV, in which case allowing TRUNCATE on the MV
> itself with a different meaning would likely be confusing).

They allow DML on the MV in order to update it.  The default
REFRESH() function executes a TRUNCATE statement followed by INSERT
/ SELECT using the MV's query.

> Oracle's MVs have lots of options, all of which hang off the 2
> basic CREATE and ALTER commands. There's a certain appeal to
> that, rather than inventing or overloading a bunch of other
> commands as more options get added. The proposed REFRESH command
> is OK for today's options, but I think it might be overly
> limiting in the future.

For what ALTER MATERIALIZED VIEW in Oracle does, I think it makes
sense to use ALTER.  I don't think this feature should use
functions for REFRESH.  Why Oracle can get away with functions for
it is that they allow DML on an MV, which seems to me to compromise
the integrity of the feature, at least as default behavior.

I see us supporting automatic incremental updates of progressively
more complex queries, and we may at some point want to add a
trigger-based maintenance option; but the functionality available
with a trigger-based approach is almost entirely availaable in
PostgreSQL today without this feature.  Rewriting queries using
expressions which match the MV's query to pull from the MV instead
of the underlying tables is the exception.  While that is a "sexy"
feature, and I'm sure one can construct examples where it helps
performance, it seems to me unlikely to be very generally useful.
I suspect that it exists mostly so that people who want to write an
RFP to pick a particular product can include that as a requirement.
 In other words, I think the main benefit of automatic rewrite
using an MV is marketing, not technical or performance.  That's
important, too; but let's focus first on getting what is most
useful.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company