Re: Materialized views WIP patch

On 3 March 2013 13:12, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> 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.
>

Ah, OK I see.

I misunderstood what the Oracle docs were saying. ALTER only changes
the MV's definition, whereas their REFRESH() function and your REFRESH
statement updates the data in the MV. That makes much more sense.

Regards,
Dean