Re: Materialized views WIP patch

Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Kevin Grittner escribió:
>
>> I'm OK with that approach, and in the absence of anyone pushing
>> for another direction, will make that change to pg_dump.  I'm
>> thinking I would only do this for materialized views which were
>> not scannable, but which cause REFRESH failures on other
>> materialized views if not refreshed first (recursively
>> evaluated), rather than just automatically refreshing all MVs on
>> restore.  The reason this seems important is that some MVs may
>> take a long time to refresh, and a user might want a
>> dump/restore to get to a point where they can use the rest of
>> the database while building the contents of some MVs in the
>> background or during off hours.
>
> Maybe it would be a good idea to try to put such commands at the
> very end of the dump, if possible.

Here is the dump order as currently implemented in that patch.  MVs
are created at the same priority as tables and views.  MV REFRESH
and MV index builds obviously need to follow population of table
data. These are at the same priority because it makes the most
sense to populated an MV without any indexes and then build them
before the MV is used to populate some other MV.  Dependency
information is used to get that to sort properly within the
priority level.

    1,                            /* DO_NAMESPACE */
    2,                            /* DO_PROCLANG */
    3,                            /* DO_COLLATION */
    4,                            /* DO_EXTENSION */
    5,                            /* DO_TYPE */
    5,                            /* DO_SHELL_TYPE */
    6,                            /* DO_FUNC */
    7,                            /* DO_AGG */
    8,                            /* DO_OPERATOR */
    9,                            /* DO_OPCLASS */
    9,                            /* DO_OPFAMILY */
    10,                            /* DO_CAST */
    11,                            /* DO_CONVERSION */
    12,                            /* DO_TSPARSER */
    13,                            /* DO_TSTEMPLATE */
    14,                            /* DO_TSDICT */
    15,                            /* DO_TSCONFIG */
    16,                            /* DO_FDW */
    17,                            /* DO_FOREIGN_SERVER */
    18,                            /* DO_TABLE */
    19,                            /* DO_DUMMY_TYPE */
    20,                            /* DO_ATTRDEF */
    21,                            /* DO_BLOB */
    22,                            /* DO_PRE_DATA_BOUNDARY */
    23,                            /* DO_TABLE_DATA */
    24,                            /* DO_BLOB_DATA */
    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?

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