Re: Materialized views WIP patch

On Thu, Jan 24, 2013 at 01:29:10PM -0500, Kevin Grittner wrote:
> Noah Misch wrote:
> > For the benefit of the archives, I note that we almost need not truncate an
> > unlogged materialized view during crash recovery. MVs are refreshed in a
> > VACUUM FULL-like manner: fill a new relfilenode, fsync it, and point the MV's
> > pg_class to that relfilenode. When a crash occurs with no refresh in flight,
> > the latest refresh had been safely synced. When a crash cuts short a refresh,
> > the pg_class update will not stick, and the durability of the old heap is not
> > in doubt. However, non-btree index builds don't have the same property; we
> > would need to force an immediate sync of the indexes to be safe here. It
> > would remain necessary to truncate unlogged MVs when recovering a base backup,
> > which may contain a partially-written refresh that did eventually commit.
> > Future MV variants that modify the MV in place would also need the usual
> > truncate on crash.
>
> Hmm. That's a very good observation. Perhaps the issue can be
> punted to a future release where we start adding more incremental
> updates to them. I'll think on that, but on the face of it, it
> sounds like the best choice.

That situation is challenging for the same reason pg_class.relisvalid was hard
to implement for unlogged relations. The startup process doesn't know the
relkind of the unlogged-relation relfilenodes it cleans. If you can work
through all that, it's certainly a nice endpoint to not lose unlogged snapshot
MVs on crash. But I intended the first half of my message as the
recommendation and the above as a wish for the future.

> You might want to ignore the interim work on detecting the new
> pg_dump dependencies through walking the internal structures. I
> decided that was heading in a direction which might be
> unnecessarily fragile and slow; so I tried writing it as a query
> against the system tables. I'm pretty happy with the results.
> Here's the query:
>
> with recursive w as
[snip]

Why is the dependency problem of ordering MV refreshes and MV index builds so
different from existing pg_dump dependency problems?

> If we bail on having pg_class.relisvalid, then it will obviously
> need adjustment.

Even if we don't have the column, we can have the fact of an MV's validity
SQL-visible in some other way.