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

From Simon Riggs
Subject Re: Materialized views WIP patch
Date
Msg-id CA+U5nMLcz2X_zR2nxxAF03ETW1oxp7TyO3+Vs1ZqKMfp9KZxGQ@mail.gmail.com
Whole thread Raw
In response to Re: Materialized views WIP patch  ("Kevin Grittner" <kgrittn@mail.com>)
List pgsql-hackers
On 16 November 2012 11:25, Kevin Grittner <kgrittn@mail.com> wrote:

>>> 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?
>
> Not technically, really, but I saw two reasons that I preferred LOAD MV:
>
> 1. It seems to me to better convey that the entire contents of the MV
>    will be built from scratch, rather than incrementally adjusted.
> 2. We haven't hashed out the syntax for more aggressive maintenance of
>    an MV, and it seemed like UPDATE MV might be syntax we would want to
>    use for something which updated selected parts of an MV when we do.
>
>> Does LOAD automatically TRUNCATE the view before reloading it? If not,
>> why not?
>
> It builds a new heap and moves it into place. When the transaction
> running LMV commits, the old heap is deleted. In implementation it is
> closer to CLUSTER or the new VACUUM FULL than TRUNCATE followed by
> creating a new table. This allows all permissions, etc., to stay in
> place.

This seems very similar to the REPLACE command we discussed earlier,
except this is restricted to Mat Views.

If we're going to have this, I would prefer a whole command.

e.g. REPLACE matviewname REFRESH

that would also allow

REPLACE tablename AS query

Same thing under the covers, just more widely applicable and thus more useful.



Either way, I don't much like overloading the use of LOAD, which
already has a very different meaning.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services



pgsql-hackers by date:

Previous
From: Cédric Villemain
Date:
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL
Next
From: Craig Ringer
Date:
Subject: Re: Parser - Query Analyser