Re: matview scannability rehash (was Re: Drastic performance loss in assert-enabled build in HEAD)

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I realize that there's no other (easy) way to make unlogged
> matviews reset to an invalid state on crash, but that doesn't
> make this design choice less of a disaster.  It boxes us into
> something that's entirely unable to support transitions between
> scannable and unscannable states by any means short of a complete
> rewrite of the matview contents; which seems fundamentally
> incompatible with any sort of incremental update scenario.

That assertion makes absolutely no sense.  Once we design (in a
future release cycle) a way for users to declare how current a view
must be to be usable, there is no reason the
pg_relation_is_scannable() function cannot be modified to use those
mechanisms.  I continue to believe that it is a bad idea to ever
allow a matview to be scanned when it is not a materialization of
its query, but that does *not* mean that all matviews that are a
materialization of the query would need to be considered scannable.
My working assumption was that the isscannable field in relcache
would be the first of many tests once we get there; if the matview
actually does represent some materialization of data, the function
would then proceed to check whether it is current enough to use,

It does mean that you could not start incremental maintenance on a
matview without first generating a base by running the query to
create initial data (as part of CREATE or REFRESH), but that seems
like a *good* thing to me.  To do otherwise would make no more
sense than to try to recover a database using just WAL files
without a base backup to apply them to.

> And I remain of the opinion that it's going to box us into not
> being able to fix the problems because of pg_upgrade on-disk-
> compatibility issues.

This argument also seems bogus to me.  Since it is a valid heap
either way, the *worst case* would be recommending that after
upgrading users take some special action on any materialized views
which were not scannable; and I doubt that we would need to do
that.  If you see some risk I'm missing, please elaborate.

> We will be far better off to drop unlogged matviews until we can
> come up with a better design.  If that's so far off that no one
> can see it happening, well, that's tough.  Leaving the door open
> for incremental maintenance is more important.

I've been looking at what is needed for incremental maintenance,
and I'm not seeing the problem.  Since you can't incrementally
update a view which has never been populated, the only way in which
it could create a problem in terms of transactional behavior, I
think, is that this would make it harder to not hold an
AccessExclusiveLock when transitioning between not having
materialized data and having it (or vice versa), and I'm dubious we
can avoid such a lock anyway.  I don't see where it creates any
problems for performing incremental updates once we are in a state
which can allow them.

> This really needs to be catalog state, not filesystem state.

That may be true, but the arguments in this post are so off-base
that I'm wondering whether it really is.  When I read some earlier
posts I was convinced, but now I think I need to review the whole
thread again to make sure I wasn't too quick to concede the point.

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