Re: [HACKERS] GSoC - code of implementation of materialized views
2010/6/29 Pavel Baroš baro...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
On Thu, Jul 8, 2010 at 9:09 AM, Pavel baro...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
On Thu, Jul 8, 2010 at 9:22 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 8, 2010 at 9:09 AM, Pavel baro...@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. Never mind... I see what you did. It's fine. /me blushes -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
Dne 8.7.2010 12:33, Robert Haas napsal(a): 2010/6/29 Pavel Barošbaro...@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? 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. Pavel Baros
Re: [HACKERS] GSoC - code of implementation of materialized views
2010/6/30 Robert Haas robertmh...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
2010/6/25 Pavel Baros baro...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
Robert Haas napsal(a): 2010/6/25 Pavel Baros baro...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 baro...@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 da...@endpoint.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
2010/6/29 David Christensen da...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GSoC - code of implementation of materialized views
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, I would like to ask for advise if there are rules about specifying keyword is reserved or unreserved. How I recognize new keywords MATERIALIZED and REFRESH should be reserved or not. thanks Pavel Baros -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC - code of implementation of materialized views
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.
*** ./src/backend/access/common/reloptions.c.orig 2010-06-23 16:31:24.0 +0200
--- ./src/backend/access/common/reloptions.c 2010-06-25 13:51:58.0 +0200
***
*** 775,780
--- 775,781
switch (classForm-relkind)
{
case RELKIND_RELATION:
+ case RELKIND_MATVIEW:
case RELKIND_TOASTVALUE:
case RELKIND_UNCATALOGED:
options = heap_reloptions(classForm-relkind, datum, false);
***
*** 1172,1177
--- 1173,1179
}
return (bytea *) rdopts;
case RELKIND_RELATION:
+ case RELKIND_MATVIEW:
return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP);
default:
/* sequences, composite types and views are not supported */
*** ./src/backend/access/heap/heapam.c.orig 2010-06-23 16:31:24.0 +0200
--- ./src/backend/access/heap/heapam.c 2010-06-25 13:52:55.0 +0200
***
*** 1877,1883
* Note: below this point, heaptup is the data we actually intend to store
* into the relation; tup is the caller's original untoasted data.
*/
! if (relation-rd_rel-relkind != RELKIND_RELATION)
{
/* toast table entries should never be recursively toasted */
Assert(!HeapTupleHasExternal(tup));
--- 1877,1884
* Note: below this point, heaptup is the data we actually intend to store
* into the relation; tup is the caller's original untoasted data.
*/
! if (relation-rd_rel-relkind != RELKIND_RELATION
! relation-rd_rel-relkind != RELKIND_MATVIEW)
{
/* toast table entries should never be recursively toasted */
Assert(!HeapTupleHasExternal(tup));
*** ./src/backend/catalog/dependency.c.orig 2010-06-23 16:31:25.0 +0200
--- ./src/backend/catalog/dependency.c 2010-06-25 13:53:46.0 +0200
***
*** 2731,2736
--- 2731,2740
appendStringInfo(buffer, _(view %s),
relname);
break;
+ case RELKIND_MATVIEW:
+ appendStringInfo(buffer, _(materialized view %s),
+ relname);
+ break;
case RELKIND_COMPOSITE_TYPE:
appendStringInfo(buffer, _(composite type %s),
relname);
*** ./src/backend/catalog/heap.c.orig 2010-06-23 16:31:25.0 +0200
--- ./src/backend/catalog/heap.c 2010-06-25 13:54:25.0 +0200
***
*** 758,763
--- 758,764
case RELKIND_RELATION:
case RELKIND_INDEX:
case RELKIND_TOASTVALUE:
+ case RELKIND_MATVIEW:
/* The relation is real, but as yet empty */
new_rel_reltup-relpages = 0;
new_rel_reltup-reltuples = 0;
***
*** 776,782
/* Initialize relfrozenxid */
if (relkind == RELKIND_RELATION ||
! relkind == RELKIND_TOASTVALUE)
{
/*
* Initialize to the minimum XID that could put tuples in the table.
--- 777,784
/* Initialize relfrozenxid */
if (relkind == RELKIND_RELATION ||
! relkind == RELKIND_TOASTVALUE ||
! relkind == RELKIND_MATVIEW)
{
/*
* Initialize to the minimum XID that could put tuples in the table.
***
*** 1027,1032
--- 1029,1035
*/
if (IsUnderPostmaster (relkind == RELKIND_RELATION ||
relkind == RELKIND_VIEW ||
+ relkind == RELKIND_MATVIEW ||
relkind == RELKIND_COMPOSITE_TYPE))
new_array_oid = AssignTypeArrayOid();
*** ./src/backend/catalog/system_views.sql.orig 2010-06-23 16:31:25.0 +0200
--- ./src/backend/catalog/system_views.sql 2010-06-25 13:55:24.0 +0200
***
*** 76,82
pg_get_userbyid(C.relowner) AS viewowner,
pg_get_viewdef(C.oid) AS definition
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
! WHERE C.relkind = 'v';
CREATE VIEW pg_tables AS
SELECT
--- 76,82
pg_get_userbyid(C.relowner) AS viewowner,
pg_get_viewdef(C.oid) AS definition
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
! WHERE C.relkind = 'v' OR C.relkind = 'm';
CREATE VIEW pg_tables AS
SELECT
*** ./src/backend/commands/comment.c.orig 2010-06-23 16:31:25.0 +0200
--- ./src/backend/commands/comment.c 2010-06-25 13:58:10.0 +0200
***
*** 107,112
--- 107,113
case OBJECT_SEQUENCE:
case OBJECT_TABLE:
case OBJECT_VIEW:
+ case OBJECT_MATVIEW:
CommentRelation(stmt-objtype, stmt-objname, stmt-comment);
break;
case OBJECT_COLUMN:
***
*** 580,585
--- 581,593
errmsg(\%s\ is not a view,
