Prev: pgsql: Fix log_temp_files docs andcomments to say bytes not kilobytes.
Next: Admission Control
From: David Christensen on 29 Jun 2010 17:28

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

> Robert Haas napsal(a):
>> 2010/6/25 Pavel Baros <baros.p(a)seznam.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(a)endpoint.com





--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Nicolas Barbier on 30 Jun 2010 03:06
2010/6/30 Robert Haas <robertmhaas(a)gmail.com>:

> By the way, does the SQL standard say anything about materialized views?

AFAIK, nope. Probably for the same reason that indexes are not
mentioned by the standard: both are only performance enhancements, and
one could easily imagine future SQL database systems that manage their
creation and removal automatically (based on usage patterns or
available disk space or somesuch).

Nicolas

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: =?windows-1252?Q?Pavel_Baro=9A?= on 29 Jun 2010 16:31
Robert Haas napsal(a):
> 2010/6/25 Pavel Baros <baros.p(a)seznam.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.
> - The parser token OptMater should probably be called OptMaterialized
> or opt_materialized, rather than abbreviating.
> - There are no docs.
> - There are no tests.
>
>


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 8 Jul 2010 06:33
2010/6/29 Pavel Baroš <baros.p(a)seznam.cz>:
> 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.

Any update on this?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 8 Jul 2010 09:22
On Thu, Jul 8, 2010 at 9:09 AM, Pavel <baros.p(a)seznam.cz> wrote:
> Any update on this?
>
> Sure, sorry for delay, I updated code on http://github.com/pbaros/postgres
> just a few minutes ago. Today I'll post patch here on HACKERS with my
> comments.

It's a little hard for me to understand what's going on via the git
repo, but it looks like you've introduced a bunch of spurious
whitespace changes in OpenIntoRel. Don't let it delay you from
posting the patch, but do please clean them up as soon as you get a
chance.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: pgsql: Fix log_temp_files docs andcomments to say bytes not kilobytes.
Next: Admission Control