From: Pavel Baros on
> 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.
From: Simon Riggs on
On Fri, 2010-06-25 at 20:24 +0200, Pavel Baros wrote:

> ... also you can look at enclosed patch.

No tests == no patch

Always best to work on the tests first, so everybody can see the syntax
you are proposing, and also see if your patch actually works. Otherwise
you may find people disagree and then you are faced with extensive
rework.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services


--
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: David Fetter on
On Sun, Jun 27, 2010 at 12:52:17PM +0100, Simon Riggs wrote:
> On Fri, 2010-06-25 at 20:24 +0200, Pavel Baros wrote:
>
> > ... also you can look at enclosed patch.
>
> No tests == no patch

This isn't quite how I'd have phrased it, and it would be nice if
nobody phrased advice quite this way. :)

In order for a patch to be accepted, it needs to include both SGML
docs if it changes user-visible behavior, and tests for any new
behaviors it has created. This is the project standard, and it or
something very like it is a good standard for just about any project,
as it gives people some ways to test intent vs. effect.

Do you want some help with creating same?

Cheers,
David.
--
David Fetter <david(a)fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter(a)gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

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.

--
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
2010/6/29 David Christensen <david(a)endpoint.com>:
> 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?

I'm not sure. I think we should focus our efforts on (1) getting it
to work at all and then (2) improving the performance of the refresh
operation, which will doubtless be pessimal in the initial
implementation. Those are big enough problems that I'm not inclined
to spend much thought on bells and whistles at this point.

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

-1 from me, but IJWH.

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

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