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

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.

test=# CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
CREATE TABLE
test=# CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
SELECT 0
test=# SELECT pg_relation_is_scannable('tm'::regclass);
 pg_relation_is_scannable
--------------------------
 f
(1 row)

test=# SELECT * FROM tm;
ERROR:  materialized view "tm" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
test=# REFRESH MATERIALIZED VIEW tm;
REFRESH MATERIALIZED VIEW
test=# SELECT pg_relation_is_scannable('tm'::regclass);
 pg_relation_is_scannable
--------------------------
 t
(1 row)

test=# TRUNCATE MATERIALIZED VIEW tm;
TRUNCATE TABLE
test=# SELECT * FROM tm;
ERROR:  materialized view "tm" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
test=# SELECT pg_relation_is_scannable('tm'::regclass);
 pg_relation_is_scannable
--------------------------
 f
(1 row)

> That being the case, lumping them as being the "same" operation
> feels like the wrong thing, and so we should choose a different
> name for the MV operation.

There is currently no truncation of MV data without rendering the
MV unscannable.  Do you still feel it needs a different command
name?

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