On 2026-02-03 01:19 +0100, Paul A Jungwirth wrote:
> Yan Haibo and I reviewed this patch for the Patch Review Workshop.
Thank you both for your review. Sorry about my late reply but it took
me some time to address your points with the attached v8 and I only had
so little time available.
> The patch applies, compiles, and passes tests.
>
> From v7-0001-Add-OR-REPLACE-option-to-CREATE-MATERIALIZED-VIEW.patch:
>
> ```
> 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
> @@ -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>
> ```
>
> "Unless" flows strangely here, at first connoting that it will work
> with IF NOT EXISTS, but then "instead" retracts that suggestion.
> Perhaps "unlike when using OR REPLACE".
Done.
> ```
> 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;
> ```
>
> It seems like there is very little shared in this function between the
> old code and new. Should replacing a matview just have a different
> function altogether?
I factored out the replace logic into create_ctas_replace leaving the
existing create_ctas_internal now untouched. create_ctas_replace is
called from create_ctas_nodata where we check whether we're replacing an
existing matview.
> ```
> + /* 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;
> + }
> ```
>
> We didn't call RangeVarGetAndCheckCreationNamespace before, but now we
> call it whether you say `OR REPLACE` or not. That seems suspicious.
> Why do we need to call it even when you don't ask to replace an
> existing matview? We only use matviewOid if we're replacing, and we
> only lock the relation if we're replacing. So probably this should be
> `if (is_matview && into->replace)` (which makes this function seem
> even more strongly that it should be split into two).
RangeVarGetAndCheckCreationNamespace is now called in create_ctas_nodata
when is_matview && into->replace is true.
> ```
> + rel = relation_open(matviewOid, NoLock);
> ```
>
> The stanzas in this branch need comments. The code strongly resembles
> DefineVirtualRelation, but without the comments. For instance in this
> line, I wasn't sure why were opening the relation with no lock. This
> comment from DefineVirtualRelation is very helpful:
>
> ```
> /* Relation is already locked, but we must build a relcache entry. */`
> ```
>
> The other lost comments would be helpful too.
I added these comments now.
> ```
> + /* 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);
> + }
> + }
> ```
>
> Can we use list_nth_cell to start at the right position and avoid the
> manual skipping? (So the loop would be for instead of foreach.) Or
> even better, for_each_from looks perfect for this.
Now simplified with for_each_from.
> ```
> + /* access method */
> + atcmd = makeNode(AlterTableCmd);
> + atcmd->subtype = AT_SetAccessMethod;
> + atcmd->name = into->accessMethod ? into->accessMethod :
> default_table_access_method;
> + atcmds = lappend(atcmds, atcmd);
> ```
>
> Just to confirm, this is a noop if the access method is already the
> same? And likewise with the other AT subcommands?
It's a noop for access method and tablespace, but not for storage
options.
AT_SetAccessMethod: Checked by ATPrepSetAccessMethod to skip phase 3
AT_SetTableSpace: Checked by ATExecSetTableSpace using
CheckRelationTableSpaceMove in phase 3
AT_ReplaceRelOptions: Always replaces the current storage options
(ATExecSetRelOptions with transformRelOptions) in phase 2 regardless of
whether there are changes.
> ```
> @@ -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;
> + }
> ```
>
> Maybe an `else` for the other case? Having parallel indentation
> communicates the parallel execution.
Done.
> ```
> @@ -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)
> ```
>
> Changing the contract without changing the function comment seems wrong.
>
> But is this really factored correctly? Maybe the check should happen
> in the caller instead.
>
> Does this require a change to ExplainOneUtility, which also calls this
> function? At least we probably need to handle OR REPLACE there.
I added the static CreateTableAsRelReplaceable to leave the existing
CreateTableAsRelExists untouched. The former now only checks whether
the relation exists and can be replaced. The logic in ExecCreateTableAs
is changed to first check is_matview && into->replace.
> ```
> 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")));
> + }
> ```
>
> Instead of repeating so much, perhaps `errmsg("cannot drop columns
> from %s", is_matview ? "materialized view" : "view")`? Or since that
> is probably bad for translators, at least this:
>
> ```
> errmsg(is_matview
> ? "cannot drop columns from materialized view"
> : "cannot drop columns from view")
> ```
>
> Likewise with more error messages in this function. (There are a lot of them.)
When formatting it like errmsg(is_matview ? "" : "") the PO files only
pick up the first of the two messages. Pulling out the ternary like
this would work:
is_matview ? errmsg(...) : errmsg(...)
But instead I added function checkMatviewColumns to matview.c based on
checkViewColumns to avoid the is_matview flag altogether. Both
functions still implement the same checks. The very last comment in
checkViewColumns mentions leaving column defaults in place. Since
default values can't be set on matview columns I figured that it's best
to provide these checks as separate functions to avoid confusion.
> The parser changes seem fine (see below re the third patch though).
>
> Tab completion seems fine.
>
> From v7-0002-Handle-default-tablespace-in-AlterTableInternal.patch:
>
> These changes should get rolled into the previous patch. I think you
> broke them out to allow considering them separately, although your
> patches would be simpler if they were the first patch in the series
> then.
Yes, I thought separate patches would be easier to review. v8 is now a
single patch.
> ```
> + /* use empty string to specify default tablespace */
> + atcmd->name = into->tableSpaceName ? into->tableSpaceName : "";
> atcmds = lappend(atcmds, atcmd);
> ```
>
> We didn't love using "" as a magic string to signal the
> AT_SetTableSpace subcommand. At least it should be a named constant,
> but maybe a separate struct member is better.
I changed the logic that has been factored out into create_ctas_replace
to now resolve the name of the default tablespace and store that in
atcmd->name.
> ```
> - /* 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)
> ```
>
> This seems like something that should happen during analysis, but I
> know we are less strict for utility statements.
>
> Are there any locking issues with calling GetDefaultTablespace here
> instead of earlier? Why does ATPrepSetTableSpace take a lockmode but
> not use it? Can the table space get dropped in the middle of the
> command? But this is probably handled already. Do we need the lock the
> *old* tablespace?
In case of ALTER TABLE SET TABLESPACE the old and new tablespaces aren't
locked either as far as I can see. Maybe I'm missing something in
tablecmds.c because I would expect something similar to
RangeVarGetAndCheckCreationNamespace taking an AccessShareLock on the
namespace.
ATPrepSetTableSpace takes the lockmode parameter since 2dbbda02e7e which
added lockmode to other ATPrepXXX functions as well without using it. I
guess this was done to provide a consistent API across the various ALTER
TABLE subcommands.
> What about partitions?
Table partitions do not apply to matviews.
> From v7-0003-Add-WITH-OLD-DATA-to-CREATE-OR-REPLACE-MATERIALIZ.patch:
>
> ```
> @@ -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;
> }
> ```
>
> Should this be ereport? I guess the current parser productions should
> prevent us from reaching this code though. So perhaps a comment?
Right. Replaced with ereport and a localizable error message.
> ```
> 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;
> + }
> ;
> ```
>
> This is the fourth production for minor variations of
> CreateMatViewStmt, which seems like a lot. Perhaps instead of
> opt_with_data we add a new production opt_with_no_or_old_data that can
> return 3 alteratives (WITH [{NO|OLD}] DATA)?
I've added production opt_with_no_or_old_data as suggested, backed by
the new enum WithDataOption in parsenodes.h. With that, I replaced
.keepData from v7 with just .data holding that new enum.
Field .skipData is still in place but I think it should be migrated to
that new .data field. I haven't done that in v8, though, to keep the
patch focused.
> ```
> +-- 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;
> +
> ```
>
> We would like to see a test adding a new column while using WITH OLD
> DATA. It is safe because every heap tuple knows how many attributes it
> holds (and I tested it), but I did wonder if anyone had been tempted
> to call fastgetattr directly for matviews. (Before your feature, it
> would have been safe, I think.) Here is the test I did:
>
> ```
> create materialized view mvnulls as
> select 1 c1, 2 c2, 3 c3, 4 c4, 5 c5, 6 c6, 7 c7, null c8;
> -- add a ninth column (exceeding the old t_bits):
> create or replace materialized view mvnulls as
> select 1 c1, 2 c2, 3 c3, 4 c4, 5 c5, 6 c6, 7 c7, null c8, null c9
> with old data;
> select c9 from mvnulls;
> ```
That test case is now included.
> Another thing to think about and test: what if while using WITH OLD
> DATA the new matview definition violates a unique index? Or a domain?
> Are there any other constraints that can be applied to matviews to
> consider?
Adding a column of a domain type with NOT NULL constraint can indeed
cause WITH OLD DATA to fail since we're setting new columns to NULL in
that case. I've added the following test case:
CREATE DOMAIN mvtest_dom AS int
CONSTRAINT mvtest_dom_nn NOT NULL;
CREATE MATERIALIZED VIEW mvtest_replace AS
SELECT 1::mvtest_dom AS a;
CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
SELECT 2::mvtest_dom AS a, 3::mvtest_dom AS b
WITH OLD DATA;
But besides that I don't see how keeping the old data could violate a
unique index or any other constraint on a domain type. During the
execution of the matview replacement the unique index and domain types
remain unaltered. So the old data will still satisfy any pre-existing
constraints.
And changing the type of an existing column is not possible. So you
can't switch to a different domain type with more restrictive
constraints while keeping the old data. For example, this won't work
since column "a" can't be changed from mvtest_dom1 to mvtest_dom2 on
principle:
CREATE DOMAIN mvtest_dom1 AS int
CONSTRAINT mvtest_dom1_check CHECK (VALUE = 1);
CREATE DOMAIN mvtest_dom2 AS int
CONSTRAINT mvtest_dom2_check CHECK (VALUE = 2);
CREATE MATERIALIZED VIEW mvtest_replace AS
SELECT 1::mvtest_dom1 AS a;
CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
SELECT 2::mvtest_dom2 AS a
WITH OLD DATA;
--
Erik Wienhold
>From dcf65b62993f5ec831d670761e37004e9eec8e94 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <[email protected]>
Date: Tue, 21 May 2024 18:35:47 +0200
Subject: [PATCH v8] Add OR REPLACE option to CREATE MATERIALIZED VIEW
Add WITH OLD DATA to keep the current data when replacing a materialized
view which is useful when running REFRESH MATERIALIZED VIEW CONCURRENTLY
afterwards.
---
.../sgml/ref/create_materialized_view.sgml | 30 +-
src/backend/commands/createas.c | 229 +++++++++++++++-
src/backend/commands/matview.c | 80 ++++++
src/backend/commands/tablecmds.c | 11 +-
src/backend/parser/gram.y | 25 ++
src/bin/psql/tab-complete.in.c | 38 ++-
src/include/commands/matview.h | 2 +
src/include/nodes/parsenodes.h | 4 +-
src/include/nodes/primnodes.h | 10 +
src/test/regress/expected/matview.out | 258 ++++++++++++++++++
src/test/regress/sql/matview.sql | 152 +++++++++++
src/tools/pgindent/typedefs.list | 1 +
12 files changed, 813 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml
b/doc/src/sgml/ref/create_materialized_view.sgml
index 62d897931c3..e6c7b7c7d37 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -21,13 +21,13 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
+CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
[ (<replaceable>column_name</replaceable> [, ...] ) ]
[ USING <replaceable class="parameter">method</replaceable> ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [=
<replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
AS <replaceable>query</replaceable>
- [ WITH [ NO ] DATA ]
+ [ WITH [ NO | OLD ] DATA ]
</synopsis>
</refsynopsisdiv>
@@ -37,7 +37,8 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
<para>
<command>CREATE MATERIALIZED VIEW</command> defines a materialized view of
a query. The query is executed and used to populate the view at the time
- the command is issued (unless <command>WITH NO DATA</command> is used) and
may be
+ the command is issued (unless <command>WITH NO DATA</command> or
+ <command>WITH OLD DATA</command> is used) and may be
refreshed later using <command>REFRESH MATERIALIZED VIEW</command>.
</para>
@@ -60,6 +61,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>OR REPLACE</literal></term>
+ <listitem>
+ <para>
+ Replaces a materialized view if it already exists.
+ Specifying <literal>OR REPLACE</literal> together with
+ <literal>IF NOT EXISTS</literal> is an error.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
@@ -67,7 +79,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
Do not throw an error if a materialized view with the same name already
exists. A notice is issued in this case. Note that there is no guarantee
that the existing materialized view is anything like the one that would
- have been created.
+ have been created, unlike when using <literal>OR REPLACE</literal>.
</para>
</listitem>
</varlistentry>
@@ -151,7 +163,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
</varlistentry>
<varlistentry>
- <term><literal>WITH [ NO ] DATA</literal></term>
+ <term><literal>WITH [ NO | OLD ] DATA</literal></term>
<listitem>
<para>
This clause specifies whether or not the materialized view should be
@@ -159,6 +171,14 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
<replaceable>table_name</replaceable>
flagged as unscannable and cannot be queried until <command>REFRESH
MATERIALIZED VIEW</command> is used.
</para>
+
+ <para>
+ The form <command>WITH OLD DATA</command> keeps the already stored data
+ when replacing an existing materialized view to keep it populated. Use
+ this form if you want to use <command>REFRESH MATERIALIZED VIEW
CONCURRENTLY</command>
+ as it requires a populated materialized view. It is an error to use this
+ form when creating a new materialized view.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 6dbb831ca89..cbc384dd3f7 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -34,9 +34,11 @@
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
#include "commands/view.h"
#include "executor/execdesc.h"
#include "executor/executor.h"
+#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/queryjumble.h"
@@ -63,6 +65,7 @@ typedef struct
/* utility functions for CTAS definition creation */
static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into);
static ObjectAddress create_ctas_nodata(List *tlist, IntoClause *into);
+static ObjectAddress create_ctas_replace(List *tlist, IntoClause *into, Oid
matviewOid);
/* DestReceiver routines for collecting data */
static void intorel_startup(DestReceiver *self, int operation, TupleDesc
typeinfo);
@@ -70,6 +73,7 @@ static bool intorel_receive(TupleTableSlot *slot,
DestReceiver *self);
static void intorel_shutdown(DestReceiver *self);
static void intorel_destroy(DestReceiver *self);
+static bool CreateTableAsRelReplaceable(CreateTableAsStmt *ctas);
/*
* create_ctas_internal
@@ -157,6 +161,8 @@ create_ctas_nodata(List *tlist, IntoClause *into)
List *attrList;
ListCell *t,
*lc;
+ bool is_matview = (into->viewQuery != NULL);
+ Oid matviewOid = InvalidOid;
/*
* Build list of ColumnDefs from non-junk elements of the tlist. If a
@@ -211,8 +217,146 @@ create_ctas_nodata(List *tlist, IntoClause *into)
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("too many column names were
specified")));
- /* Create the relation definition using the ColumnDef list */
- return create_ctas_internal(attrList, into);
+ /* Get the existing matview to be replaced */
+ if (is_matview && into->replace)
+ (void) RangeVarGetAndCheckCreationNamespace(into->rel,
+
AccessExclusiveLock,
+
&matviewOid);
+
+ if (OidIsValid(matviewOid))
+ /* Replace the existing matview */
+ return create_ctas_replace(attrList, into, matviewOid);
+ else
+ /* Create the relation definition using the ColumnDef list */
+ return create_ctas_internal(attrList, into);
+}
+
+
+/*
+ * create_ctas_replace
+ *
+ * Internal utility used for replacing the definition of a materialized view.
+ * Caller needs to provide a list of attributes (ColumnDef nodes) and the
+ * materialized view OID.
+ */
+static ObjectAddress
+create_ctas_replace(List *attrList, IntoClause *into, Oid matviewOid)
+{
+ ObjectAddress intoRelationAddr;
+ Relation rel;
+ List *atcmds = NIL;
+ AlterTableCmd *atcmd;
+ TupleDesc descriptor;
+ Query *query;
+
+ /* Relation is already locked, but we must build a relcache entry. */
+ rel = relation_open(matviewOid, NoLock);
+
+ /* Make sure it *is* a matview. */
+ if (rel->rd_rel->relkind != RELKIND_MATVIEW)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a materialized view",
+ RelationGetRelationName(rel)));
+
+ /* Also check it's not in use already */
+ CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW");
+
+ descriptor = BuildDescForRelation(attrList);
+ checkMatviewColumns(descriptor, rel->rd_att);
+
+ /*
+ * If new attributes have been added, we must add pg_attribute entries
for
+ * them. It is convenient (although overkill) to use the ALTER TABLE
ADD
+ * COLUMN infrastructure for this.
+ *
+ * Note that we must do this before updating the query for the matview,
+ * since the rules system requires that the correct matview columns be
in
+ * place when defining the new rules.
+ *
+ * Also note that ALTER TABLE doesn't run parse transformation on
+ * AT_AddColumnToView commands. The ColumnDef we supply must be ready
to
+ * execute as-is.
+ */
+ if (list_length(attrList) > rel->rd_att->natts)
+ {
+ ListCell *c;
+
+ for_each_from(c, attrList, rel->rd_att->natts)
+ {
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_AddColumnToView;
+ atcmd->def = (Node *) lfirst(c);
+ atcmds = lappend(atcmds, atcmd);
+ }
+ }
+
+ /*
+ * Use ALTER TABLE to set access method, tablespace, and storage
options.
+ * When replacing an existing matview we need to alter the relation such
+ * that the defaults apply as if they have not been specified at all by
+ * the CREATE OR REPLACE MATERIALIZED VIEW statement.
+ */
+
+ /* access method */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetAccessMethod;
+ atcmd->name = into->accessMethod
+ ? into->accessMethod : default_table_access_method;
+ atcmds = lappend(atcmds, atcmd);
+
+ /* tablespace */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_SetTableSpace;
+ if (into->tableSpaceName)
+ atcmd->name = into->tableSpaceName;
+ else
+ {
+ Oid spcOid;
+ char *spcName;
+
+ /*
+ * Must use the default tablespace if no explicit tablespace is
+ * specified.
+ */
+ spcOid = GetDefaultTablespace(RELPERSISTENCE_PERMANENT, false);
+ if (!OidIsValid(spcOid))
+ spcOid = MyDatabaseTableSpace;
+
+ spcName = get_tablespace_name(spcOid);
+ if (!spcName) /* should not happen */
+ elog(ERROR, "could not find tuple for tablespace %u",
spcOid);
+
+ atcmd->name = spcName;
+ }
+ atcmds = lappend(atcmds, atcmd);
+
+ /* storage options */
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_ReplaceRelOptions;
+ atcmd->def = (Node *) into->options;
+ atcmds = lappend(atcmds, atcmd);
+
+ /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
+ AlterTableInternal(matviewOid, atcmds, true);
+
+ /* Make the new matview columns visible */
+ CommandCounterIncrement();
+
+ relation_close(rel, NoLock);
+ ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid);
+
+ /*
+ * Replace the "view" part of the matview. StoreViewQuery scribbles on
+ * tree, so make a copy.
+ */
+ query = copyObject(into->viewQuery);
+ StoreViewQuery(intoRelationAddr.objectId, query, true);
+
+ /* Make the new matview query visible */
+ CommandCounterIncrement();
+
+ return intoRelationAddr;
}
@@ -232,8 +376,37 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt
*stmt,
DestReceiver *dest;
ObjectAddress address;
- /* Check if the relation exists or not */
- if (CreateTableAsRelExists(stmt))
+ /*
+ * Check if the relation exists or not. An existing materialized view
can
+ * be replaced.
+ */
+ if (is_matview && into->replace)
+ {
+ if (CreateTableAsRelReplaceable(stmt))
+ {
+ /* Change the relation to match the new query and other
options. */
+ address = create_ctas_nodata(query->targetList, into);
+
+ /*
+ * Refresh the materialized view with a fake statement
unless we
+ * must keep the old data.
+ */
+ if (into->data != WITHDATA_OLD)
+ {
+ RefreshMatViewStmt *refresh;
+
+ refresh = makeNode(RefreshMatViewStmt);
+ refresh->relation = into->rel;
+ refresh->skipData = into->skipData;
+ refresh->concurrent = false;
+
+ address = ExecRefreshMatView(refresh,
pstate->p_sourcetext, qc);
+ }
+
+ return address;
+ }
+ }
+ else if (CreateTableAsRelExists(stmt))
return InvalidObjectAddress;
/*
@@ -273,6 +446,11 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt
*stmt,
*/
if (is_matview)
{
+ if (into->data == WITHDATA_OLD)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("must not specify WITH OLD DATA
when creating a new materialized view")));
+
do_refresh = !into->skipData;
into->skipData = true;
}
@@ -429,6 +607,49 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas)
return false;
}
+/*
+ * CreateTableAsRelReplaceable --- check existence of replaceable relation for
+ * CreateTableAsStmt
+ *
+ * Utility wrapper checking if the relation pending for creation in this
+ * CreateTableAsStmt query already exists or not. Returns true if the relation
+ * exists and should be replaced, otherwise false.
+ */
+static bool
+CreateTableAsRelReplaceable(CreateTableAsStmt *ctas)
+{
+ Oid nspid;
+ Oid oldrelid;
+ ObjectAddress address;
+ IntoClause *into = ctas->into;
+
+ nspid = RangeVarGetCreationNamespace(into->rel);
+
+ oldrelid = get_relname_relid(into->rel->relname, nspid);
+ if (OidIsValid(oldrelid))
+ {
+ if (!into->replace)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already
exists",
+ into->rel->relname)));
+
+ /*
+ * The relation exists and OR REPLACE has been specified.
+ *
+ * If we are in an extension script, insist that the
pre-existing
+ * object be a member of the extension, to avoid security risks.
+ */
+ ObjectAddressSet(address, RelationRelationId, oldrelid);
+ checkMembershipInCurrentExtension(&address);
+
+ return true;
+ }
+
+ /* Relation does not exist, it can be created */
+ return false;
+}
+
/*
* CreateIntoRelDestReceiver -- create a suitable DestReceiver object
*
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index f7d8007f796..853eae37057 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -967,3 +967,83 @@ CloseMatViewIncrementalMaintenance(void)
matview_maintenance_depth--;
Assert(matview_maintenance_depth >= 0);
}
+
+/*
+ * Verify that the columns associated with proposed new matview definition
+ * match the columns of the old matview. This is similar to equalRowTypes(),
+ * with code added to generate specific complaints. Also, we allow the new
+ * matview to have more columns than the old.
+ */
+void
+checkMatviewColumns(TupleDesc newdesc, TupleDesc olddesc)
+{
+ int i;
+
+ if (newdesc->natts < olddesc->natts)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns from materialized
view"));
+ }
+
+ for (i = 0; i < olddesc->natts; i++)
+ {
+ Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
+ Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
+
+ /* XXX msg not right, but we don't support DROP COL on matview
anyway */
+ if (newattr->attisdropped != oldattr->attisdropped)
+ {
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot drop columns from
materialized view"));
+ }
+
+ if (strcmp(NameStr(newattr->attname),
NameStr(oldattr->attname)) != 0)
+ {
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change name of
materialized view column \"%s\" to \"%s\"",
+ NameStr(oldattr->attname),
+ NameStr(newattr->attname)),
+ errhint("Use ALTER MATERIALIZED VIEW
... RENAME COLUMN ... to change name of materialized view column instead."));
+ }
+
+ /*
+ * We cannot allow type, typmod, or collation to change, since
these
+ * properties may be embedded in Vars of other views/rules
referencing
+ * this one. Other column attributes can be ignored.
+ */
+ if (newattr->atttypid != oldattr->atttypid ||
+ newattr->atttypmod != oldattr->atttypmod)
+ {
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change data type of
materialized view column \"%s\" from %s to %s",
+ NameStr(oldattr->attname),
+
format_type_with_typemod(oldattr->atttypid,
+
oldattr->atttypmod),
+
format_type_with_typemod(newattr->atttypid,
+
newattr->atttypmod)));
+ }
+
+ /*
+ * At this point, attcollations should be both valid or both
invalid,
+ * so applying get_collation_name unconditionally should be
fine.
+ */
+ if (newattr->attcollation != oldattr->attcollation)
+ {
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot change collation of
materialized view column \"%s\" from \"%s\" to \"%s\"",
+ NameStr(oldattr->attname),
+
get_collation_name(oldattr->attcollation),
+
get_collation_name(newattr->attcollation)));
+ }
+ }
+
+ /*
+ * We ignore the constraint fields since the new matview desc can't have
+ * any constraints.
+ */
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 472db112fa7..5bf322257c5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4720,7 +4720,8 @@ AlterTableGetLockLevel(List *cmds)
* Subcommands that may be visible to
concurrent SELECTs
*/
case AT_DropColumn: /* change visible to SELECT */
- case AT_AddColumnToView: /* CREATE VIEW */
+ case AT_AddColumnToView: /* via CREATE OR REPLACE
+
* [MATERIALIZED] VIEW */
case AT_DropOids: /* used to equiv to DropColumn
*/
case AT_EnableAlwaysRule: /* may change SELECT
rules */
case AT_EnableReplicaRule: /* may change SELECT
rules */
@@ -5020,8 +5021,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* Recursion occurs during execution phase */
pass = AT_PASS_ADD_COL;
break;
- case AT_AddColumnToView: /* add column via CREATE OR
REPLACE VIEW */
- ATSimplePermissions(cmd->subtype, rel, ATT_VIEW);
+ case AT_AddColumnToView: /* via CREATE OR REPLACE
[MATERIALIZED]
+ * VIEW
*/
+ ATSimplePermissions(cmd->subtype, rel, ATT_VIEW |
ATT_MATVIEW);
ATPrepAddColumn(wqueue, rel, recurse, recursing, true,
cmd,
lockmode, context);
/* Recursion occurs during execution phase */
@@ -5458,7 +5460,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
switch (cmd->subtype)
{
case AT_AddColumn: /* ADD COLUMN */
- case AT_AddColumnToView: /* add column via CREATE OR
REPLACE VIEW */
+ case AT_AddColumnToView: /* via CREATE OR REPLACE
[MATERIALIZED]
+ * VIEW
*/
address = ATExecAddColumn(wqueue, tab, rel, &cmd,
cmd->recurse, false,
lockmode, cur_pass, context);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..91e52689d7a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -250,6 +250,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
Alias *alias;
RangeVar *range;
IntoClause *into;
+ WithDataOption withdata;
WithClause *with;
InferClause *infer;
OnConflictClause *onconflict;
@@ -358,6 +359,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
opt_grant_grant_option
opt_nowait opt_if_exists opt_with_data
opt_transaction_chain
+%type <withdata> opt_with_no_or_old_data
%type <list> grant_role_opt_list
%type <defelt> grant_role_opt
%type <node> grant_role_opt_value
@@ -5030,6 +5032,13 @@ opt_with_data:
| /*EMPTY*/
{ $$ = true; }
;
+opt_with_no_or_old_data:
+ WITH DATA_P
{ $$ = WITHDATA_DEFAULT; }
+ | WITH NO DATA_P
{ $$ = WITHDATA_NONE; }
+ | WITH OLD DATA_P
{ $$ = WITHDATA_OLD; }
+ | /*EMPTY*/
{ $$ = WITHDATA_DEFAULT; }
+ ;
+
/*****************************************************************************
*
@@ -5067,6 +5076,22 @@ CreateMatViewStmt:
$8->skipData = !($11);
$$ = (Node *) ctas;
}
+ | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target
AS SelectStmt opt_with_no_or_old_data
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+
+ ctas->query = $9;
+ ctas->into = $7;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = false;
+ /* cram additional flags into the
IntoClause */
+ $7->rel->relpersistence = $4;
+ $7->skipData = $10 == WITHDATA_NONE;
+ $7->data = $10;
+ $7->replace = true;
+ $$ = (Node *) ctas;
+ }
;
create_mv_target:
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 46b9add0604..e683e4b1b33 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2225,7 +2225,7 @@ match_previous_words(int pattern_id,
/* complete with something you can create or replace */
else if (TailMatches("CREATE", "OR", "REPLACE"))
COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE",
"VIEW",
- "AGGREGATE", "TRANSFORM", "TRIGGER");
+ "AGGREGATE", "TRANSFORM", "TRIGGER",
"MATERIALIZED VIEW");
/* DROP, but not DROP embedded in other commands */
/* complete with something you can drop */
@@ -4256,28 +4256,42 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("SELECT");
/* CREATE MATERIALIZED VIEW */
- else if (Matches("CREATE", "MATERIALIZED"))
+ else if (Matches("CREATE", "MATERIALIZED") ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED"))
COMPLETE_WITH("VIEW");
- /* Complete CREATE MATERIALIZED VIEW <name> with AS or USING */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+
+ /*
+ * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS or
+ * USING
+ */
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny))
COMPLETE_WITH("AS", "USING");
/*
- * Complete CREATE MATERIALIZED VIEW <name> USING with list of access
- * methods
+ * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> USING with
list
+ * of access methods
*/
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING"))
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING") ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "USING"))
COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
- /* Complete CREATE MATERIALIZED VIEW <name> USING <access method> with
AS */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING",
MatchAny))
+
+ /*
+ * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> USING <access
+ * method> with AS
+ */
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING",
MatchAny) ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "USING", MatchAny))
COMPLETE_WITH("AS");
/*
- * Complete CREATE MATERIALIZED VIEW <name> [USING <access method> ] AS
- * with "SELECT"
+ * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> [USING
<access
+ * method> ] AS with "SELECT"
*/
else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||
- Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny,
"USING", MatchAny, "AS"))
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "AS") ||
+ Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny,
"USING", MatchAny, "AS") ||
+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED",
"VIEW", MatchAny, "USING", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
/* CREATE EVENT TRIGGER */
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index 738c731c1a9..140f7e96696 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -33,4 +33,6 @@ extern DestReceiver *CreateTransientRelDestReceiver(Oid
transientoid);
extern bool MatViewIncrementalMaintenanceIsEnabled(void);
+extern void checkMatviewColumns(TupleDesc newdesc, TupleDesc olddesc);
+
#endif /* MATVIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4133c404a6b..7c629d09fb1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2519,7 +2519,8 @@ typedef struct AlterTableStmt
typedef enum AlterTableType
{
AT_AddColumn, /* add column */
- AT_AddColumnToView, /* implicitly via CREATE OR
REPLACE VIEW */
+ AT_AddColumnToView, /* implicitly via CREATE OR
REPLACE
+ *
[MATERIALIZED] VIEW */
AT_ColumnDefault, /* alter column default */
AT_CookedColumnDefault, /* add a pre-cooked column default */
AT_DropNotNull, /* alter column drop not null */
@@ -4595,5 +4596,4 @@ typedef struct WaitStmt
List *options; /* List of DefElem nodes */
} WaitStmt;
-
#endif /* PARSENODES_H */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bb05aeebee4..3b916993dbe 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -147,6 +147,14 @@ typedef struct TableFunc
ParseLoc location;
} TableFunc;
+/* WITH DATA option of CREATE MATERIALIZED VIEW */
+typedef enum WithDataOption
+{
+ WITHDATA_DEFAULT, /* WITH DATA */
+ WITHDATA_NONE, /* WITH NO DATA */
+ WITHDATA_OLD, /* WITH OLD DATA */
+} WithDataOption;
+
/*
* IntoClause - target information for SELECT INTO, CREATE TABLE AS, and
* CREATE MATERIALIZED VIEW
@@ -170,6 +178,8 @@ typedef struct IntoClause
/* materialized view's SELECT query */
struct Query *viewQuery pg_node_attr(query_jumble_ignore);
bool skipData; /* true for WITH NO DATA */
+ WithDataOption data; /* WITH [ NO | OLD ] DATA */
+ bool replace; /* replace existing matview? */
} IntoClause;
diff --git a/src/test/regress/expected/matview.out
b/src/test/regress/expected/matview.out
index 0355720dfc6..e923aa4f0a7 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -699,3 +699,261 @@ NOTICE: relation "matview_ine_tab" already exists,
skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+--
+-- Test CREATE OR REPLACE MATERIALIZED VIEW
+--
+-- Matview does not already exist
+DROP MATERIALIZED VIEW IF EXISTS mvtest_replace;
+NOTICE: materialized view "mvtest_replace" does not exist, skipping
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS a;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 1
+(1 row)
+
+-- Replace query with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2 AS a;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 2
+(1 row)
+
+-- Replace query without data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 3 AS a
+ WITH NO DATA;
+SELECT * FROM mvtest_replace; -- error: not populated
+ERROR: materialized view "mvtest_replace" has not been populated
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 3
+(1 row)
+
+-- Replace query but keep old data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 5 AS a
+ WITH OLD DATA;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 3
+(1 row)
+
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+ a
+---
+ 5
+(1 row)
+
+-- Add column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 4 AS a, 1 b;
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 4 | 1
+(1 row)
+
+-- Replace table options
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+ a | b | relname | reloptions | spcname | amname
+---+---+----------------+------------+---------+--------
+ 4 | 1 | mvtest_replace | | | heap
+(1 row)
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ USING heap2
+ WITH (fillfactor = 50)
+ TABLESPACE regress_tblspace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+ a | b | relname | reloptions | spcname | amname
+---+---+----------------+-----------------+------------------+--------
+ 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2
+(1 row)
+
+-- Restore default options
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+ a | b | relname | reloptions | spcname | amname
+---+---+----------------+------------+---------+--------
+ 5 | 1 | mvtest_replace | | | heap
+(1 row)
+
+-- Can replace matview that has a dependent view
+CREATE VIEW mvtest_replace_v AS
+ SELECT * FROM mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 6 AS a, 1 AS b;
+SELECT * FROM mvtest_replace, mvtest_replace_v;
+ a | b | a | b
+---+---+---+---
+ 6 | 1 | 6 | 1
+(1 row)
+
+DROP VIEW mvtest_replace_v;
+-- Index gets rebuilt when replacing with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 7 AS a, 1 AS b;
+CREATE UNIQUE INDEX ON mvtest_replace (b);
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 7 | 1
+(1 row)
+
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Index Scan using mvtest_replace_b_idx on mvtest_replace
+ Index Cond: (b = 1)
+(2 rows)
+
+SELECT * FROM mvtest_replace WHERE b = 1;
+ a | b
+---+---
+ 7 | 1
+(1 row)
+
+RESET enable_seqscan;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 8 AS a, 1 AS b;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Index Scan using mvtest_replace_b_idx on mvtest_replace
+ Index Cond: (b = 1)
+(2 rows)
+
+SELECT * FROM mvtest_replace WHERE b = 1;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+RESET enable_seqscan;
+-- Cannot change column data type
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 9 AS a, 'x' AS b; -- error
+ERROR: cannot change data type of materialized view column "b" from integer
to text
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+-- Cannot rename column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 10 AS a, 1 AS b2; -- error
+ERROR: cannot change name of materialized view column "b" to "b2"
+HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of
materialized view column instead.
+SELECT * FROM mvtest_replace;
+ a | b
+---+---
+ 8 | 1
+(1 row)
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- Cannot change column collation
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error
+ERROR: cannot change collation of materialized view column "c" from "C" to
"POSIX"
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- Cannot drop column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 13 AS a, 1 AS b; -- error
+ERROR: cannot drop columns from materialized view
+SELECT * FROM mvtest_replace;
+ a | b | c
+----+---+---
+ 11 | 1 | y
+(1 row)
+
+-- Must target a matview
+CREATE VIEW mvtest_not_mv AS
+ SELECT 1 AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS
+ SELECT 1 AS a; -- error
+ERROR: "mvtest_not_mv" is not a materialized view
+DROP VIEW mvtest_not_mv;
+-- Cannot use OR REPLACE with IF NOT EXISTS
+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
+ SELECT 1 AS a;
+ERROR: syntax error at or near "NOT"
+LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...
+ ^
+DROP MATERIALIZED VIEW mvtest_replace;
+-- Clause WITH OLD DATA is not allowed when creating a new matview
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA; -- error
+ERROR: must not specify WITH OLD DATA when creating a new materialized view
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8;
+-- Add a ninth column (exceeding the old t_bits)
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8,
+ null AS c9
+ WITH OLD DATA;
+SELECT c9 FROM mvtest_replace;
+ c9
+----
+
+(1 row)
+
+-- Test constraint violation on WITH OLD DATA
+DROP MATERIALIZED VIEW mvtest_replace;
+CREATE DOMAIN mvtest_dom AS int
+ CONSTRAINT mvtest_dom_nn NOT NULL;
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1::mvtest_dom AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2::mvtest_dom AS a, 3::mvtest_dom AS b
+ WITH OLD DATA; -- error: new column "b" cannot be null
+ERROR: domain mvtest_dom does not allow null values
+SELECT a FROM mvtest_replace;
+ a
+---
+ 1
+(1 row)
+
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 934426b9ae8..abc354a13f8 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -318,3 +318,155 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF,
BUFFERS OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
+
+--
+-- Test CREATE OR REPLACE MATERIALIZED VIEW
+--
+
+-- Matview does not already exist
+DROP MATERIALIZED VIEW IF EXISTS mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS a;
+SELECT * FROM mvtest_replace;
+
+-- Replace query with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2 AS a;
+SELECT * FROM mvtest_replace;
+
+-- Replace query without data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 3 AS a
+ WITH NO DATA;
+SELECT * FROM mvtest_replace; -- error: not populated
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+
+-- Replace query but keep old data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 5 AS a
+ WITH OLD DATA;
+SELECT * FROM mvtest_replace;
+REFRESH MATERIALIZED VIEW mvtest_replace;
+SELECT * FROM mvtest_replace;
+
+-- Add column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 4 AS a, 1 b;
+SELECT * FROM mvtest_replace;
+
+-- Replace table options
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ USING heap2
+ WITH (fillfactor = 50)
+ TABLESPACE regress_tblspace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+-- Restore default options
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace
+ AS SELECT 5 AS a, 1 AS b;
+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname
+ FROM mvtest_replace m
+ CROSS JOIN pg_class c
+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
+ LEFT JOIN pg_am a ON a.oid = c.relam
+ WHERE c.relname = 'mvtest_replace';
+
+-- Can replace matview that has a dependent view
+CREATE VIEW mvtest_replace_v AS
+ SELECT * FROM mvtest_replace;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 6 AS a, 1 AS b;
+SELECT * FROM mvtest_replace, mvtest_replace_v;
+DROP VIEW mvtest_replace_v;
+
+-- Index gets rebuilt when replacing with data
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 7 AS a, 1 AS b;
+CREATE UNIQUE INDEX ON mvtest_replace (b);
+SELECT * FROM mvtest_replace;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+SELECT * FROM mvtest_replace WHERE b = 1;
+RESET enable_seqscan;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 8 AS a, 1 AS b;
+SET enable_seqscan = off; -- force index scan
+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;
+SELECT * FROM mvtest_replace WHERE b = 1;
+RESET enable_seqscan;
+
+-- Cannot change column data type
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 9 AS a, 'x' AS b; -- error
+SELECT * FROM mvtest_replace;
+
+-- Cannot rename column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 10 AS a, 1 AS b2; -- error
+SELECT * FROM mvtest_replace;
+
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;
+SELECT * FROM mvtest_replace;
+
+-- Cannot change column collation
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error
+SELECT * FROM mvtest_replace;
+
+-- Cannot drop column
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 13 AS a, 1 AS b; -- error
+SELECT * FROM mvtest_replace;
+
+-- Must target a matview
+CREATE VIEW mvtest_not_mv AS
+ SELECT 1 AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS
+ SELECT 1 AS a; -- error
+DROP VIEW mvtest_not_mv;
+
+-- Cannot use OR REPLACE with IF NOT EXISTS
+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
+ SELECT 1 AS a;
+
+DROP MATERIALIZED VIEW mvtest_replace;
+
+-- Clause WITH OLD DATA is not allowed when creating a new matview
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 17 AS a
+ WITH OLD DATA; -- error
+
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8;
+-- Add a ninth column (exceeding the old t_bits)
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4,
+ 5 AS c5, 6 AS c6, 7 AS c7, null AS c8,
+ null AS c9
+ WITH OLD DATA;
+SELECT c9 FROM mvtest_replace;
+
+-- Test constraint violation on WITH OLD DATA
+DROP MATERIALIZED VIEW mvtest_replace;
+CREATE DOMAIN mvtest_dom AS int
+ CONSTRAINT mvtest_dom_nn NOT NULL;
+CREATE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 1::mvtest_dom AS a;
+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
+ SELECT 2::mvtest_dom AS a, 3::mvtest_dom AS b
+ WITH OLD DATA; -- error: new column "b" cannot be null
+SELECT a FROM mvtest_replace;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..2223a658899 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3471,6 +3471,7 @@ WindowStatePerFunc
WindowStatePerFuncData
WithCheckOption
WithClause
+WithDataOption
WordBoundaryNext
WordEntry
WordEntryIN
--
2.54.0
