Re: Materialized views WIP patch

Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:
> 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,

There was, in earlier versions of the patch: pg_class.relisvald.
The problem is that we needed some way to determine from the heap
that it was invalid to support UNLOGGED MVs.  Several people were
offended by my attempt to use relisvald as the primary indicator
and transfer the information from the heap state to pg_class and
relcache.  There were some pretty big technical challenges to that.
So I caved on that one and went with the pg_relation_is_scannable()
function based on the heap as reported by relcache.  That being one
of the newer parts of the patch, it is probably not as solid as the
parts which haven't changed much in the last three months.

> 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.

Good spot.  That should be easy enough to fix.

Thanks.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company