Re: Materialized views WIP patch - Mailing list pgsql-hackers

On Fri, Mar 1, 2013 at 4:18 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Personally, I don't understand why anyone would want updateable
> materialized views.  That's probably because 99% of the cases where
> I've seen that someone wanted them, they wanted them updated to
> match the underlying data using some technique that didn't require
> the modification or commit of the underlying data to carry the
> overhead of maintaining the MV.  In other words, they do not want
> the MV to be up-to-date for performance reasons.  That is a big
> part of the reason for *wanting* to use an MV.  How do you make an
> asynchronously-maintained view updateable?
>
> In addtion, at least 80% of the cases I've seen where people want
> an MV it is summary information, which does not tie a single MV row
> to a single underlying row.  If someone updates an aggregate number
> like an average, I see no reasonable way to map that to the
> underlying data in a meaningful way.
>
> I see the contract of a materialized view as providing a
> table-backed relation which shows the result set of a query as of
> some point in time.  Perhaps it is a failure of imagination, but I
> don't see where modifying that relation directly is compatible with
> that contract.
>
> Can you describe a meaningful use cases for an udpateable
> materialized view?

I actually agree that overwhelming majority of users don't need or
want updateable materialized views. My point was that we can't at this
point rule out that people will think of a good use for this. I don't
have any real use cases for this, but I can imagine a few situations
where updateable materialized views wouldn't be nonsensical.

One case would be if the underlying data is bulkloaded and is
subsetted into smaller materialized views for processing using
off-the-shelf tools that expect tables. One might want to propagate
changes from those applications to the base data.

The other case would be the theoretical future where materialized
views can be incrementally and transactionally maintained, in that
case being able to express modifications on the views could actually
make sense.

I understand that the examples are completely hypothetical and could
be solved by using regular tables. I just have feeling that will
regret conflating TRUNCATE semantics for slight implementation and
notation convenience. To give another example of potential future
update semantics, if we were to allow users manually maintaining
materialized view contents using DML commands, one would expect
TRUNCATE to mean "make this matview empty", not "make this matview
unavailable".

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de