Re: Materialized views WIP patch

Kevin,

> Attached is a patch that is still WIP but that I think is getting
> pretty close to completion. It is not intended to be the be-all and
> end-all for materialized views, but the minimum useful feature set --
> which is all that I've had time to do for this release. In
> particular, the view is only updated on demand by a complete rebuild.
> For the next release, I hope to build on this base to allow more
> eager and incremental updates, and perhaps a concurrent batch update.

Nice to see this come in!

> 1. CREATE MATERIALIZED VIEW syntax is stolen directly from CREATE
> TABLE AS, with all the same clauses supported. That includes
> declaring a materialized view to be temporary or unlogged.

What use would a temporary matview be?

Unlogged is good.

> 2. MVs don't support inheritance.

In which direction? Can't inherit, or can't be inherited from?

> 3. MVs can't define foreign keys.
> 4. MVs can't be the target of foreign keys.
> 5. MVs can't have triggers.

Makes sense.

> 9. MVs can't directly be used in a COPY statement, but can be the
> source of data using a SELECT.

Hmmm? I don't understand the reason for this.

> 13. pg_class now has a relisvalid column, which is true if an MV is
> truncated or created WITH NO DATA. You can not scan a relation
> flagged as invalid.

What error would a user see?

> 14. ALTER MATERIALIZED VIEW is supported for the options that seemed
> to make sense. For example, you can change the tablespace or
> schema, but you cannot add or drop column with ALTER.

How would you change the definition of an MV then?

> 16. To get new data into the MV, the command is LOAD MATERIALIZED
> VIEW mat view_name. This seemed more descriptive to me that the
> alternatives and avoids declaring any new keywords beyond
> MATERIALIZED. If the MV is flagged as relisvalid == false, this
> will change it to true.

UPDATE MATERIALIZED VIEW was problematic?

Does LOAD automatically TRUNCATE the view before reloading it? If not,
why not?

> It would be good to have some discussion to try to reach a consensus
> about whether we need to differentiate between *missing* datat (where
> a materialized view which has been loaded WITH NO DATA or TRUNCATEd
> and has not been subsequently LOADed) and potentially *stale* data.
> If we don't care to distinguish between a view which generated no
> rows when it ran and a one for which the query has not been run, we
> can avoid adding the relisvalid flag, and we could support UNLOGGED
> MVs. Perhaps someone can come up with a better solution to that
> problem.

Hmmm. I understand the distinction you're making here, but I'm not sure
it actually matters to the user. MVs, by their nature, always have
potentially stale data. Being empty (in an inaccurate way) is just one
kind of stale data.

It would be nice for the user to have some way to know that a matview is
empty due to never being LOADed or recently being TRUNCATEd. However, I
don't think that relisvalid flag -- and preventing scanning the relation
-- is a good solution. What I'd rather have instead is a timestamp of
when the MV was last LOADed. If the MV was never loaded (or was
truncated) that timestamp would be NULL. Such a timestamp would allow
users to construct all kinds of ad-hoc refresh schemes for MVs which
would not be possible without it.

I don't see how this relates to UNLOGGED matviews either way.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com