agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Wienhold <[email protected]>
Subject: [PATCH v8] Add OR REPLACE option to CREATE MATERIALIZED VIEW
Date: Tue, 21 May 2024 18:35:47 +0200
Add WITH OLD DATA to keep the current data when replacing a materialized
view which is useful when running REFRESH MATERIALIZED VIEW CONCURRENTLY
afterwards.
---
.../sgml/ref/create_materialized_view.sgml | 30 +-
src/backend/commands/createas.c | 229 +++++++++++++++-
src/backend/commands/matview.c | 80 ++++++
src/backend/commands/tablecmds.c | 11 +-
src/backend/parser/gram.y | 25 ++
src/bin/psql/tab-complete.in.c | 38 ++-
src/include/commands/matview.h | 2 +
src/include/nodes/parsenodes.h | 4 +-
src/include/nodes/primnodes.h | 10 +
src/test/regress/expected/matview.out | 258 ++++++++++++++++++
src/test/regress/sql/matview.sql | 152 +++++++++++
src/tools/pgindent/typedefs.list | 1 +
12 files changed, 813 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index 62d897931c3..e6c7b7c7d37 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -21,13 +21,13 @@ 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>] [, ... ] ) ]
[ 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 MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
<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>
@@ -60,6 +61,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 +79,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, unlike when using <literal>OR REPLACE</literal>.
</para>
</listitem>
</varlistentry>
@@ -151,7 +163,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
</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
@@ -159,6 +171,14 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
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. Use
+ this form if you want to use <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command>
+ as it requires a populated materialized view. It is an error to use this
+ form when creating a new materialized view.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 6dbb831ca89..cbc384dd3f7 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -34,9 +34,11 @@
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
#include "commands/view.h"
#include "executor/execdesc.h"
#include "executor/executor.h"
+#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/queryjumble.h"
@@ -63,6 +65,7 @@ typedef struct
/* utility functions for CTAS definition creation */
static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into);
static ObjectAddress create_ctas_nodata(List *tlist, IntoClause *into);
+static ObjectAddress create_ctas_replace(List *tlist, IntoClause *into, Oid matviewOid);
/* DestReceiver routines for collecting data */
static void intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
@@ -70,6 +73,7 @@ static bool intorel_receive(TupleTableSlot *slot, DestReceiver *self);
static void intorel_shutdown(DestReceiver *self);
static void intorel_destroy(DestReceiver *self);
+static bool CreateTableAsRelReplaceable(CreateTableAsStmt *ctas);
/*
* create_ctas_internal
@@ -157,6 +161,8 @@ create_ctas_nodata(List *tlist, IntoClause *into)
List *attrList;
ListCell *t,
*lc;
+ bool is_matview = (into->viewQuery != NULL);
+ Oid matviewOid = InvalidOid;
/*
* Build list of ColumnDefs from non-junk elements of the tlist. If a
@@ -211,8 +217,146 @@ create_ctas_nodata(List *tlist, IntoClause *into)
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("too many column names were specified")));
- /* Create the relation definition using the ColumnDef list */
- return create_ctas_internal(attrList, into);
+ /* Get the existing matview to be replaced */
+ if (is_matview && into->replace)
+ (void) RangeVarGetAndCheckCreationNamespace(into->rel,
+ AccessExclusiveLock,
+ &matviewOid);
+
+ if (OidIsValid(matviewOid))
+ /* Replace the existing matview */
+ return create_ctas_replace(attrList, into, matviewOid);
+ else
+ /* Create the relation definition using the ColumnDef list */
+ return create_ctas_internal(attrList, into);
+}
+
+
+/*
+ * create_ctas_replace
+ *
+ * Internal utility used for replacing the definition of a materialized view.
+ * Caller needs to provide a list of attributes (ColumnDef nodes) and the
+ * materialized view OID.
+ */
+static ObjectAddress
+create_ctas_replace(List *attrList, IntoClause *into, Oid matviewOid)
+{
+ ObjectAddress intoRelationAddr;
+ Relation rel;
+ List *atcmds = NIL;
+ AlterTableCmd *atcmd;
+ TupleDesc descriptor;
+ Query *query;
+
+ /* Relation is already locked, but we must build a relcache entry. */
+ rel = relation_open(matviewOid, NoLock);
+
+ /* Make sure it *is* a matview. */
+ if (rel->rd_rel->relkind != RELKIND_MATVIEW)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a materialized view",
+ RelationGetRelationName(rel)));
+
+ /* Also check it's not in use already */
+ CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW");
+
+ descriptor = BuildDescForRelation(attrList);
+ checkMatviewColumns(descriptor, rel->rd_att);
+
+ /*
+ * If new attributes have been added, we must add pg_attribute entries for
+ * them. It is convenient (although overkill) to use the ALTER TABLE ADD
+ * COLUMN infrastructure for this.
+ *
+ * Note that we must do this before updating the query for the matview,
+ * since the rules system requires that the correct matview columns be in
+ * place when defining the new rules.
+ *
+ * Also note that ALTER TABLE doesn't run parse transformation on
+ * AT_AddColumnToView commands. The ColumnDef we supply must be ready to
+ * execute as-is.
+ */
+ if (list_length(attrList) > rel->rd_att->natts)
+ {
+ ListCell *c;
+
+ for_each_from(c, attrList, rel->rd_att->natts)
+ {
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_AddColumnToView;
+ atcmd->def = (Node *) lfirst(c);
+ atcmds = lappend(atcmds, atcmd);
+ }
+ }
+
+ /*
+ * Use ALTER TABLE to set access method, tablespace, and storage options.
+ * When replacing an existing matview we need to alter the relation such
+ * that the defaults apply as if they have not been specified at all by
+ * the CREATE OR REPLACE MATERIALIZED VIEW statement.
+ */
+
+ /* access method */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetAccessMethod;
+ atcmd->name = into->accessMethod
+ ? into->accessMethod : default_table_access_method;
+ atcmds = lappend(atcmds, atcmd);
+
+ /* tablespace */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetTableSpace;
+ if (into->tableSpaceName)
+ atcmd->name = into->tableSpaceName;
+ else
+ {
+ Oid spcOid;
+ char *spcName;
+
+ /*
+ * Must use the default tablespace if no explicit tablespace is
+ * specified.
+ */
+ spcOid = GetDefaultTablespace(RELPERSISTENCE_PERMANENT, false);
+ if (!OidIsValid(spcOid))
+ spcOid = MyDatabaseTableSpace;
+
+ spcName = get_tablespace_name(spcOid);
+ if (!spcName) /* should not happen */
+ elog(ERROR, "could not find tuple for tablespace %u", spcOid);
+
+ atcmd->name = spcName;
+ }
+ atcmds = lappend(atcmds, atcmd);
+
+ /* storage options */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_ReplaceRelOptions;
+ atcmd->def = (Node *) into->options;
+ atcmds = lappend(atcmds, atcmd);
+
+ /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
+ AlterTableInternal(matviewOid, atcmds, true);
+
+ /* Make the new matview columns visible */
+ CommandCounterIncrement();
+
+ relation_close(rel, NoLock);
+ ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid);
+
+ /*
+ * Replace the "view" part of the matview. StoreViewQuery scribbles on
+ * tree, so make a copy.
+ */
+ query = copyObject(into->viewQuery);
+ StoreViewQuery(intoRelationAddr.objectId, query, true);
+
+ /* Make the new matview query visible */
+ CommandCounterIncrement();
+
+ return intoRelationAddr;
}
@@ -232,8 +376,37 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
DestReceiver *dest;
ObjectAddress address;
- /* Check if the relation exists or not */
- if (CreateTableAsRelExists(stmt))
+ /*
+ * Check if the relation exists or not. An existing materialized view can
+ * be replaced.
+ */
+ if (is_matview && into->replace)
+ {
+ if (CreateTableAsRelReplaceable(stmt))
+ {
+ /* Change the relation to match the new query and other options. */
+ address = create_ctas_nodata(query->targetList, into);
+
+ /*
+ * Refresh the materialized view with a fake statement unless we
+ * must keep the old data.
+ */
+ if (into->data != WITHDATA_OLD)
+ {
+ RefreshMatViewStmt *refresh;
+
+ refresh = makeNode(RefreshMatViewStmt);
+ refresh->relation = into->rel;
+ refresh->skipData = into->skipData;
+ refresh->concurrent = false;
+
+ address = ExecRefreshMatView(refresh, pstate->p_sourcetext, qc);
+ }
+
+ return address;
+ }
+ }
+ else if (CreateTableAsRelExists(stmt))
return InvalidObjectAddress;
/*
@@ -273,6 +446,11 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
*/
if (is_matview)
{
+ if (into->data == WITHDATA_OLD)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("must not specify WITH OLD DATA when creating a new materialized view")));
+
do_refresh = !into->skipData;
into->skipData = true;
}
@@ -429,6 +607,49 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas)
return false;
}
+/*
+ * CreateTableAsRelReplaceable --- check existence of replaceable relation for
+ * CreateTableAsStmt
+ *
+ * Utility wrapper checking if the relation pending for creation in this
+ * CreateTableAsStmt query already exists or not. Returns true if the relation
+ * exists and should be replaced, otherwise false.
+ */
+static bool
+CreateTableAsRelReplaceable(CreateTableAsStmt *ctas)
+{
+ Oid nspid;
+ Oid oldrelid;
+ ObjectAddress address;
+ IntoClause *into = ctas->into;
+
+ nspid = RangeVarGetCreationNamespace(into->rel);
+
+ oldrelid = get_relname_relid(into->rel->relname, nspid);
+ if (OidIsValid(oldrelid))
+ {
+ if (!into->replace)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already exists",
+ into->rel->relname)));
+
+ /*
+ * The relation exists and 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.
+ */
+ ObjectAddressSet(address, RelationRelationId, oldrelid);
+ checkMembershipInCurrentExtension(&address);
+
+ return true;
+ }
+
+ /* Relation does not exist, it can be created */
+ return false;
+}
+
/*
* CreateIntoRelDestReceiver -- create a suitable DestReceiver object
*
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index f7d8007f796..853eae37057 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -967,3 +967,83 @@ CloseMatViewIncrementalMaintenance(void)
matview_maintenance_depth--;
Assert(matview_maintenance_depth >= 0);
}
+
+/*
+ * Verify that the columns associated with proposed new matview definition
+ * match the columns of the old matview. This is similar to equalRowTypes(),
+ * with code added to generate specific complaints. Also, we allow the new
+ * matview to have more columns than the old.
+ */
+void
+checkMatviewColumns(TupleDesc newdesc, TupleDesc olddesc)
+{
+ int i;
+
+ if (newdesc->natts < olddesc->natts)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns from materialized view"));
+ }
+
+ for (i = 0; i < olddesc->natts; i++)
+ {
+ Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
+ Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
+
+ /* XXX msg not right, but we don't support DROP COL on matview anyway */
+ if (newattr->attisdropped != oldattr->attisdropped)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns from materialized view"));
+ }
+
+ if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
+ {
+ 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."));
+ }
+
+ /*
+ * We cannot allow type, typmod, or collation to change, since these
+ * properties may be embedded in Vars of other views/rules referencing
+ * this one. Other column attributes can be ignored.
+ */
+ if (newattr->atttypid != oldattr->atttypid ||
+ newattr->atttypmod != oldattr->atttypmod)
+ {
+ 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)));
+ }
+
+ /*
+ * 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 materialized view column \"%s\" from \"%s\" to \"%s\"",
+ NameStr(oldattr->attname),
+ get_collation_name(oldattr->attcollation),
+ get_collation_name(newattr->attcollation)));
+ }
+ }
+
+ /*
+ * We ignore the constraint fields since the new matview desc can't have
+ * any constraints.
+ */
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 472db112fa7..5bf322257c5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4720,7 +4720,8 @@ 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 */
@@ -5020,8 +5021,9 @@ 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 */
@@ -5458,7 +5460,8 @@ 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/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..91e52689d7a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -250,6 +250,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
Alias *alias;
RangeVar *range;
IntoClause *into;
+ WithDataOption withdata;
WithClause *with;
InferClause *infer;
OnConflictClause *onconflict;
@@ -358,6 +359,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
opt_grant_grant_option
opt_nowait opt_if_exists opt_with_data
opt_transaction_chain
+%type <withdata> opt_with_no_or_old_data
%type <list> grant_role_opt_list
%type <defelt> grant_role_opt
%type <node> grant_role_opt_value
@@ -5030,6 +5032,13 @@ opt_with_data:
| /*EMPTY*/ { $$ = true; }
;
+opt_with_no_or_old_data:
+ WITH DATA_P { $$ = WITHDATA_DEFAULT; }
+ | WITH NO DATA_P { $$ = WITHDATA_NONE; }
+ | WITH OLD DATA_P { $$ = WITHDATA_OLD; }
+ | /*EMPTY*/ { $$ = WITHDATA_DEFAULT; }
+ ;
+
/*****************************************************************************
*
@@ -5067,6 +5076,22 @@ CreateMatViewStmt:
$8->skipData = !($11);
$$ = (Node *) ctas;
}
+ | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_no_or_old_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 == WITHDATA_NONE;
+ $7->data = $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 46b9add0604..e683e4b1b33 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2225,7 +2225,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 */
@@ -4256,28 +4256,42 @@ 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
- * methods
+ * 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
- * with "SELECT"
+ * 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/matview.h b/src/include/commands/matview.h
index 738c731c1a9..140f7e96696 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -33,4 +33,6 @@ extern DestReceiver *CreateTransientRelDestReceiver(Oid transientoid);
extern bool MatViewIncrementalMaintenanceIsEnabled(void);
+extern void checkMatviewColumns(TupleDesc newdesc, TupleDesc olddesc);
+
#endif /* MATVIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4133c404a6b..7c629d09fb1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2519,7 +2519,8 @@ 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 */
@@ -4595,5 +4596,4 @@ typedef struct WaitStmt
List *options; /* List of DefElem nodes */
} WaitStmt;
-
#endif /* PARSENODES_H */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bb05aeebee4..3b916993dbe 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -147,6 +147,14 @@ typedef struct TableFunc
ParseLoc location;
} TableFunc;
+/* WITH DATA option of CREATE MATERIALIZED VIEW */
+typedef enum WithDataOption
+{
+ WITHDATA_DEFAULT, /* WITH DATA */
+ WITHDATA_NONE, /* WITH NO DATA */
+ WITHDATA_OLD, /* WITH OLD DATA */
+} WithDataOption;
+
/*
* IntoClause - target information for SELECT INTO, CREATE TABLE AS, and
* CREATE MATERIALIZED VIEW
@@ -170,6 +178,8 @@ typedef struct IntoClause
/* materialized view's SELECT query */
struct Query *viewQuery pg_node_attr(query_jumble_ignore);
bool skipData; /* true for WITH NO DATA */
+ WithDataOption data; /* WITH [ NO | 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 0355720dfc6..e923aa4f0a7 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -699,3 +699,261 @@ 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)
+
+-- 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;
+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)
+
+-- Restore default options
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ 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 | | | heap
+(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;
+-- Clause WITH OLD DATA is not allowed when creating a new matview
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA; -- error
+ERROR: must not specify WITH OLD DATA when creating a new materialized view
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8;
+-- Add a ninth column (exceeding the old t_bits)
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8,
+ null AS c9
+ WITH OLD DATA;
+SELECT c9 FROM mvtest_replace;
+ c9
+----
+
+(1 row)
+
+-- Test constraint violation on WITH OLD DATA
+DROP MATERIALIZED VIEW mvtest_replace;
+CREATE DOMAIN mvtest_dom AS int
+ CONSTRAINT mvtest_dom_nn NOT NULL;
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1::mvtest_dom AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2::mvtest_dom AS a, 3::mvtest_dom AS b
+ WITH OLD DATA; -- error: new column "b" cannot be null
+ERROR: domain mvtest_dom does not allow null values
+SELECT a FROM mvtest_replace;
+ a
+---
+ 1
+(1 row)
+
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 934426b9ae8..abc354a13f8 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -318,3 +318,155 @@ 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;
+
+-- 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;
+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';
+-- Restore default options
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ 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;
+
+-- Clause WITH OLD DATA is not allowed when creating a new matview
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA; -- error
+
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8;
+-- Add a ninth column (exceeding the old t_bits)
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8,
+ null AS c9
+ WITH OLD DATA;
+SELECT c9 FROM mvtest_replace;
+
+-- Test constraint violation on WITH OLD DATA
+DROP MATERIALIZED VIEW mvtest_replace;
+CREATE DOMAIN mvtest_dom AS int
+ CONSTRAINT mvtest_dom_nn NOT NULL;
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1::mvtest_dom AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2::mvtest_dom AS a, 3::mvtest_dom AS b
+ WITH OLD DATA; -- error: new column "b" cannot be null
+SELECT a FROM mvtest_replace;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..2223a658899 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3471,6 +3471,7 @@ WindowStatePerFunc
WindowStatePerFuncData
WithCheckOption
WithClause
+WithDataOption
WordBoundaryNext
WordEntry
WordEntryIN
--
2.54.0
--5k2efyjyy5ysz4p6--
view thread (369+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v8] Add OR REPLACE option to CREATE MATERIALIZED VIEW
In-Reply-To: <no-message-id-1734416@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox