Re: GSoC - code of implementation of materialized views
От | Pavel Baros |
---|---|
Тема | Re: GSoC - code of implementation of materialized views |
Дата | |
Msg-id | i02s8v$1l47$1@news.hub.org обсуждение исходный текст |
Ответ на | GSoC - code of implementation of materialized views (Pavel Baros <baros.p@seznam.cz>) |
Ответы |
Re: GSoC - code of implementation of materialized views
(Simon Riggs <simon@2ndQuadrant.com>)
Re: GSoC - code of implementation of materialized views (Robert Haas <robertmhaas@gmail.com>) |
Список | pgsql-hackers |
> 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.000000000 +0200 --- ./src/backend/access/common/reloptions.c 2010-06-25 13:51:58.000000000 +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.000000000 +0200 --- ./src/backend/access/heap/heapam.c 2010-06-25 13:52:55.000000000 +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 recursivelytoasted */ 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 recursivelytoasted */ Assert(!HeapTupleHasExternal(tup)); *** ./src/backend/catalog/dependency.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/catalog/dependency.c 2010-06-25 13:53:46.000000000 +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.000000000 +0200 --- ./src/backend/catalog/heap.c 2010-06-25 13:54:25.000000000 +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 tuplesin 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 inthe 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.000000000 +0200 --- ./src/backend/catalog/system_views.sql 2010-06-25 13:55:24.000000000 +0200 *************** *** 76,82 **** pg_get_userbyid(C.relowner) AS viewowner, pg_get_viewdef(C.oid) AS definition FROM pg_classC 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_classC 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.000000000 +0200 --- ./src/backend/commands/comment.c 2010-06-25 13:58:10.000000000 +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", RelationGetRelationName(relation)))); break; + case OBJECT_MATVIEW: + if (relation->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(relation)))); + break; } /* Create the comment using the relation's oid */ *** ./src/backend/commands/copy.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/commands/copy.c 2010-06-25 14:01:28.000000000 +0200 *************** *** 1227,1233 **** if (cstate->rel) { ! if (cstate->rel->rd_rel->relkind != RELKIND_RELATION) { if (cstate->rel->rd_rel->relkind ==RELKIND_VIEW) ereport(ERROR, --- 1227,1233 ---- if (cstate->rel) { ! if (cstate->rel->rd_rel->relkind != RELKIND_RELATION && cstate->rel->rd_rel->relkind != RELKIND_MATVIEW) { if (cstate->rel->rd_rel->relkind == RELKIND_VIEW) ereport(ERROR, *************** *** 1701,1707 **** Assert(cstate->rel); ! if (cstate->rel->rd_rel->relkind != RELKIND_RELATION) { if (cstate->rel->rd_rel->relkind == RELKIND_VIEW) ereport(ERROR, --- 1701,1707 ---- Assert(cstate->rel); ! if (cstate->rel->rd_rel->relkind != RELKIND_RELATION && cstate->rel->rd_rel->relkind != RELKIND_MATVIEW) { if (cstate->rel->rd_rel->relkind == RELKIND_VIEW) ereport(ERROR, *** ./src/backend/commands/indexcmds.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/commands/indexcmds.c 2010-06-25 14:03:39.000000000 +0200 *************** *** 181,186 **** --- 181,187 ---- /* Note: during bootstrap may see uncataloged relation */ if (rel->rd_rel->relkind != RELKIND_RELATION&& + rel->rd_rel->relkind != RELKIND_MATVIEW && rel->rd_rel->relkind != RELKIND_UNCATALOGED) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), *** ./src/backend/commands/tablecmds.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/commands/tablecmds.c 2010-06-25 14:04:35.000000000 +0200 *************** *** 205,210 **** --- 205,216 ---- gettext_noop("view \"%s\" does not exist, skipping"), gettext_noop("\"%s\" is not a view"), gettext_noop("Use DROP VIEW to remove a view.")}, + {RELKIND_MATVIEW, + ERRCODE_UNDEFINED_TABLE, + gettext_noop("materialized view \"%s\" does not exist"), + gettext_noop("materialized view \"%s\" does not exist, skipping"), + gettext_noop("\"%s\" is not a materialized view"), + gettext_noop("Use DROP MATERIALIZED VIEW to remove a materialized view.")}, {RELKIND_INDEX, ERRCODE_UNDEFINED_OBJECT, gettext_noop("index \"%s\" does not exist"), *************** *** 678,683 **** --- 684,693 ---- relkind = RELKIND_VIEW; break; + case OBJECT_MATVIEW: + relkind = RELKIND_MATVIEW; + break; + default: elog(ERROR, "unrecognized drop object type: %d", (int) drop->removeType); *************** *** 6439,6444 **** --- 6449,6455 ---- { case RELKIND_RELATION: case RELKIND_VIEW: + case RELKIND_MATVIEW: /* ok to change owner */ break; case RELKIND_INDEX: *************** *** 7715,7720 **** --- 7726,7732 ---- switch (stmttype) { case OBJECT_TABLE: + case OBJECT_MATVIEW: /* * For mostly-historical reasons, we allow ALTER TABLE to applyto *************** *** 7747,7752 **** --- 7759,7765 ---- { case RELKIND_RELATION: case RELKIND_VIEW: + case RELKIND_MATVIEW: /* ok to change schema */ break; case RELKIND_SEQUENCE: *** ./src/backend/commands/view.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/commands/view.c 2010-06-25 14:05:52.000000000 +0200 *************** *** 97,103 **** *--------------------------------------------------------------------- */ static Oid ! DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) { Oid viewOid, namespaceId; --- 97,103 ---- *--------------------------------------------------------------------- */ static Oid ! DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace, char relkind) { Oid viewOid, namespaceId; *************** *** 155,160 **** --- 155,168 ---- rel = relation_open(viewOid, AccessExclusiveLock); /* + * Check if do not try to replace materialized view. + */ + if (rel->rd_rel->relkind == RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CREATE OR REPLACE on materialized view is not supported!"), + errhint("Use CREATE MATERIALIZED VIEW ..."))); + /* * Make sure it *is* a view, and do permissions checks. */ if (rel->rd_rel->relkind!= RELKIND_VIEW) *************** *** 239,245 **** * existing view, so we don't need more code to complain if "replace" * is false). */ ! return DefineRelation(createStmt, RELKIND_VIEW); } } --- 247,253 ---- * existing view, so we don't need more code to complain if "replace" * is false). */ ! return DefineRelation(createStmt, relkind); } } *************** *** 299,305 **** } static void ! DefineViewRules(Oid viewOid, Query *viewParse, bool replace) { /* * Set up the ON SELECT rule. Since the queryhas already been through --- 307,313 ---- } static void ! DefineViewRules(Oid viewOid, Query *viewParse, bool is_instead, bool replace, bool is_materialized) { /* * Setup the ON SELECT rule. Since the query has already been through *************** *** 308,315 **** DefineQueryRewrite(pstrdup(ViewSelectRuleName), viewOid, NULL, ! CMD_SELECT, ! true, replace, list_make1(viewParse)); --- 316,323 ---- DefineQueryRewrite(pstrdup(ViewSelectRuleName), viewOid, NULL, ! is_materialized ? CMD_REFRESH : CMD_SELECT, ! is_materialized ? false : is_instead, replace, list_make1(viewParse)); *************** *** 465,471 **** * aborted. */ viewOid = DefineVirtualRelation(view, viewParse->targetList, ! stmt->replace); /* * The relation we have just created is not visible toany other commands --- 473,479 ---- * aborted. */ viewOid = DefineVirtualRelation(view, viewParse->targetList, ! stmt->replace, (stmt->ismaterialized ? RELKIND_MATVIEW : RELKIND_VIEW)); /* * The relation we have just created is not visible to any other commands *************** *** 483,487 **** /* * Now create the rules associated with the view. */ ! DefineViewRules(viewOid, viewParse, stmt->replace); } --- 491,495 ---- /* * Now create the rules associated with the view. */ ! DefineViewRules(viewOid, viewParse, true, stmt->replace, stmt->ismaterialized); } *** ./src/backend/nodes/copyfuncs.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/nodes/copyfuncs.c 2010-06-25 14:10:17.000000000 +0200 *************** *** 2845,2850 **** --- 2845,2851 ---- COPY_NODE_FIELD(aliases); COPY_NODE_FIELD(query); COPY_SCALAR_FIELD(replace); + COPY_SCALAR_FIELD(ismaterialized); return newnode; } *** ./src/backend/nodes/equalfuncs.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/nodes/equalfuncs.c 2010-06-25 14:10:37.000000000 +0200 *************** *** 1381,1386 **** --- 1381,1387 ---- COMPARE_NODE_FIELD(aliases); COMPARE_NODE_FIELD(query); COMPARE_SCALAR_FIELD(replace); + COMPARE_SCALAR_FIELD(ismaterialized); return true; } *** ./src/backend/parser/gram.y.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/parser/gram.y 2010-06-25 15:06:19.000000000 +0200 *************** *** 310,316 **** %type <fun_param_mode> arg_class %type <typnam> func_return func_type ! %type <boolean> TriggerForType OptTemp %type <oncommit> OnCommitOption %type <node> for_locking_item --- 310,316 ---- %type <fun_param_mode> arg_class %type <typnam> func_return func_type ! %type <boolean> TriggerForType OptTemp OptMater %type <oncommit> OnCommitOption %type <node> for_locking_item *************** *** 502,508 **** LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGIN_P ! MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER --- 502,508 ---- LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGIN_P ! MAPPING MATCH MATERIALIZED MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEXTNO NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER *************** *** 517,523 **** QUOTE ! RANGE READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICARESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE --- 517,523 ---- QUOTE ! RANGE READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACEREPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE *************** *** 1598,1603 **** --- 1598,1611 ---- n->relkind = OBJECT_VIEW; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $4; + n->cmds = $5; + n->relkind = OBJECT_MATVIEW; + $$ = (Node *)n; + } ; alter_table_cmds: *************** *** 1914,1919 **** --- 1922,1934 ---- n->def = (Node *)$2; $$ = (Node *)n; } + /* ALTER TABLE <name> REFRESH */ + | REFRESH + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_Refresh; + $$ = (Node *)n; + } ; alter_column_default: *************** *** 4046,4051 **** --- 4061,4067 ---- drop_type: TABLE { $$ = OBJECT_TABLE; } | SEQUENCE { $$ = OBJECT_SEQUENCE; } | VIEW { $$ = OBJECT_VIEW;} + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | INDEX { $$ = OBJECT_INDEX; } | TYPE_P { $$ = OBJECT_TYPE; } | DOMAIN_P { $$ = OBJECT_DOMAIN; } *************** *** 4102,4108 **** * The COMMENT ON statement can take different forms based upon the type of * the object associatedwith the comment. The form of the statement is: * ! * COMMENT ON [ [ DATABASE | DOMAIN | INDEX | SEQUENCE | TABLE | TYPE | VIEW | * CONVERSION | LANGUAGE| OPERATOR CLASS | LARGE OBJECT | * CAST | COLUMN | SCHEMA | TABLESPACE | ROLE | * TEXT SEARCH PARSER | TEXT SEARCH DICTIONARY | --- 4118,4124 ---- * The COMMENT ON statement can take different forms based upon the type of * the object associatedwith the comment. The form of the statement is: * ! * COMMENT ON [ [ DATABASE | DOMAIN | INDEX | SEQUENCE | TABLE | TYPE | [MATERIALIZED] VIEW | * CONVERSION| LANGUAGE | OPERATOR CLASS | LARGE OBJECT | * CAST | COLUMN | SCHEMA | TABLESPACE | ROLE | * TEXT SEARCH PARSER | TEXT SEARCH DICTIONARY | *************** *** 4281,4286 **** --- 4297,4303 ---- | DOMAIN_P { $$ = OBJECT_TYPE; } | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | TABLESPACE { $$ = OBJECT_TABLESPACE; } | ROLE { $$ = OBJECT_ROLE; } *************** *** 6319,6337 **** /***************************************************************************** * * QUERY: ! * CREATE [ OR REPLACE ] [ TEMP ] VIEW <viewname> '('target-list ')' * AS <query> [ WITH [ CASCADED| LOCAL ] CHECK OPTION ] * *****************************************************************************/ ! ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list AS SelectStmt opt_check_option { ViewStmt *n = makeNode(ViewStmt); ! n->view = $4; n->view->istemp = $2; ! n->aliases = $5; ! n->query = $7; n->replace = false; $$ = (Node *) n; } --- 6336,6355 ---- /***************************************************************************** * * QUERY: ! * CREATE [ OR REPLACE ] [ TEMP ] [ MATERIALIZED ] VIEW <viewname> '('target-list ')' * AS <query> [WITH [ CASCADED | LOCAL ] CHECK OPTION ] * *****************************************************************************/ ! ViewStmt: CREATE OptTemp OptMater VIEW qualified_name opt_column_list AS SelectStmt opt_check_option { ViewStmt *n = makeNode(ViewStmt); ! n->view = $5; n->view->istemp = $2; ! n->ismaterialized = $3; ! n->aliases = $6; ! n->query = $8; n->replace = false; $$ = (Node *) n; } *************** *** 6341,6346 **** --- 6359,6365 ---- ViewStmt *n = makeNode(ViewStmt); n->view = $6; n->view->istemp = $4; + n->ismaterialized = false; n->aliases = $7; n->query = $9; n->replace = true; *************** *** 6348,6353 **** --- 6367,6377 ---- } ; + + OptMater: MATERIALIZED { $$ = true; } + | /* EMPTY */ { $$ = false; } + ; + opt_check_option: WITH CHECK OPTION { *************** *** 10982,10987 **** --- 11006,11012 ---- | REASSIGN | RECHECK | RECURSIVE + | REFRESH | REINDEX | RELATIVE_P | RELEASE *************** *** 11211,11216 **** --- 11236,11242 ---- | LIMIT | LOCALTIME | LOCALTIMESTAMP + | MATERIALIZED | NOT | NULL_P | OFF *** ./src/backend/rewrite/rewriteDefine.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/rewrite/rewriteDefine.c 2010-06-25 14:27:04.000000000 +0200 *************** *** 159,165 **** referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, ! (evtype == CMD_SELECT) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO); /* * Also install dependencieson objects referenced in action and qual. --- 159,165 ---- referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, ! (evtype == CMD_SELECT || evtype == CMD_REFRESH) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO); /* * Alsoinstall dependencies on objects referenced in action and qual. *************** *** 246,252 **** * Verify relation is of a type that rules can sensibly be applied to. */ if (event_relation->rd_rel->relkind!= RELKIND_RELATION && ! event_relation->rd_rel->relkind != RELKIND_VIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a table or view", --- 246,253 ---- * Verify relation is of a type that rules can sensibly be applied to. */ if (event_relation->rd_rel->relkind!= RELKIND_RELATION && ! event_relation->rd_rel->relkind != RELKIND_VIEW && ! event_relation->rd_rel->relkind != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a table or view", *************** *** 288,294 **** errhint("Use triggers instead."))); } ! if (event_type == CMD_SELECT) { /* * Rules ON SELECT are restricted to view definitions --- 289,295 ---- errhint("Use triggers instead."))); } ! if (event_type == CMD_SELECT || event_type == CMD_REFRESH) { /* * Rules ON SELECT are restrictedto view definitions *************** *** 313,319 **** * ... the one action must be a SELECT, ... */ query = (Query *) linitial(action); ! if (!is_instead || query->commandType != CMD_SELECT || query->utilityStmt != NULL || query->intoClause != NULL) --- 314,320 ---- * ... the one action must be a SELECT, ... */ query = (Query *) linitial(action); ! if ((!is_instead && (event_relation->rd_rel->relkind != RELKIND_MATVIEW)) || query->commandType !=CMD_SELECT || query->utilityStmt != NULL || query->intoClause != NULL) *************** *** 349,355 **** RewriteRule *rule; rule = event_relation->rd_rules->rules[i]; ! if (rule->event == CMD_SELECT) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("\"%s\" is already a view", --- 350,356 ---- RewriteRule *rule; rule = event_relation->rd_rules->rules[i]; ! if (rule->event == CMD_SELECT || rule->event == CMD_REFRESH) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("\"%s\" is alreadya view", *************** *** 499,505 **** * * XXX what about getting rid of its TOAST table? For now, we don't. */ ! if (RelisBecomingView) RelationDropStorage(event_relation); /* Close rel, but keep lock till commit...*/ --- 500,506 ---- * * XXX what about getting rid of its TOAST table? For now, we don't. */ ! if (RelisBecomingView && (event_relation->rd_rel->relkind != RELKIND_MATVIEW)) RelationDropStorage(event_relation); /* Close rel, but keep lock till commit... */ *** ./src/backend/rewrite/rewriteHandler.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/rewrite/rewriteHandler.c 2010-06-25 14:36:41.000000000 +0200 *************** *** 1390,1396 **** for (i = 0; i < rules->numLocks; i++) { rule = rules->rules[i]; ! if (rule->event != CMD_SELECT) continue; if (rule->attrno > 0) --- 1390,1396 ---- for (i = 0; i < rules->numLocks; i++) { rule = rules->rules[i]; ! if (rule->event != CMD_SELECT && rule->event != CMD_REFRESH) continue; if (rule->attrno> 0) *************** *** 1422,1427 **** --- 1422,1433 ---- { rule = lfirst(l); + /* + * Prevent firing rule, if it is REFRESH rule + */ + if (rule->event == CMD_REFRESH) + continue; + parsetree = ApplyRetrieveRule(parsetree, rule, rt_index, *************** *** 1659,1664 **** --- 1665,1700 ---- rt_entry_relation = heap_open(rt_entry->relid, NoLock); /* + * Inserting, updating or deleting row in materilized views are not allowed + */ + if (rt_entry_relation->rd_rel->relkind == RELKIND_MATVIEW) + { + heap_close(rt_entry_relation, NoLock); + switch (parsetree->commandType) + { + case CMD_INSERT: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot insert into a materialized view"))); + break; + case CMD_UPDATE: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot update a materialized view"))); + break; + case CMD_DELETE: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot delete from a materialized view"))); + break; + default: + elog(ERROR, "unrecognized commandType: %d", + (int) parsetree->commandType); + break; + } + } + + /* * If it's an INSERT or UPDATE, rewrite the targetlist into standard * form. This will beneeded by the planner anyway, and doing it now * ensures that any references to NEW.field will behave sanely. *** ./src/backend/rewrite/rewriteSupport.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/rewrite/rewriteSupport.c 2010-06-25 12:38:58.000000000 +0200 *************** *** 69,75 **** { /* Do the update */ classForm->relhasrules = relHasRules; ! if (relIsBecomingView) classForm->relkind = RELKIND_VIEW; simple_heap_update(relationRelation,&tuple->t_self, tuple); --- 69,77 ---- { /* Do the update */ classForm->relhasrules = relHasRules; ! ! /* do not change RELKIND if its Materialized View */ ! if (relIsBecomingView && classForm->relkind != RELKIND_MATVIEW) classForm->relkind = RELKIND_VIEW; simple_heap_update(relationRelation, &tuple->t_self, tuple); *** ./src/backend/tcop/utility.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/tcop/utility.c 2010-06-25 14:38:20.000000000 +0200 *************** *** 606,611 **** --- 606,612 ---- case OBJECT_TABLE: case OBJECT_SEQUENCE: caseOBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_INDEX: RemoveRelations(stmt); break; *************** *** 1544,1549 **** --- 1545,1553 ---- case OBJECT_VIEW: tag = "DROP VIEW"; break; + case OBJECT_MATVIEW: + tag = "DROP MATERIALIZED VIEW"; + break; case OBJECT_INDEX: tag = "DROP INDEX"; break; *************** *** 1775,1780 **** --- 1779,1787 ---- case OBJECT_VIEW: tag = "ALTER VIEW"; break; + case OBJECT_MATVIEW: + tag = "ALTER MATERIALIZED VIEW"; + break; default: tag = "???"; break; *** ./src/backend/utils/adt/ruleutils.c.orig 2010-06-23 16:31:25.000000000 +0200 --- ./src/backend/utils/adt/ruleutils.c 2010-06-25 14:40:10.000000000 +0200 *************** *** 2384,2391 **** query = (Query *) linitial(actions); ! if (ev_type != '1' || ev_attr >= 0 || !is_instead || ! strcmp(ev_qual, "<>") != 0 || query->commandType != CMD_SELECT) { appendStringInfo(buf, "Not a view"); return; --- 2384,2393 ---- query = (Query *) linitial(actions); ! /* ev_type == 1 SELECT, ev_type == 6 REFRESH */ ! if (ev_attr >= 0 || strcmp(ev_qual, "<>") != 0 || query->commandType != CMD_SELECT || ! ((!is_instead || ev_type != '1') && ! (is_instead || ev_type != '6'))) { appendStringInfo(buf, "Not a view"); return; *** ./src/include/catalog/pg_class.h.orig 2010-06-23 16:31:26.000000000 +0200 --- ./src/include/catalog/pg_class.h 2010-06-24 09:45:08.000000000 +0200 *************** *** 147,152 **** --- 147,153 ---- #define RELKIND_UNCATALOGED 'u' /* temporary heap */ #define RELKIND_TOASTVALUE 't' /* moved off huge values */ #define RELKIND_VIEW 'v' /* view*/ + #define RELKIND_MATVIEW 'm' /* materialized view */ #define RELKIND_COMPOSITE_TYPE 'c' /* composite type */ #endif /* PG_CLASS_H */ *** ./src/include/nodes/nodes.h.orig 2010-06-23 16:31:26.000000000 +0200 --- ./src/include/nodes/nodes.h 2010-06-25 14:41:37.000000000 +0200 *************** *** 512,517 **** --- 512,518 ---- CMD_DELETE, CMD_UTILITY, /* cmds like create, destroy, copy, vacuum, * etc. */ + CMD_REFRESH, /* refreshing tables, like materialized views */ CMD_NOTHING /*dummy command for instead nothing rules * with qual */ } CmdType; *** ./src/include/nodes/parsenodes.h.orig 2010-06-23 16:31:26.000000000 +0200 --- ./src/include/nodes/parsenodes.h 2010-06-24 14:30:11.000000000 +0200 *************** *** 1055,1060 **** --- 1055,1061 ---- OBJECT_INDEX, OBJECT_LANGUAGE, OBJECT_LARGEOBJECT, + OBJECT_MATVIEW, OBJECT_OPCLASS, OBJECT_OPERATOR, OBJECT_OPFAMILY, *************** *** 1150,1156 **** AT_EnableReplicaRule, /* ENABLE REPLICA RULE name */ AT_DisableRule, /*DISABLE RULE name */ AT_AddInherit, /* INHERIT parent */ ! AT_DropInherit /* NO INHERIT parent */ } AlterTableType; typedef struct AlterTableCmd /* one subcommandof an ALTER TABLE */ --- 1151,1158 ---- AT_EnableReplicaRule, /* ENABLE REPLICA RULE name */ AT_DisableRule, /*DISABLE RULE name */ AT_AddInherit, /* INHERIT parent */ ! AT_DropInherit, /* NO INHERIT parent */ ! AT_Refresh /* alter materialized view REFRESH */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ *************** *** 2181,2186 **** --- 2183,2189 ---- List *aliases; /* target column names */ Node *query; /* the SELECTquery */ bool replace; /* replace an existing view? */ + bool ismaterialized; /* materialize view? */ } ViewStmt; /* ---------------------- *** ./src/include/parser/kwlist.h.orig 2010-06-23 16:31:26.000000000 +0200 --- ./src/include/parser/kwlist.h 2010-06-24 14:14:30.000000000 +0200 *************** *** 229,234 **** --- 229,235 ---- PG_KEYWORD("login", LOGIN_P, UNRESERVED_KEYWORD) PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD) PG_KEYWORD("match",MATCH, UNRESERVED_KEYWORD) + PG_KEYWORD("materialized", MATERIALIZED, RESERVED_KEYWORD) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD) PG_KEYWORD("minute",MINUTE_P, UNRESERVED_KEYWORD) PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD) *************** *** 302,307 **** --- 303,309 ---- PG_KEYWORD("recheck", RECHECK, UNRESERVED_KEYWORD) PG_KEYWORD("recursive", RECURSIVE, UNRESERVED_KEYWORD)PG_KEYWORD("references", REFERENCES, RESERVED_KEYWORD) + PG_KEYWORD("refresh", REFRESH, UNRESERVED_KEYWORD) PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD) PG_KEYWORD("relative",RELATIVE_P, UNRESERVED_KEYWORD) PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD)
В списке pgsql-hackers по дате отправления: