Re: Materialized views WIP patch

Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Sat, Feb 16, 2013 at 09:53:14AM -0800, Kevin Grittner wrote:

> I agree that making the dump fail on this account is bad.

I would argue that this is an overstatement of the issue except for
restores that use the single-transaction switch and pg_upgrade
without the hard link option.  In all other cases, one could just
issue REFRESH statements after the dump successfully completed all
the other work.  But those two cases are important enough that the
issue must be seriously considered.

>> (1)  Force mva to refresh on restore, even though it was empty
>> and not scannable on dump.  This may delay completion of the
>> restore for an extended time.  It would leave both mva and mvb
>> populated.
>
> This is reasonable.  If the user doesn't like it, he can
> presumably use an edited dump list to remove the REFRESHes.

> Overall, I recommend option 1.

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.

> None of the options will furnish the desire of every database,

Agreed.

> but the DBA can always tailor the outcome by replacing the dumped
> REFRESH statements with his own.

... or by issuing TRUNCATE or REFRESH statements before the dump to
avoid the issue.

> I'm not envisioning that MVs left invalid for the long term will
> be a typical thing, anyway.

Agreed.  I think this will be an infrequent issue caused by unusual
user actions; but it would be bound to come up occasionally.

-Kevin