Ants Aasma <ants@cybertec.at> wrote:
> Kevin Grittner <kgrittn@ymail.com> wrote:
>> Barring a sudden confluence of opinion, I will go with TRUNCATE
>> for the initial spelling. I tend to favor that spelling for
>> several reasons. One was the size of the patch needed to add
>> the opposite of REFRESH to the backend code:
>
> FWIW, I found Andres's point about closing the door on updatable
> views quite convincing. If at any point we want to add updatable
> materialized views, it seems like a bad inconsistency to have
> TRUNCATE mean something completely different from DELETE. While
> update capability for materialized views might not be a common
> use case, I don't think it's fair to completely shut the door on
> it to have easier implementation and shorter syntax. Especially
> as the shorter syntax is semantically inconsistent - normal
> truncate removes the data, materialized view just makes the data
> inaccessible until the next refresh.
>
> Sorry for weighing in late, but it seemed to me that this point
> didn't get enough consideration.
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?
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
