Re: Materialized views WIP patch

On 21.02.2013 16:38, Kevin Grittner wrote:
> Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Greg Stark<stark(at)mit(dot)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