Re: counting algorithm for incremental matview maintenance

On Thu, May 16, 2013 at 8:33 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>> We could drive the triggering of incremental maintenance off of the
>>> dependency information which is already stored, but for performance
>>> we probably want to add a new pg_class flag to indicate that the
>>> relation is referenced by a matview definition which specifies
>>> incremental update. That would allow a fast path for skipping
>>> other tests for DML on non-referenced relations, at the expense of
>>> some additional catalog updates on some DDL.
>>
>> I'm afraid this might require creating a matview or updating the
>> definition of a matview to refer to different relations to take
>> AccessExclusiveLock on those relations, in order to avoid SnapshotNow
>> problems while updating this flag for those relations, and I think
>> that's probably unacceptable. Some thought may be needed here to come
>> up with a good solution.
>
> Thanks for the feedback.
>
> I had been thinking that such a flag would be the moral equivalent
> of such existing flags as relhaspkey, relhasrules, relhastriggers,
> and relhassubclass. Those all require owner rights to change, and
> perhaps we don't want to require that a user be the owner of a
> table to define a materialized view which references that table and
> specifies incremental update. On the other hand, we might not want
> to let just any old user who has SELECT permission on a table to
> specify that it feeds an incrementally updated matview, since there
> is no escaping the fact that extra work will be needed for DML
> against that table if it is doing that. I seem to recall that at
> least one other product requires the owner of a table to ALTER it
> to set a flag specifying that the table is allowed to be used to
> back incrementally updated matviews; perhaps that's the way to go?

Possibly. That at least has the advantage of transparency: if you do
ALTER TABLE wunk ENABLE DELTA QUEUE or somesuch syntax, it's very
clear that you're buying an AccessExclusiveLock. And while
AccessExclusiveLocks are not a lot of fun, one that you know is coming
is a lot better than one that comes as a surprise.

I feel like it would be nicer, though, to come up with some trick that
avoids the need to update the referenced table's pg_class entry
altogether. I don't immediately have a good idea, but I'll mull it
over and see if I come up with anything.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company