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.relkindin ('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