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 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.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 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.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_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.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 apply to
***************
*** 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 query has already been through
--- 307,313 ----
}
static void
! DefineViewRules(Oid viewOid, Query *viewParse, bool is_instead, bool replace, bool is_materialized)
{
/*
* Set up 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 to any 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 NEXT NO 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 REPLICA RESET 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 REPLACE REPLICA 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 associated with 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 associated with 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 dependencies on objects referenced in action and qual.
--- 159,165 ----
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced,
! (evtype == CMD_SELECT || evtype == CMD_REFRESH) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO);
/*
* Also install 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 restricted to 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 already a 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 be needed 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:
case OBJECT_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 subcommand of 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 SELECT query */
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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
