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

Noah Misch <noah@leadboat.com> wrote:
> On Mon, Feb 18, 2013 at 06:49:14AM -0800, Kevin Grittner wrote:
>> Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>>> Maybe it would be a good idea to try to put such commands at
>>> the very end of the dump, if possible.
>
>>     25,                            /* DO_POST_DATA_BOUNDARY */
>>     26,                            /* DO_CONSTRAINT */
>>     27,                            /* DO_INDEX */
>>     28,                            /* DO_REFRESH_MATVIEW */
>>     28                             /* DO_MATVIEW_INDEX */
>>     29,                            /* DO_RULE */
>>     30,                            /* DO_TRIGGER */
>>     31,                            /* DO_FK_CONSTRAINT */
>>     32,                            /* DO_DEFAULT_ACL */
>>     33,                            /* DO_EVENT_TRIGGER */
>>
>> I don't think that pushing MV refreshes and index creation
>> farther down the list should require anything beyond adjusting
>> the priority numbers.  I don't see a problem pushing them to the
>> end.  Does anyone else see anything past priority 28 that MV
>> population should *not* follow?
>
> DO_EVENT_TRIGGER should remain last; it may change the behavior
> of nearly any other command.
>
> Moving DO_REFRESH_MATVIEW past DO_TRIGGER would affect the
> outcome when the MV calls functions that ultimately trip triggers
> or rules. Currently, the behavior will be the same as for CHECK
> constraints: the rules and triggers don't exist yet.  This may
> also affect, for the better, MVs referencing views that need the
> CREATE TABLE ... CREATE RULE _RETURN restoration pathway.  It
> looks like a positive change.  On the flip side, I wonder if
> there's some case I'm not considering where it's important to
> delay restoring rules and/or triggers until after restoring
> objects for which restoration can entail calls to arbitrary user
> functions.

I didn't quite follow all of Noah's points or their implications,
so we chatted off-list.  He made a couple additional observations
which allow some simplification of the patch, and allow MV REFRESH
to be moved to the very end of the priority list without ill
effect.

(1)  While it might be incorrect for the CREATE INDEX on a
materialized view to come after event triggers are set up, REFRESH
can be expected to be a routine action in the presence of such
triggers, and it might actually be incorrect to REFRESH when the
triggers are not present.

(2)  REFRESH MATERIALIZED VIEW creates and builds a new heap, and
reindexes it after the data has been loaded, so the timing of the
CREATE INDEX statements on MVs is not critical, as long as they are
done after the CREATE and before the REFRESH.  We could drop them
into the same priority as the other CREATE INDEX statements, and it
would not be a big deal because the MVs would be empty.

This should allow me to simplify the code a little bit and move the
RMV step to the very end.  That may have some advantages when users
want to start using the database while MVs are being populated.

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