Re: Materialized views WIP patch

Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Fri, Feb 15, 2013 at 08:24:16PM -0500, Robert Haas wrote:
>> On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>> There is one odd aspect to pg_dump, but I think the way it is
>>> behaving is the best way to handle it, although I invite other
>>> opinions.  If you load from pg_dump output, it will try to
>>> populated materialized views which were populated on dump, and
>>> leave the ones which were not scannable because the contents had
>>> not been generated in an empty and unscannable state on restore.
>>> That much seems pretty obvious.  Where it gets  a little tricky is
>>> if mva is generated with data, and mvb is generated based on mva.
>>> Then mva is truncated.  Then you dump.  mvb was populated at the
>>> time of the dump, but its contents can't be regenerated on restore
>>> because mva is not scannable.  As the patch currently stands, you
>>> get an error on the attempt to REFRESH mvb.  I think that's a good
>>> thing, but I'm open to arguments to the contrary.
>>
>> Hmm, anything that means a dump-and-restore can fail seems like a bad
>> thing to me.  There's nothing outrageous about that scenario.  It's
>> arguable what state dump-and-restore should leave the new database in,
>> but I don't see why it shouldn't work.  I predict we'll end up with
>> unhappy users if we leave it like this.
>
> pg_upgrade is going to fail on that pg_restore error.  :-(

With the hard link option it should succeed, I would think.  If we
arranged for the check option, when run without the hard link
option, to report such cases so that the user could choose to
either truncate mvb or refresh mva before the upgrade, would that
satisfy this concern?

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