Re: counting algorithm for incremental matview maintenance

On Tue, May 14, 2013 at 2:52 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> In surveying the literature on $subject, I find that most of the
> theoretical work related to how to incrementally update
> materialized views based on the matview declaration was published
> between 1988 and 1993. The best paper I have been able to find on
> the topic was published in ACM SIGMOD in 1993[1], and covers two
> algorithms: counting and DRed. The former should be very fast for
> non-recursive views, but not very efficient for recursive views.
> The latter algorithm is the other way around -- it looks like it
> will do well with recursive views but generally be slower for
> non-recursive views.
>
> It does not seem feasible to me to implement both techniques in a
> single one-year PostgreSQL release. In fact, if we have trouble
> getting everyone onto the same page early, we might have to settle
> for trying to just get some infrastructure into place, without
> anything to actually make use of it. That would be unfortunate,
> since Oracle added incremental maintenance of matviews to their
> existing feature in 1999, and have been improving it regularly
> since then. Many other products also have mature implementations
> of this, and there seems to be a lot of demand for it in
> PostgreSQL. In the best of circumstances it will take years for us
> to catch up on this front.

#1 issue I have with current matview functionality is locking.
currently refresh takes out an access exclusive lock. so, question
is, do you think your proposal will be such that it will no longer
require taking out full lock for refresh purposes (either incremental
or otherwise)?

merlin