Skip site navigation (1) Skip section navigation (2)

Re: Materialized views WIP patch

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Kevin Grittner <kgrittn(at)mail(dot)com>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Materialized views WIP patch
Date: 2012-11-18 11:37:58
Message-ID: CA+U5nMLcz2X_zR2nxxAF03ETW1oxp7TyO3+Vs1ZqKMfp9KZxGQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 16 November 2012 11:25, Kevin Grittner <kgrittn(at)mail(dot)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


In response to

pgsql-hackers by date

Next:From: Craig RingerDate: 2012-11-18 12:34:46
Subject: Re: Parser - Query Analyser
Previous:From: C├ędric VillemainDate: 2012-11-18 09:52:24
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group