Re: GSoC - code of implementation of materialized views

On Jun 29, 2010, at 3:31 PM, Pavel Baroš wrote:

> Robert Haas napsal(a):
>> 2010/6/25 Pavel Baros <baros(dot)p(at)seznam(dot)cz>:
>>
>>>> On http://github.com/pbaros/postgres can be seen changes and my attempt to
>>>> implement materialized views. The first commit to the repository implements
>>>> following:
>>>>
>>>> Materialized view can be created, dropped and used in SELECT statement.
>>>>
>>>> CREATE MATERIALIZED VIEW mvname AS SELECT ...;
>>>> DROP MATERIALIZED VIEW mvname [CASCADE];
>>>> SELECT * FROM mvname;
>>>>
>>>> also works:
>>>> COMMENT ON MATERIALIZED VIEW mvname IS 'etc.';
>>>> SELECT pg_get_viewdef(mvname);
>>>>
>>> ... also you can look at enclosed patch.
>>>
>>
>> So, this patch doesn't actually seem to do very much. It doesn't
>> appear that creating the materialized view actually populates it with
>> any data; and the refresh command doesn't work either. So it appears
>> that you can create a "materialized view", but it won't actually
>> contain any data - which doesn't seem at all useful.
>>
>>
>
> Yeah, it is my fault, I did not mentioned that this patch is not final. It is only small part of whole implementation. I wanted to show just this, because I think that is the part that should not change much. And to show I did something, I am not ignoring GSoC. Now I can fully focus on the program.
>
> Most of the problems you mentioned (except pg_dump) I have implemented and I will post it to HACKERS soon. Until now I've not had much time, because I just finished my BSc. studies yesterday.
>
> And again, sorry for misunderstanding.
>
> Pavel Baros
>
>> Some other problems:
>>
>> - The command tag for CREATE MATERIALIZED VIEW should return CREATE
>> MATERIALIZED VIEW rather than CREATE VIEW, since we're treating it as
>> a separate object type. I note that dropping a materialized view
>> already uses DROP MATERIALIZED VIEW, so right now it isn't
>> symmetrical.
>> - Using "\d" with no argument doesn't list materialized views.
>> - Using "\d" with a materialized view as an argument doesn't work
>> properly - the first line says something like ?m? "public.m" instead
>> of materialized view "public.m".
>> - Using "\d+" with a materialized view as an argument should probably
>> should the view definition.
>> - Using "\dd" doesn't list comments on materialized views.
>> - Commenting on a column of a materialized view should probably be allowed.
>> - pg_dump fails with a message like this: failed sanity check, parent
>> table OID 24604 of pg_rewrite entry OID 24607 not found
>> - ALTER MATERIALIZED VIEW name OWNER TO role, RENAME TO role, and SET
>> SCHEMA schema either fall to work or fail to parse (plan ALTER VIEW
>> also doesn't work on a materialized view)
>> - ALTER MATERIALIZED VIEW name SET/DROP DEFAULT also doesn't work,
>> which is OK: it shouldn't work. But the error message needs work.
>> - The error message "CREATE OR REPLACE on materialized view is not
>> support!" shouldn't end with an exclamation point.

Do we see supporting the creation of a materialized view from a regular view, as in ALTER VIEW regular_view SET MATERIALIZED or some such?

Since we're treating this as a distinct object type, instead of repeatedly typing "MATERIALIZED VIEW", is there a possibility of introducing a keyword alias "MATVIEW" without complicating the grammar/code all that much, or is that frowned upon? Paintbrushes, anyone?

Regards,

David
--
David Christensen
End Point Corporation
david(at)endpoint(dot)com