Re: Materialized views WIP patch - Mailing list pgsql-hackers

Pavel Stehule wrote:
> 2012/11/27 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

>> I would like that we have a way to refresh a Materialized View
>> by calling a stored procedure, but I don't think it should be
>> the main UI.

I agree. I saw that Oracle uses a function for that without any
statement-level support, and that would probably be easier to
implement; but it felt wrong to do it that way. I couldn't think of
any other cases where similar action is taken without statement
syntax for it.

>> The wholesale refreshing of a matview appears to me to be
>> comparable to TRUNCATE is that it's both a DDL and a DML. The
>> incremental refreshing modes we want to have later are clearly
>> DML only, either on commit refresh or incrementally on demand.

Personally, I expect the most popular update method to eventually
become a queued update. I've looked ahead far enough to see that I
want to structure the incremental updates to be controlled through
an API where changes to supporting tables produce records saying
what was done which are fed to consumers which do the updating.
Then it becomes a matter of whether that consumer performs the
related updates to the MV during commit processing of the producer,
by pulling from a queue, or by reading accumulated rows when the MV
is referenced.

But I'm getting ahead of things with such speculation...

>> I would then propose that we use ALTER MATERIALIZED VIEW as the
>> UI for the wholesale on demand refreshing operation, and UPDATE
>> MATERIALIZED VIEW as the incremental command (to come later).

Honestly, I have managed to keep myself from speculating on syntax
for incremental updates. There will be enough complexity involved
that I expect months of bikeshedding. :-/

>> So my proposal for the current feature would be:
>>
>> ALTER MATERIALIZED VIEW mv UPDATE [ CONCURRENTLY ];
>> UPDATE MATERIALIZED VIEW mv;

An ALTER MATERIALIZED VIEW option was my first thought on syntax to
do what LOAD does in the current patch. But it bothered me that I
couldn't think of any other cases where ALTER <some-object-type>
only changed the data contained within the object and had no other
impact. Are you both really comfortable with an ALTER MATERIALIZED
VIEW which has no effect other than to update the data? It seems
wrong to me.

>> The choice of keywords and syntax here hopefully clearly hint
>> the user about the locking behavior of the commands, too. And as
>> we said, the bare minimum for this patch does *not* include the
>> CONCURRENTLY option, which we still all want to have (someday).
>> :)
> 
> +1

Sure -- a CONCURRENTLY option for LMV (or AMVU) seems like one of
the next steps. I'll feel more confident about implementing that
when it appears that we have shaken the last bugs out of
CREATE/DROP INDEX CONCURRENTLY, since anything which affects those
statements will probably also matter here.

-Kevin