pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Erik Wienhold <ewie@ewie.name>
To: Said Assemlal <sassemlal@neurorx.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: CREATE OR REPLACE MATERIALIZED VIEW
Date: Mon, 10 Mar 2025 03:46:05 +0100
Message-ID: <bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name> (raw)
In-Reply-To: <64495e31-0a91-4894-87f8-31451ee69f44@ewie.name>
References: <3c86a16f-4272-4df3-9959-70a9a7d88a71@ewie.name>
	<d9db9bdb-593d-4cef-9274-0db640fcff42@neurorx.com>
	<e4382244-6b55-450b-a4f0-32959056ade4@ewie.name>
	<e55c4930-f788-4bb0-a684-743621d6cfc5@neurorx.com>
	<7afe68b0-f983-4a9f-a1b4-32188cebbf38@ewie.name>
	<b74736cf-a4b4-4c32-8df1-d08abe9a14ed@ewie.name>
	<d4bf3ccf-5c2b-448d-ad16-59dd6fbe2a2e@ewie.name>
	<64495e31-0a91-4894-87f8-31451ee69f44@ewie.name>

The attached v6 fixes the build.  Somehow I missed testing with
--with-cassert the whole time and it turned that out I forgot to pass
queryString to ExecRefreshMatView.

-- 
Erik Wienhold
From 6ec126d8da5ca80f93ea8a58e07d654f5e21ef6d Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 21 May 2024 18:35:47 +0200
Subject: [PATCH v6 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 62d897931c3..5e03320eb73 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 44b4665ccd3..9f37ed7f177 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, pstate->p_sourcetext, qc);
+		}
+
 		return InvalidObjectAddress;
+	}
 
 	/*
 	 * Create the tuple receiver object and insert info it will need
@@ -401,14 +516,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.
@@ -416,11 +532,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 59156a1c1f6..49713bc0422 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4605,7 +4605,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 */
@@ -4900,8 +4900,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 */
@@ -5332,7 +5332,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 6f0301555e0..53d4cacc4c1 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 271ae26cbaf..d2edc460378 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4895,6 +4895,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 8432be641ac..e862b135a73 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2162,7 +2162,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 */
@@ -4008,28 +4008,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 c41f51b161c..95290f0a1c4 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 23c9e3c5abf..5a25ba96ab3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2402,7 +2402,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 d0576da3e25..e800d13a6e0 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -169,6 +169,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 54939ecc6b0..c0cf8ecbffc 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 6704eeae2df..d6a4dc4b857 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.1
From 232b1439f6cb5d931540901b8e582bd4e6551abf Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 28 May 2024 02:19:53 +0200
Subject: [PATCH v6 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 d2edc460378..8be790eaccf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4894,6 +4894,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 d5388a1fecf..9a1e0f76587 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 c0cf8ecbffc..d4b42da1ddf 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.1
From eba3f5904ff97217a07628948fd3c707f5d9e94d Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Fri, 26 Jul 2024 23:33:15 +0200
Subject: [PATCH v6 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 5e03320eb73..1352e9de409 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 9f37ed7f177..0ed44b246bd 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, pstate->p_sourcetext, qc);
+			}
 
-			return ExecRefreshMatView(refresh, pstate->p_sourcetext, qc);
+			return address;
 		}
 
 		return InvalidObjectAddress;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8be790eaccf..e405268f413 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4924,6 +4924,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 e800d13a6e0..5290e6c0e8a 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -169,6 +169,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 d4b42da1ddf..f0f52dd1c6c 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 d6a4dc4b857..91f547e9cb8 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.1

Attachments:

  [text/plain] v6-0001-Add-CREATE-OR-REPLACE-MATERIALIZED-VIEW.patch (33.1K, ../bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name/2-v6-0001-Add-CREATE-OR-REPLACE-MATERIALIZED-VIEW.patch)
  download | inline diff:
From 6ec126d8da5ca80f93ea8a58e07d654f5e21ef6d Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 21 May 2024 18:35:47 +0200
Subject: [PATCH v6 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 62d897931c3..5e03320eb73 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 44b4665ccd3..9f37ed7f177 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, pstate->p_sourcetext, qc);
+		}
+
 		return InvalidObjectAddress;
+	}
 
 	/*
 	 * Create the tuple receiver object and insert info it will need
@@ -401,14 +516,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.
@@ -416,11 +532,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 59156a1c1f6..49713bc0422 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4605,7 +4605,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 */
@@ -4900,8 +4900,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 */
@@ -5332,7 +5332,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 6f0301555e0..53d4cacc4c1 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 271ae26cbaf..d2edc460378 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4895,6 +4895,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 8432be641ac..e862b135a73 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2162,7 +2162,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 */
@@ -4008,28 +4008,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 c41f51b161c..95290f0a1c4 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 23c9e3c5abf..5a25ba96ab3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2402,7 +2402,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 d0576da3e25..e800d13a6e0 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -169,6 +169,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 54939ecc6b0..c0cf8ecbffc 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 6704eeae2df..d6a4dc4b857 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.1

  [text/plain] v6-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch (11.0K, ../bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name/3-v6-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch)
  download | inline diff:
From 232b1439f6cb5d931540901b8e582bd4e6551abf Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 28 May 2024 02:19:53 +0200
Subject: [PATCH v6 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 d2edc460378..8be790eaccf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4894,6 +4894,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 d5388a1fecf..9a1e0f76587 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 c0cf8ecbffc..d4b42da1ddf 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.1

  [text/plain] v6-0003-Replace-matview-WITH-OLD-DATA.patch (7.7K, ../bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name/4-v6-0003-Replace-matview-WITH-OLD-DATA.patch)
  download | inline diff:
From eba3f5904ff97217a07628948fd3c707f5d9e94d Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Fri, 26 Jul 2024 23:33:15 +0200
Subject: [PATCH v6 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 5e03320eb73..1352e9de409 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 9f37ed7f177..0ed44b246bd 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, pstate->p_sourcetext, qc);
+			}
 
-			return ExecRefreshMatView(refresh, pstate->p_sourcetext, qc);
+			return address;
 		}
 
 		return InvalidObjectAddress;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8be790eaccf..e405268f413 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4924,6 +4924,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 e800d13a6e0..5290e6c0e8a 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -169,6 +169,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 d4b42da1ddf..f0f52dd1c6c 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 d6a4dc4b857..91f547e9cb8 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.1

view thread (29+ messages)  latest in thread

Message-ID: <bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name>
Permalink:  ../bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name/
Also on:    postgresql.org/message-id/bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name

copy link · copy postgr.es · copy git am

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: pgsql-hackers@postgresql.org
  Cc: ewie@ewie.name, sassemlal@neurorx.com
  Subject: Re: CREATE OR REPLACE MATERIALIZED VIEW
  In-Reply-To: <bf64dad9-eaaa-4911-b447-13acee89ea80@ewie.name>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox