Re: Materialized views WIP patch

On 21.02.2013 16:38, Kevin Grittner wrote:
> Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>> Greg Stark<stark@mit.edu>  writes:
>>> The way I was thinking about it, whatever the command is named, you
>>> might be able to tell the database to drop the storage associated with
>>> the view but that would make the view invalid until it was refreshed.
>>> It wouldn't make it appear to be empty.
>>
>> Actually, that seems like a pretty key point to me.  TRUNCATE TABLE
>> results in a table that is perfectly valid, you just deleted all the
>> rows that used to be in it.  Throwing away an MV's contents should
>> not result in an MV that is considered valid.
>
> It doesn't.  That was one of the more contentious points in the
> earlier bikeshedding phases.  Some felt that throwing away the
> contents was a form of making the MV "out of date" and as such
> didn't require any special handling.  Others, including myself,
> felt that "data not present" was a distinct state from "generated
> zero rows" and that attempting to scan a materialized view for
> which data had not been generated must result in an error.  The
> latter property has been maintained from the beginning -- or at
> least that has been the intent.

Yeah, "data not present" is clearly different from "0 rows". I'm 
surprised there isn't an explicit boolean column somewhere for that, but 
I guess you can use the size of the heap for that too, as long as you're 
careful to not truncate it to 0 blocks when it's empty but scannable.

There's at least one bug left in that area:

postgres=# create table t (id int4);
CREATE TABLE
postgres=# create materialized view tm as select * from t where id < 
0;SELECT 0
postgres=# select * from tm; id
----
(0 rows)

postgres=# create index i_tm on tm(id);CREATE INDEX
postgres=# cluster tm using i_tm;
CLUSTER
postgres=# select * from tm;
ERROR:  materialized view "tm" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

Clustering a materialized view invalidates it.

- Heikki