Here's a rebased v5 due to conflicts with de1e298857. No other changes since v4.
-- Erik Wienhold
From ac9bba0960f7a6fa507020400f1b4bcf4c9a25d3 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Tue, 21 May 2024 18:35:47 +0200
Subject: [PATCH v5 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW
---
.../sgml/ref/create_materialized_view.sgml | 15 +-
src/backend/commands/createas.c | 207 ++++++++++++++----
src/backend/commands/tablecmds.c | 8 +-
src/backend/commands/view.c | 106 ++++++---
src/backend/parser/gram.y | 15 ++
src/bin/psql/tab-complete.in.c | 26 ++-
src/include/commands/view.h | 3 +
src/include/nodes/parsenodes.h | 2 +-
src/include/nodes/primnodes.h | 1 +
src/test/regress/expected/matview.out | 191 ++++++++++++++++
src/test/regress/sql/matview.sql | 108 +++++++++
11 files changed, 589 insertions(+), 93 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml
b/doc/src/sgml/ref/create_materialized_view.sgml
index 62d897931c..5e03320eb7 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
+CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
[ (<replaceable>column_name</replaceable> [, ...] ) ]
[ USING <replaceable class="parameter">method</replaceable> ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [=
<replaceable class="parameter">value</replaceable>] [, ... ] ) ]
@@ -60,6 +60,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>OR REPLACE</literal></term>
+ <listitem>
+ <para>
+ Replaces a materialized view if it already exists.
+ Specifying <literal>OR REPLACE</literal> together with
+ <literal>IF NOT EXISTS</literal> is an error.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
@@ -67,7 +78,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
Do not throw an error if a materialized view with the same name already
exists. A notice is issued in this case. Note that there is no guarantee
that the existing materialized view is anything like the one that would
- have been created.
+ have been created, unless you use <literal>OR REPLACE</literal> instead.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 23cecd99c9..cba369114b 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self);
static ObjectAddress
create_ctas_internal(List *attrList, IntoClause *into)
{
- CreateStmt *create = makeNode(CreateStmt);
- bool is_matview;
+ bool is_matview,
+ replace = false;
char relkind;
- Datum toast_options;
- const char *const validnsps[] = HEAP_RELOPT_NAMESPACES;
+ Oid matviewOid = InvalidOid;
ObjectAddress intoRelationAddr;
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW
*/
is_matview = (into->viewQuery != NULL);
relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION;
- /*
- * Create the target relation by faking up a CREATE TABLE parsetree and
- * passing it to DefineRelation.
- */
- create->relation = into->rel;
- create->tableElts = attrList;
- create->inhRelations = NIL;
- create->ofTypename = NULL;
- create->constraints = NIL;
- create->options = into->options;
- create->oncommit = into->onCommit;
- create->tablespacename = into->tableSpaceName;
- create->if_not_exists = false;
- create->accessMethod = into->accessMethod;
+ /* Check if an existing materialized view needs to be replaced. */
+ if (is_matview)
+ {
+ LOCKMODE lockmode;
- /*
- * Create the relation. (This will error out if there's an existing
view,
- * so we don't need more code to complain if "replace" is false.)
- */
- intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL,
NULL);
+ lockmode = into->replace ? AccessExclusiveLock : NoLock;
+ (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode,
+
&matviewOid);
+ replace = OidIsValid(matviewOid) && into->replace;
+ }
- /*
- * If necessary, create a TOAST table for the target table. Note that
- * NewRelationCreateToastTable ends with CommandCounterIncrement(), so
- * that the TOAST table will be visible for insertion.
- */
- CommandCounterIncrement();
+ if (is_matview && replace)
+ {
+ Relation rel;
+ List *atcmds = NIL;
+ AlterTableCmd *atcmd;
+ TupleDesc descriptor;
+
+ rel = relation_open(matviewOid, NoLock);
+
+ if (rel->rd_rel->relkind != RELKIND_MATVIEW)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a materialized
view",
+
RelationGetRelationName(rel)));
+
+ CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW");
+
+ descriptor = BuildDescForRelation(attrList);
+ checkViewColumns(descriptor, rel->rd_att, true);
+
+ /* Add new attributes via ALTER TABLE. */
+ if (list_length(attrList) > rel->rd_att->natts)
+ {
+ ListCell *c;
+ int skip = rel->rd_att->natts;
+
+ foreach(c, attrList)
+ {
+ if (skip > 0)
+ {
+ skip--;
+ continue;
+ }
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_AddColumnToView;
+ atcmd->def = (Node *) lfirst(c);
+ atcmds = lappend(atcmds, atcmd);
+ }
+ }
+
+ /* Set access method via ALTER TABLE. */
+ if (into->accessMethod != NULL)
+ {
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetAccessMethod;
+ atcmd->name = into->accessMethod;
+ atcmds = lappend(atcmds, atcmd);
+ }
+
+ /* Set tablespace via ALTER TABLE. */
+ if (into->tableSpaceName != NULL)
+ {
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetTableSpace;
+ atcmd->name = into->tableSpaceName;
+ atcmds = lappend(atcmds, atcmd);
+ }
+
+ /* Set storage parameters via ALTER TABLE. */
+ if (into->options != NIL)
+ {
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_ReplaceRelOptions;
+ atcmd->def = (Node *) into->options;
+ atcmds = lappend(atcmds, atcmd);
+ }
+
+ if (atcmds != NIL)
+ {
+ AlterTableInternal(matviewOid, atcmds, true);
+ CommandCounterIncrement();
+ }
+
+ relation_close(rel, NoLock);
+
+ ObjectAddressSet(intoRelationAddr, RelationRelationId,
matviewOid);
+ }
+ else
+ {
+ CreateStmt *create = makeNode(CreateStmt);
+ Datum toast_options;
+ const static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
+
+ /*
+ * Create the target relation by faking up a CREATE TABLE
parsetree
+ * and passing it to DefineRelation.
+ */
+ create->relation = into->rel;
+ create->tableElts = attrList;
+ create->inhRelations = NIL;
+ create->ofTypename = NULL;
+ create->constraints = NIL;
+ create->options = into->options;
+ create->oncommit = into->onCommit;
+ create->tablespacename = into->tableSpaceName;
+ create->if_not_exists = false;
+ create->accessMethod = into->accessMethod;
+
+ /*
+ * Create the relation. (This will error out if there's an
existing
+ * view, so we don't need more code to complain if "replace" is
+ * false.)
+ */
+ intoRelationAddr = DefineRelation(create, relkind, InvalidOid,
NULL,
+
NULL);
- /* parse and validate reloptions for the toast table */
- toast_options = transformRelOptions((Datum) 0,
-
create->options,
-
"toast",
-
validnsps,
-
true, false);
+ /*
+ * If necessary, create a TOAST table for the target table.
Note that
+ * NewRelationCreateToastTable ends with
CommandCounterIncrement(), so
+ * that the TOAST table will be visible for insertion.
+ */
+ CommandCounterIncrement();
+
+ /* parse and validate reloptions for the toast table */
+ toast_options = transformRelOptions((Datum) 0,
+
create->options,
+
"toast",
+
validnsps,
+
true, false);
- (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
+ (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
- NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options);
+ NewRelationCreateToastTable(intoRelationAddr.objectId,
toast_options);
+ }
/* Create the "view" part of a materialized view. */
if (is_matview)
@@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into)
/* StoreViewQuery scribbles on tree, so make a copy */
Query *query = copyObject(into->viewQuery);
- StoreViewQuery(intoRelationAddr.objectId, query, false);
+ StoreViewQuery(intoRelationAddr.objectId, query, replace);
CommandCounterIncrement();
}
@@ -232,7 +328,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt
*stmt,
/* Check if the relation exists or not */
if (CreateTableAsRelExists(stmt))
+ {
+ /* An existing materialized view can be replaced. */
+ if (is_matview && into->replace)
+ {
+ RefreshMatViewStmt *refresh;
+
+ /* Change the relation to match the new query and other
options. */
+ (void) create_ctas_nodata(query->targetList, into);
+
+ /* Refresh the materialized view with a fake statement.
*/
+ refresh = makeNode(RefreshMatViewStmt);
+ refresh->relation = into->rel;
+ refresh->skipData = into->skipData;
+ refresh->concurrent = false;
+
+ return ExecRefreshMatView(refresh, NULL, NULL);
+ }
+
return InvalidObjectAddress;
+ }
/*
* Create the tuple receiver object and insert info it will need
@@ -400,14 +515,15 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas)
oldrelid = get_relname_relid(into->rel->relname, nspid);
if (OidIsValid(oldrelid))
{
- if (!ctas->if_not_exists)
+ if (!ctas->if_not_exists && !into->replace)
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg("relation \"%s\" already
exists",
into->rel->relname)));
/*
- * The relation exists and IF NOT EXISTS has been specified.
+ * The relation exists and IF NOT EXISTS or OR REPLACE has been
+ * specified.
*
* If we are in an extension script, insist that the
pre-existing
* object be a member of the extension, to avoid security risks.
@@ -415,11 +531,12 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas)
ObjectAddressSet(address, RelationRelationId, oldrelid);
checkMembershipInCurrentExtension(&address);
- /* OK to skip */
- ereport(NOTICE,
- (errcode(ERRCODE_DUPLICATE_TABLE),
- errmsg("relation \"%s\" already exists,
skipping",
- into->rel->relname)));
+ if (ctas->if_not_exists)
+ /* OK to skip */
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already
exists, skipping",
+ into->rel->relname)));
return true;
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4fc54bd6eb..12ba984a05 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4589,7 +4589,7 @@ AlterTableGetLockLevel(List *cmds)
* Subcommands that may be visible to
concurrent SELECTs
*/
case AT_DropColumn: /* change visible to SELECT */
- case AT_AddColumnToView: /* CREATE VIEW */
+ case AT_AddColumnToView: /* via CREATE OR
REPLACE [MATERIALIZED] VIEW */
case AT_DropOids: /* used to equiv to DropColumn
*/
case AT_EnableAlwaysRule: /* may change SELECT
rules */
case AT_EnableReplicaRule: /* may change SELECT
rules */
@@ -4884,8 +4884,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* Recursion occurs during execution phase */
pass = AT_PASS_ADD_COL;
break;
- case AT_AddColumnToView: /* add column via CREATE OR
REPLACE VIEW */
- ATSimplePermissions(cmd->subtype, rel, ATT_VIEW);
+ case AT_AddColumnToView: /* via CREATE OR REPLACE
[MATERIALIZED] VIEW */
+ ATSimplePermissions(cmd->subtype, rel, ATT_VIEW |
ATT_MATVIEW);
ATPrepAddColumn(wqueue, rel, recurse, recursing, true,
cmd,
lockmode, context);
/* Recursion occurs during execution phase */
@@ -5314,7 +5314,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
switch (cmd->subtype)
{
case AT_AddColumn: /* ADD COLUMN */
- case AT_AddColumnToView: /* add column via CREATE OR
REPLACE VIEW */
+ case AT_AddColumnToView: /* via CREATE OR REPLACE
[MATERIALIZED] VIEW */
address = ATExecAddColumn(wqueue, tab, rel, &cmd,
cmd->recurse, false,
lockmode, cur_pass, context);
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index 6f0301555e..53d4cacc4c 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -30,8 +30,6 @@
#include "utils/lsyscache.h"
#include "utils/rel.h"
-static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc);
-
/*---------------------------------------------------------------------
* DefineVirtualRelation
*
@@ -129,7 +127,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool
replace,
* column list.
*/
descriptor = BuildDescForRelation(attrList);
- checkViewColumns(descriptor, rel->rd_att);
+ checkViewColumns(descriptor, rel->rd_att, false);
/*
* If new attributes have been added, we must add pg_attribute
entries
@@ -263,15 +261,22 @@ DefineVirtualRelation(RangeVar *relation, List *tlist,
bool replace,
* added to generate specific complaints. Also, we allow the new view to have
* more columns than the old.
*/
-static void
-checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
+void
+checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, bool is_matview)
{
int i;
if (newdesc->natts < olddesc->natts)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("cannot drop columns from view")));
+ {
+ if (is_matview)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns from
materialized view"));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns from
view")));
+ }
for (i = 0; i < olddesc->natts; i++)
{
@@ -280,17 +285,34 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
/* XXX msg not right, but we don't support DROP COL on view
anyway */
if (newattr->attisdropped != oldattr->attisdropped)
- ereport(ERROR,
-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("cannot drop columns from
view")));
+ {
+ if (is_matview)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns
from materialized view"));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns
from view")));
+ }
if (strcmp(NameStr(newattr->attname),
NameStr(oldattr->attname)) != 0)
- ereport(ERROR,
-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("cannot change name of view
column \"%s\" to \"%s\"",
-
NameStr(oldattr->attname),
-
NameStr(newattr->attname)),
- errhint("Use ALTER VIEW ... RENAME
COLUMN ... to change name of view column instead.")));
+ {
+ if (is_matview)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change name of
materialized view column \"%s\" to \"%s\"",
+
NameStr(oldattr->attname),
+
NameStr(newattr->attname)),
+ errhint("Use ALTER MATERIALIZED
VIEW ... RENAME COLUMN ... to change name of materialized view column
instead."));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change name of
view column \"%s\" to \"%s\"",
+
NameStr(oldattr->attname),
+
NameStr(newattr->attname)),
+ errhint("Use ALTER VIEW ...
RENAME COLUMN ... to change name of view column instead.")));
+ }
/*
* We cannot allow type, typmod, or collation to change, since
these
@@ -299,26 +321,48 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
*/
if (newattr->atttypid != oldattr->atttypid ||
newattr->atttypmod != oldattr->atttypmod)
- ereport(ERROR,
-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("cannot change data type of
view column \"%s\" from %s to %s",
-
NameStr(oldattr->attname),
-
format_type_with_typemod(oldattr->atttypid,
-
oldattr->atttypmod),
-
format_type_with_typemod(newattr->atttypid,
-
newattr->atttypmod))));
+ {
+ if (is_matview)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change data type
of materialized view column \"%s\" from %s to %s",
+
NameStr(oldattr->attname),
+
format_type_with_typemod(oldattr->atttypid,
+
oldattr->atttypmod),
+
format_type_with_typemod(newattr->atttypid,
+
newattr->atttypmod)));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change data
type of view column \"%s\" from %s to %s",
+
NameStr(oldattr->attname),
+
format_type_with_typemod(oldattr->atttypid,
+
oldattr->atttypmod),
+
format_type_with_typemod(newattr->atttypid,
+
newattr->atttypmod))));
+ }
/*
* At this point, attcollations should be both valid or both
invalid,
* so applying get_collation_name unconditionally should be
fine.
*/
if (newattr->attcollation != oldattr->attcollation)
- ereport(ERROR,
-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("cannot change collation of
view column \"%s\" from \"%s\" to \"%s\"",
-
NameStr(oldattr->attname),
-
get_collation_name(oldattr->attcollation),
-
get_collation_name(newattr->attcollation))));
+ {
+ if (is_matview)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change collation
of materialized view column \"%s\" from \"%s\" to \"%s\"",
+
NameStr(oldattr->attname),
+
get_collation_name(oldattr->attcollation),
+
get_collation_name(newattr->attcollation)));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change
collation of view column \"%s\" from \"%s\" to \"%s\"",
+
NameStr(oldattr->attname),
+
get_collation_name(oldattr->attcollation),
+
get_collation_name(newattr->attcollation))));
+ }
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6079de70e0..c917c11f9e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4854,6 +4854,21 @@ CreateMatViewStmt:
$8->skipData = !($11);
$$ = (Node *) ctas;
}
+ | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target
AS SelectStmt opt_with_data
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+
+ ctas->query = $9;
+ ctas->into = $7;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = false;
+ /* cram additional flags into the
IntoClause */
+ $7->rel->relpersistence = $4;
+ $7->skipData = !($10);
+ $7->replace = true;
+ $$ = (Node *) ctas;
+ }
;
create_mv_target:
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa2..dd72c617d5 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2144,7 +2144,7 @@ match_previous_words(int pattern_id,
/* complete with something you can create or replace */
else if (TailMatches("CREATE", "OR", "REPLACE"))
COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE",
"VIEW",
- "AGGREGATE", "TRANSFORM", "TRIGGER");
+ "AGGREGATE", "TRANSFORM", "TRIGGER",
"MATERIALIZED VIEW");
/* DROP, but not DROP embedded in other commands */
/* complete with something you can drop */
@@ -3979,28 +3979,34 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("SELECT");
/* CREATE MATERIALIZED VIEW */
- else if (Matches("CREATE", "MATERIALIZED"))
+ else if (Matches("CREATE", "MATERIALIZED") ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED"))
COMPLETE_WITH("VIEW");
- /* Complete CREATE MATERIALIZED VIEW <name> with AS or USING */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+ /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS or
USING */
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny))
COMPLETE_WITH("AS", "USING");
/*
- * Complete CREATE MATERIALIZED VIEW <name> USING with list of access
+ * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> USING with
list of access
* methods
*/
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING"))
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING") ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "USING"))
COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
- /* Complete CREATE MATERIALIZED VIEW <name> USING <access method> with
AS */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING",
MatchAny))
+ /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> USING
<access method> with AS */
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING",
MatchAny) ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "USING", MatchAny))
COMPLETE_WITH("AS");
/*
- * Complete CREATE MATERIALIZED VIEW <name> [USING <access method> ] AS
+ * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> [USING
<access method> ] AS
* with "SELECT"
*/
else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||
- Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny,
"USING", MatchAny, "AS"))
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "AS") ||
+ Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny,
"USING", MatchAny, "AS") ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "USING", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
/* CREATE EVENT TRIGGER */
diff --git a/src/include/commands/view.h b/src/include/commands/view.h
index c41f51b161..95290f0a1c 100644
--- a/src/include/commands/view.h
+++ b/src/include/commands/view.h
@@ -22,4 +22,7 @@ extern ObjectAddress DefineView(ViewStmt *stmt, const char
*queryString,
extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace);
+extern void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc,
+ bool is_matview);
+
#endif /* VIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b191eaaeca..f4c16e6a9e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2358,7 +2358,7 @@ typedef struct AlterTableStmt
typedef enum AlterTableType
{
AT_AddColumn, /* add column */
- AT_AddColumnToView, /* implicitly via CREATE OR
REPLACE VIEW */
+ AT_AddColumnToView, /* implicitly via CREATE OR
REPLACE [MATERIALIZED] VIEW */
AT_ColumnDefault, /* alter column default */
AT_CookedColumnDefault, /* add a pre-cooked column default */
AT_DropNotNull, /* alter column drop not null */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 9c2957eb54..fce057a8ac 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -168,6 +168,7 @@ typedef struct IntoClause
/* materialized view's SELECT query */
struct Query *viewQuery pg_node_attr(query_jumble_ignore);
bool skipData; /* true for WITH NO DATA */
+ bool replace; /* replace existing matview? */
} IntoClause;
diff --git a/src/test/regress/expected/matview.out
b/src/test/regress/expected/matview.out
index 9eab51bc2a..71f6a0681d 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists,
skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+--
+-- test CREATE OR REPLACE MATERIALIZED VIEW
+--
+-- matview does not already exist
+DROP MATERIALIZED VIEW IF EXISTS mvtest_replace;
+NOTICE: materialized view "mvtest_replace" does not exist, skipping
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS a;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 1
+(1 row)
+
+-- replace query with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2 AS a;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 2
+(1 row)
+
+-- replace query without data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 3 AS a
+ WITH NO DATA;
+SELECT * FROM mvtest_replace; -- error: not populated
+ERROR: materialized view "mvtest_replace" has not been populated
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 3
+(1 row)
+
+-- add column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 4 AS a, 1 b;
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 4 | 1
+(1 row)
+
+-- replace table options
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+ a | b | relname | reloptions | spcname | amname
+---+---+----------------+------------+---------+--------
+ 4 | 1 | mvtest_replace | | | heap
+(1 row)
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ USING heap2
+ WITH (fillfactor = 50)
+ TABLESPACE regress_tblspace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+ a | b | relname | reloptions | spcname | amname
+---+---+----------------+-----------------+------------------+--------
+ 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2
+(1 row)
+
+-- can replace matview that has a dependent view
+CREATE VIEW mvtest_replace_v AS
+ SELECT * FROM mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 6 AS a, 1 AS b;
+SELECT * FROM mvtest_replace, mvtest_replace_v;
+ a | b | a | b
+---+---+---+---
+ 6 | 1 | 6 | 1
+(1 row)
+
+DROP VIEW mvtest_replace_v;
+-- index gets rebuilt when replacing with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 7 AS a, 1 AS b;
+CREATE UNIQUE INDEX ON mvtest_replace (b);
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 7 | 1
+(1 row)
+
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Index Scan using mvtest_replace_b_idx on mvtest_replace
+ Index Cond: (b = 1)
+(2 rows)
+
+SELECT * FROM mvtest_replace WHERE b = 1;
+ a | b
+---+---
+ 7 | 1
+(1 row)
+
+RESET enable_seqscan;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 8 AS a, 1 AS b;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Index Scan using mvtest_replace_b_idx on mvtest_replace
+ Index Cond: (b = 1)
+(2 rows)
+
+SELECT * FROM mvtest_replace WHERE b = 1;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+RESET enable_seqscan;
+-- cannot change column data type
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 9 AS a, 'x' AS b; -- error
+ERROR: cannot change data type of materialized view column "b" from integer
to text
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+-- cannot rename column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 10 AS a, 1 AS b2; -- error
+ERROR: cannot change name of materialized view column "b" to "b2"
+HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of
materialized view column instead.
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- cannot change column collation
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error
+ERROR: cannot change collation of materialized view column "c" from "C" to
"POSIX"
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- cannot drop column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 13 AS a, 1 AS b; -- error
+ERROR: cannot drop columns from materialized view
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- must target a matview
+CREATE VIEW mvtest_not_mv AS
+ SELECT 1 AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS
+ SELECT 1 AS a; -- error
+ERROR: "mvtest_not_mv" is not a materialized view
+DROP VIEW mvtest_not_mv;
+-- cannot use OR REPLACE with IF NOT EXISTS
+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
+ SELECT 1 AS a;
+ERROR: syntax error at or near "NOT"
+LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...
+ ^
+DROP MATERIALIZED VIEW mvtest_replace;
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 6704eeae2d..d6a4dc4b85 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -314,3 +314,111 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF,
BUFFERS OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
+
+--
+-- test CREATE OR REPLACE MATERIALIZED VIEW
+--
+
+-- matview does not already exist
+DROP MATERIALIZED VIEW IF EXISTS mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS a;
+SELECT * FROM mvtest_replace;
+
+-- replace query with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2 AS a;
+SELECT * FROM mvtest_replace;
+
+-- replace query without data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 3 AS a
+ WITH NO DATA;
+SELECT * FROM mvtest_replace; -- error: not populated
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+
+-- add column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 4 AS a, 1 b;
+SELECT * FROM mvtest_replace;
+
+-- replace table options
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ USING heap2
+ WITH (fillfactor = 50)
+ TABLESPACE regress_tblspace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+
+-- can replace matview that has a dependent view
+CREATE VIEW mvtest_replace_v AS
+ SELECT * FROM mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 6 AS a, 1 AS b;
+SELECT * FROM mvtest_replace, mvtest_replace_v;
+DROP VIEW mvtest_replace_v;
+
+-- index gets rebuilt when replacing with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 7 AS a, 1 AS b;
+CREATE UNIQUE INDEX ON mvtest_replace (b);
+SELECT * FROM mvtest_replace;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+SELECT * FROM mvtest_replace WHERE b = 1;
+RESET enable_seqscan;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 8 AS a, 1 AS b;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+SELECT * FROM mvtest_replace WHERE b = 1;
+RESET enable_seqscan;
+
+-- cannot change column data type
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 9 AS a, 'x' AS b; -- error
+SELECT * FROM mvtest_replace;
+
+-- cannot rename column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 10 AS a, 1 AS b2; -- error
+SELECT * FROM mvtest_replace;
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;
+SELECT * FROM mvtest_replace;
+
+-- cannot change column collation
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error
+SELECT * FROM mvtest_replace;
+
+-- cannot drop column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 13 AS a, 1 AS b; -- error
+SELECT * FROM mvtest_replace;
+
+-- must target a matview
+CREATE VIEW mvtest_not_mv AS
+ SELECT 1 AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS
+ SELECT 1 AS a; -- error
+DROP VIEW mvtest_not_mv;
+
+-- cannot use OR REPLACE with IF NOT EXISTS
+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
+ SELECT 1 AS a;
+
+DROP MATERIALIZED VIEW mvtest_replace;
--
2.48.0
From 2478cd438360a2fd5d74082c96b8feabd49ac593 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Tue, 28 May 2024 02:19:53 +0200
Subject: [PATCH v5 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS
---
src/backend/parser/gram.y | 14 ++++++
.../expected/test_extensions.out | 45 +++++++++++++++++++
src/test/regress/expected/matview.out | 20 +++++++++
3 files changed, 79 insertions(+)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c917c11f9e..cb5647dcb6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4853,6 +4853,20 @@ CreateMatViewStmt:
$8->rel->relpersistence = $2;
$8->skipData = !($11);
$$ = (Node *) ctas;
+
+ if (ctas->into->rel->schemaname)
+ ereport(WARNING,
+
errmsg("IF NOT EXISTS is deprecated in materialized view creation"),
+
errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.%s.",
+
ctas->into->rel->schemaname,
+
ctas->into->rel->relname),
+
parser_errposition(@1));
+ else
+ ereport(WARNING,
+
errmsg("IF NOT EXISTS is deprecated in materialized view creation"),
+
errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.",
+
ctas->into->rel->relname),
+
parser_errposition(@1));
}
| CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target
AS SelectStmt opt_with_data
{
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out
b/src/test/modules/test_extensions/expected/test_extensions.out
index d5388a1fec..9a1e0f7658 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -404,6 +404,11 @@ Objects in extension "test_ext_cor"
CREATE COLLATION ext_cine_coll
( LC_COLLATE = "C", LC_CTYPE = "C" );
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: collation ext_cine_coll is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE COLLATION IF NOT EXISTS ext_cine_coll
@@ -412,6 +417,11 @@ extension script file "test_ext_cine--1.0.sql", near line
10
DROP COLLATION ext_cine_coll;
CREATE MATERIALIZED VIEW ext_cine_mv AS SELECT 11 AS f1;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: materialized view ext_cine_mv is not a member of extension
"test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS
SELECT 42 AS f1"
@@ -420,6 +430,11 @@ DROP MATERIALIZED VIEW ext_cine_mv;
CREATE FOREIGN DATA WRAPPER dummy;
CREATE SERVER ext_cine_srv FOREIGN DATA WRAPPER dummy;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: server ext_cine_srv is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE SERVER IF NOT EXISTS ext_cine_srv FOREIGN DATA
WRAPPER ext_cine_fdw"
@@ -427,6 +442,11 @@ extension script file "test_ext_cine--1.0.sql", near line
17
DROP SERVER ext_cine_srv;
CREATE SCHEMA ext_cine_schema;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: schema ext_cine_schema is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE SCHEMA IF NOT EXISTS ext_cine_schema"
@@ -434,6 +454,11 @@ extension script file "test_ext_cine--1.0.sql", near line
19
DROP SCHEMA ext_cine_schema;
CREATE SEQUENCE ext_cine_seq;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: sequence ext_cine_seq is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE SEQUENCE IF NOT EXISTS ext_cine_seq"
@@ -441,6 +466,11 @@ extension script file "test_ext_cine--1.0.sql", near line
21
DROP SEQUENCE ext_cine_seq;
CREATE TABLE ext_cine_tab1 (x int);
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: table ext_cine_tab1 is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab1 (x int)"
@@ -448,12 +478,22 @@ extension script file "test_ext_cine--1.0.sql", near line
23
DROP TABLE ext_cine_tab1;
CREATE TABLE ext_cine_tab2 AS SELECT 42 AS y;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
ERROR: table ext_cine_tab2 is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab2 AS SELECT 42
AS y"
extension script file "test_ext_cine--1.0.sql", near line 25
DROP TABLE ext_cine_tab2;
CREATE EXTENSION test_ext_cine;
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
\dx+ test_ext_cine
Objects in extension "test_ext_cine"
Object description
@@ -475,6 +515,11 @@ Objects in extension "test_ext_cine"
(14 rows)
ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
+QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1;
\dx+ test_ext_cine
Objects in extension "test_ext_cine"
Object description
diff --git a/src/test/regress/expected/matview.out
b/src/test/regress/expected/matview.out
index 71f6a0681d..d506c615da 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -565,6 +565,10 @@ CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM
mvtest_foo_data;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
ERROR: relation "mvtest_mv_foo" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM
mvtest_foo_data;
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELE...
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW mvtest_mv_foo.
NOTICE: relation "mvtest_mv_foo" already exists, skipping
CREATE UNIQUE INDEX ON mvtest_mv_foo (i);
RESET ROLE;
@@ -662,12 +666,20 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0;
-- error
ERROR: relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
CREATE MATERIALIZED VIEW matview_ine_tab AS
@@ -676,6 +688,10 @@ ERROR: relation "matview_ine_tab" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
QUERY PLAN
------------
@@ -688,6 +704,10 @@ ERROR: relation "matview_ine_tab" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation
+LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
+ ^
+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
QUERY PLAN
------------
--
2.48.0
From 68c8209174f48479df34f37b9ad303a737571473 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Fri, 26 Jul 2024 23:33:15 +0200
Subject: [PATCH v5 3/3] Replace matview WITH OLD DATA
---
.../sgml/ref/create_materialized_view.sgml | 16 +++++++++--
src/backend/commands/createas.c | 26 +++++++++++------
src/backend/parser/gram.y | 16 +++++++++++
src/include/nodes/primnodes.h | 1 +
src/test/regress/expected/matview.out | 28 +++++++++++++++++++
src/test/regress/sql/matview.sql | 15 ++++++++++
6 files changed, 90 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml
b/doc/src/sgml/ref/create_materialized_view.sgml
index 5e03320eb7..1352e9de40 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -27,7 +27,7 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_nam
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [=
<replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
AS <replaceable>query</replaceable>
- [ WITH [ NO ] DATA ]
+ [ WITH [ NO | OLD ] DATA ]
</synopsis>
</refsynopsisdiv>
@@ -37,7 +37,8 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_nam
<para>
<command>CREATE MATERIALIZED VIEW</command> defines a materialized view of
a query. The query is executed and used to populate the view at the time
- the command is issued (unless <command>WITH NO DATA</command> is used) and
may be
+ the command is issued (unless <command>WITH NO DATA</command> or
+ <command>WITH OLD DATA</command> is used) and may be
refreshed later using <command>REFRESH MATERIALIZED VIEW</command>.
</para>
@@ -162,7 +163,7 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_nam
</varlistentry>
<varlistentry>
- <term><literal>WITH [ NO ] DATA</literal></term>
+ <term><literal>WITH [ NO | OLD ] DATA</literal></term>
<listitem>
<para>
This clause specifies whether or not the materialized view should be
@@ -170,6 +171,15 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_nam
flagged as unscannable and cannot be queried until <command>REFRESH
MATERIALIZED VIEW</command> is used.
</para>
+
+ <para>
+ The form <command>WITH OLD DATA</command> keeps the already stored data
+ when replacing an existing materialized view to keep it populated. For
+ newly created materialized views, this has the same effect as
+ <command>WITH DATA</command>. Use this form if you want to use
+ <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command> as it requires
+ a populated materialized view.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index cba369114b..1af714fe4a 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -332,18 +332,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt
*stmt,
/* An existing materialized view can be replaced. */
if (is_matview && into->replace)
{
- RefreshMatViewStmt *refresh;
-
/* Change the relation to match the new query and other
options. */
- (void) create_ctas_nodata(query->targetList, into);
+ address = create_ctas_nodata(query->targetList, into);
- /* Refresh the materialized view with a fake statement.
*/
- refresh = makeNode(RefreshMatViewStmt);
- refresh->relation = into->rel;
- refresh->skipData = into->skipData;
- refresh->concurrent = false;
+ /*
+ * Refresh the materialized view with a fake statement
unless we
+ * must keep the old data.
+ */
+ if (!into->keepData)
+ {
+ RefreshMatViewStmt *refresh;
+
+ refresh = makeNode(RefreshMatViewStmt);
+ refresh->relation = into->rel;
+ refresh->skipData = into->skipData;
+ refresh->concurrent = false;
+
+ address = ExecRefreshMatView(refresh, NULL,
NULL);
+ }
- return ExecRefreshMatView(refresh, NULL, NULL);
+ return address;
}
return InvalidObjectAddress;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index cb5647dcb6..9e7041f98e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4883,6 +4883,22 @@ CreateMatViewStmt:
$7->replace = true;
$$ = (Node *) ctas;
}
+ | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target
AS SelectStmt WITH OLD DATA_P
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+
+ ctas->query = $9;
+ ctas->into = $7;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = false;
+ /* cram additional flags into the
IntoClause */
+ $7->rel->relpersistence = $4;
+ $7->skipData = false;
+ $7->keepData = true;
+ $7->replace = true;
+ $$ = (Node *) ctas;
+ }
;
create_mv_target:
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index fce057a8ac..793c971133 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -168,6 +168,7 @@ typedef struct IntoClause
/* materialized view's SELECT query */
struct Query *viewQuery pg_node_attr(query_jumble_ignore);
bool skipData; /* true for WITH NO DATA */
+ bool keepData; /* true for WITH OLD DATA */
bool replace; /* replace existing matview? */
} IntoClause;
diff --git a/src/test/regress/expected/matview.out
b/src/test/regress/expected/matview.out
index d506c615da..04c2095c74 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -751,6 +751,23 @@ SELECT * FROM mvtest_replace;
3
(1 row)
+-- replace query but keep old data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 5 AS a
+ WITH OLD DATA;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 3
+(1 row)
+
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 5
+(1 row)
+
-- add column
CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
SELECT 4 AS a, 1 b;
@@ -905,3 +922,14 @@ ERROR: syntax error at or near "NOT"
LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...
^
DROP MATERIALIZED VIEW mvtest_replace;
+-- Create new matview WITH OLD DATA. This populates the new matview as if
+-- WITH DATA had been specified.
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA;
+SELECT * FROM mvtest_replace;
+ a
+----
+ 17
+(1 row)
+
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index d6a4dc4b85..91f547e9cb 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -338,6 +338,14 @@ SELECT * FROM mvtest_replace; -- error: not populated
REFRESH MATERIALIZED VIEW mvtest_replace;
SELECT * FROM mvtest_replace;
+-- replace query but keep old data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 5 AS a
+ WITH OLD DATA;
+SELECT * FROM mvtest_replace;
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+
-- add column
CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
SELECT 4 AS a, 1 b;
@@ -422,3 +430,10 @@ CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS
mvtest_replace AS
SELECT 1 AS a;
DROP MATERIALIZED VIEW mvtest_replace;
+
+-- Create new matview WITH OLD DATA. This populates the new matview as if
+-- WITH DATA had been specified.
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA;
+SELECT * FROM mvtest_replace;
--
2.48.0
signature.asc
Description: PGP signature
