Sorry for the late reply but I haven't had the time to dig into this.
Here's v7 fixing the points below.
On 2025-04-05 22:37 +0200, Tom Lane wrote:
> * I think the proposal to deprecate IF NOT EXISTS is a nonstarter.
> Yeah, I don't like it much, but the standard of proof to remove
> features is amazingly high and I don't think it's been reached here.
> We're unlikely to remove IF NOT EXISTS for tables, and to the extent
> that matviews are like tables it's reasonable for them to have it too.
Yeah, I got that gist from the replies upthread and dropped that patch.
> * On the other hand, the semantics you've implemented for CREATE OR
> REPLACE are not right. The contract for any form of C.O.R. is that
> it will either fail, or produce exactly the same object definition
> that you would have gotten from plain CREATE with no conflicting
> object. The v6 code is visibly not doing that for properties such
> as tablespace --- if the command doesn't mention that, you don't
> get the default tablespace, you get whatever the old object had.
Thanks a lot. I added a test case for that and v7-0001 now restores the
default options if none are specified. Handling the default tablespace
is a bit cumbersome IMO because its name must be passed to
AlterTableInternal. With v7-0002 I moved that to ATPrepSetTableSpace as
an alternative using the empty string as stand-in for the default
tablespace. What do you think?
> * BTW, I'm inclined to think that WITH OLD DATA ought to fail
> if the command isn't replacing an existing matview. It seems
> inconsistent to silently reinterpret it as WITH DATA, just as
> silently reinterpreting "no tablespace mentioned" as "use the
> old tablespace" is inconsistent. I'm not dead set on that
> but it feels wrong.
Yes that also felt iffy to me. It just didn't occur to me to simply
raise an error in ExecCreateTableAs. Done so in v7-0003.
--
Erik Wienhold
>From 8fff179bf323ca8b4a96b977ab3193db483114cd Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Tue, 21 May 2024 18:35:47 +0200
Subject: [PATCH v7 1/3] Add OR REPLACE option to CREATE MATERIALIZED VIEW
---
.../sgml/ref/create_materialized_view.sgml | 15 +-
src/backend/commands/createas.c | 219 ++++++++++++++----
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 | 205 ++++++++++++++++
src/test/regress/sql/matview.sql | 117 ++++++++++
11 files changed, 624 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 dfd2ab8e862..1620273f965 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -24,6 +24,7 @@
*/
#include "postgres.h"
+#include "miscadmin.h"
#include "access/heapam.h"
#include "access/reloptions.h"
#include "access/tableam.h"
@@ -34,6 +35,7 @@
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
#include "commands/view.h"
#include "executor/execdesc.h"
#include "executor/executor.h"
@@ -81,55 +83,161 @@ 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 */
+ 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);
+ }
+ }
+
+ /*
+ * The following alters access method, tablespace, and storage
options.
+ * When replacing an existing matview we need to alter the
relation
+ * such that the defaults apply as if they have not been
specified at
+ * all by the CREATE statement.
+ */
+
+ /* access method */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetAccessMethod;
+ atcmd->name = into->accessMethod ? into->accessMethod :
default_table_access_method;
+ atcmds = lappend(atcmds, atcmd);
+
+ /* tablespace */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetTableSpace;
+ if (into->tableSpaceName != NULL)
+ atcmd->name = into->tableSpaceName;
+ else
+ {
+ Oid spcid;
+
+ /*
+ * Resolve the name of the default or database
tablespace because
+ * we need to specify the tablespace by name.
+ *
+ * TODO: Move that to ATPrepSetTableSpace? Must allow
AlterTableCmd.name to be NULL then.
+ */
+ spcid = GetDefaultTablespace(RELPERSISTENCE_PERMANENT,
false);
+ if (!OidIsValid(spcid))
+ spcid = MyDatabaseTableSpace;
+ atcmd->name = get_tablespace_name(spcid);
+ }
+ atcmds = lappend(atcmds, atcmd);
+
+ /* storage options */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_ReplaceRelOptions;
+ atcmd->def = (Node *) into->options;
+ atcmds = lappend(atcmds, atcmd);
+
+ 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)
@@ -137,7 +245,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();
}
@@ -234,7 +342,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
@@ -402,14 +529,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.
@@ -417,11 +545,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 cb811520c29..44dcd2c5b0d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4645,7 +4645,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 */
@@ -4940,8 +4940,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 */
@@ -5372,7 +5372,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 db43034b9db..7aaf0e37ad8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4929,6 +4929,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 1f2ca946fc5..f49f0b60c95 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2187,7 +2187,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 */
@@ -4072,28 +4072,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 86a236bd58b..74922f8ae89 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2412,7 +2412,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 6dfca3cb35b..3a0c3b06c81 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 c56c9fa3a25..56104b7ee8b 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -694,3 +694,208 @@ 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)
+
+-- restore default options
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+ a | b | relname | reloptions | spcname | amname
+---+---+----------------+------------+---------+--------
+ 5 | 1 | mvtest_replace | | | heap
+(1 row)
+
+-- can replace matview that has a dependent view
+CREATE VIEW mvtest_replace_v AS
+ SELECT * FROM mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 6 AS a, 1 AS b;
+SELECT * FROM mvtest_replace, mvtest_replace_v;
+ a | b | a | b
+---+---+---+---
+ 6 | 1 | 6 | 1
+(1 row)
+
+DROP VIEW mvtest_replace_v;
+-- index gets rebuilt when replacing with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 7 AS a, 1 AS b;
+CREATE UNIQUE INDEX ON mvtest_replace (b);
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 7 | 1
+(1 row)
+
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Index Scan using mvtest_replace_b_idx on mvtest_replace
+ Index Cond: (b = 1)
+(2 rows)
+
+SELECT * FROM mvtest_replace WHERE b = 1;
+ a | b
+---+---
+ 7 | 1
+(1 row)
+
+RESET enable_seqscan;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 8 AS a, 1 AS b;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Index Scan using mvtest_replace_b_idx on mvtest_replace
+ Index Cond: (b = 1)
+(2 rows)
+
+SELECT * FROM mvtest_replace WHERE b = 1;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+RESET enable_seqscan;
+-- cannot change column data type
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 9 AS a, 'x' AS b; -- error
+ERROR: cannot change data type of materialized view column "b" from integer
to text
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+-- cannot rename column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 10 AS a, 1 AS b2; -- error
+ERROR: cannot change name of materialized view column "b" to "b2"
+HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of
materialized view column instead.
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- cannot change column collation
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error
+ERROR: cannot change collation of materialized view column "c" from "C" to
"POSIX"
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- cannot drop column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 13 AS a, 1 AS b; -- error
+ERROR: cannot drop columns from materialized view
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- must target a matview
+CREATE VIEW mvtest_not_mv AS
+ SELECT 1 AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS
+ SELECT 1 AS a; -- error
+ERROR: "mvtest_not_mv" is not a materialized view
+DROP VIEW mvtest_not_mv;
+-- cannot use OR REPLACE with IF NOT EXISTS
+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
+ SELECT 1 AS a;
+ERROR: syntax error at or near "NOT"
+LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...
+ ^
+DROP MATERIALIZED VIEW mvtest_replace;
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 6704eeae2df..fe0cb4d25bf 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -314,3 +314,120 @@ 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';
+-- restore default options
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+
+-- can replace matview that has a dependent view
+CREATE VIEW mvtest_replace_v AS
+ SELECT * FROM mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 6 AS a, 1 AS b;
+SELECT * FROM mvtest_replace, mvtest_replace_v;
+DROP VIEW mvtest_replace_v;
+
+-- index gets rebuilt when replacing with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 7 AS a, 1 AS b;
+CREATE UNIQUE INDEX ON mvtest_replace (b);
+SELECT * FROM mvtest_replace;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+SELECT * FROM mvtest_replace WHERE b = 1;
+RESET enable_seqscan;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 8 AS a, 1 AS b;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+SELECT * FROM mvtest_replace WHERE b = 1;
+RESET enable_seqscan;
+
+-- cannot change column data type
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 9 AS a, 'x' AS b; -- error
+SELECT * FROM mvtest_replace;
+
+-- cannot rename column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 10 AS a, 1 AS b2; -- error
+SELECT * FROM mvtest_replace;
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;
+SELECT * FROM mvtest_replace;
+
+-- cannot change column collation
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error
+SELECT * FROM mvtest_replace;
+
+-- cannot drop column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 13 AS a, 1 AS b; -- error
+SELECT * FROM mvtest_replace;
+
+-- must target a matview
+CREATE VIEW mvtest_not_mv AS
+ SELECT 1 AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS
+ SELECT 1 AS a; -- error
+DROP VIEW mvtest_not_mv;
+
+-- cannot use OR REPLACE with IF NOT EXISTS
+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
+ SELECT 1 AS a;
+
+DROP MATERIALIZED VIEW mvtest_replace;
--
2.50.1
>From 21beeed4c89aa6e77e2e81de87f57cc33feaa7ef Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Tue, 5 Aug 2025 00:05:43 +0200
Subject: [PATCH v7 2/3] Handle default tablespace in AlterTableInternal
Move handling of default tablespace for CREATE OR REPLACE MATERIALIZED
VIEW from create_ctas_internal to ATPrepSetTableSpace. It feels cleaner
that way in my opinion by not having to resolve the tablespace name just
to pass it to AlterTableInternal. The default table space is passed as
empty string to AlterTableInternal.
---
src/backend/commands/createas.c | 21 ++-------------------
src/backend/commands/tablecmds.c | 14 ++++++++++++--
2 files changed, 14 insertions(+), 21 deletions(-)
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 1620273f965..30ca0a21903 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -24,7 +24,6 @@
*/
#include "postgres.h"
-#include "miscadmin.h"
#include "access/heapam.h"
#include "access/reloptions.h"
#include "access/tableam.h"
@@ -35,7 +34,6 @@
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
-#include "commands/tablespace.h"
#include "commands/view.h"
#include "executor/execdesc.h"
#include "executor/executor.h"
@@ -160,23 +158,8 @@ create_ctas_internal(List *attrList, IntoClause *into)
/* tablespace */
atcmd = makeNode(AlterTableCmd);
atcmd->subtype = AT_SetTableSpace;
- if (into->tableSpaceName != NULL)
- atcmd->name = into->tableSpaceName;
- else
- {
- Oid spcid;
-
- /*
- * Resolve the name of the default or database
tablespace because
- * we need to specify the tablespace by name.
- *
- * TODO: Move that to ATPrepSetTableSpace? Must allow
AlterTableCmd.name to be NULL then.
- */
- spcid = GetDefaultTablespace(RELPERSISTENCE_PERMANENT,
false);
- if (!OidIsValid(spcid))
- spcid = MyDatabaseTableSpace;
- atcmd->name = get_tablespace_name(spcid);
- }
+ /* use empty string to specify default tablespace */
+ atcmd->name = into->tableSpaceName ? into->tableSpaceName : "";
atcmds = lappend(atcmds, atcmd);
/* storage options */
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 44dcd2c5b0d..6ec0b87f841 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -16580,8 +16580,18 @@ ATPrepSetTableSpace(AlteredTableInfo *tab, Relation
rel, const char *tablespacen
{
Oid tablespaceId;
- /* Check that the tablespace exists */
- tablespaceId = get_tablespace_oid(tablespacename, false);
+ if (tablespacename != NULL && tablespacename[0] == '\0')
+ {
+ /* Use default tablespace if name is empty string */
+ tablespaceId =
GetDefaultTablespace(rel->rd_rel->relpersistence, rel->rd_rel->relispartition);
+ if (!OidIsValid(tablespaceId))
+ tablespaceId = MyDatabaseTableSpace;
+ }
+ else
+ {
+ /* Check that the tablespace exists */
+ tablespaceId = get_tablespace_oid(tablespacename, false);
+ }
/* Check permissions except when moving to database's default */
if (OidIsValid(tablespaceId) && tablespaceId != MyDatabaseTableSpace)
--
2.50.1
>From 01e073f5f1af495ba0559c08ad5ae1902a3ea593 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Fri, 26 Jul 2024 23:33:15 +0200
Subject: [PATCH v7 3/3] Add WITH OLD DATA to CREATE OR REPLACE MATERIALIZED
VIEW
This keeps the matview populated when replacing its definition.
---
.../sgml/ref/create_materialized_view.sgml | 15 ++++++++--
src/backend/commands/createas.c | 29 +++++++++++++------
src/backend/parser/gram.y | 16 ++++++++++
src/include/nodes/primnodes.h | 1 +
src/test/regress/expected/matview.out | 22 ++++++++++++++
src/test/regress/sql/matview.sql | 13 +++++++++
6 files changed, 84 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..5b82af2ac70 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,14 @@ 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. Use
+ this form if you want to use <command>REFRESH MATERIALIZED VIEW
CONCURRENTLY</command>
+ as it requires a populated materialized view. It is an error to use this
+ form when creating a new materialized view.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 30ca0a21903..ac2491fe01c 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -329,18 +329,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
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;
- /* Refresh the materialized view with a fake statement.
*/
- 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;
@@ -383,6 +391,9 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt
*stmt,
*/
if (is_matview)
{
+ if (into->keepData)
+ elog(ERROR, "must not specify WITH OLD DATA when
creating a new materialized view");
+
do_refresh = !into->skipData;
into->skipData = true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7aaf0e37ad8..e51ce2701be 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4944,6 +4944,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 3a0c3b06c81..e4c52ab05b4 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 56104b7ee8b..39b3a7c941c 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -731,6 +731,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;
@@ -899,3 +916,8 @@ ERROR: syntax error at or near "NOT"
LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...
^
DROP MATERIALIZED VIEW mvtest_replace;
+-- Clause WITH OLD DATA is not allowed when creating a new matview.
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA; -- error
+ERROR: must not specify WITH OLD DATA when creating a new materialized view
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index fe0cb4d25bf..fb2e5f4b589 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;
@@ -431,3 +439,8 @@ CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS
mvtest_replace AS
SELECT 1 AS a;
DROP MATERIALIZED VIEW mvtest_replace;
+
+-- Clause WITH OLD DATA is not allowed when creating a new matview.
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA; -- error
--
2.50.1
