Re: Materialized views WIP patch

Thanks for looking at this!

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.

> I'm going to follow this with a review covering a broader range
> of topics.

I'll need time to digest the rest of it. As you note, recent
commits conflict with the last patch. Please look at the github
repo where I've been working on this. I'll post an updated patch
later today.

https://github.com/kgrittn/postgres/tree/matview

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
(
select
   d1.objid,
   d1.objid as wrkid,
   d2.refobjid,
   c2.relkind as refrelkind
 from pg_depend d1
 join pg_class c1 on c1.oid = d1.objid
                 and c1.relkind = 'm'
                 and c1.relisvalid
 join pg_rewrite r1 on r1.ev_class = d1.objid
 join pg_depend d2 on d2.classid = 'pg_rewrite'::regclass
                  and d2.objid = r1.oid
                  and d2.refobjid <> d1.objid
 join pg_class c2 on c2.oid = d2.refobjid
                 and c2.relkind in ('m','v')
                 and c2.relisvalid
 where d1.classid = 'pg_class'::regclass
union
select
   w.objid,
   w.refobjid as wrkid,
   d3.refobjid,
   c3.relkind as refrelkind
 from w
 join pg_rewrite r3 on r3.ev_class = w.refobjid
 join pg_depend d3 on d3.classid = 'pg_rewrite'::regclass
                  and d3.objid = r3.oid
                  and d3.refobjid <> w.refobjid
 join pg_class c3 on c3.oid = d3.refobjid
                 and c3.relkind in ('m','v')
                 and c3.relisvalid
 where w.refrelkind <> 'm'
),
x as
(
select objid::regclass, refobjid::regclass from w
 where refrelkind = 'm'
)
select 'm'::text as type, x.objid, x.refobjid from x
union all
select
   'i'::text as type,
   x.objid,
   i.indexrelid as refobjid
 from x
 join pg_index i on i.indrelid = x.refobjid
                and i.indisvalid
;

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

-Kevin