From: Erik Wienhold <ewie@ewie.name>
To: Said Assemlal <sassemlal@neurorx.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: CREATE OR REPLACE MATERIALIZED VIEW
Date: Sat, 27 Jul 2024 02:45:15 +0200
Message-ID: <7afe68b0-f983-4a9f-a1b4-32188cebbf38@ewie.name> (raw)
In-Reply-To: <e55c4930-f788-4bb0-a684-743621d6cfc5@neurorx.com>
References: <3c86a16f-4272-4df3-9959-70a9a7d88a71@ewie.name>
<d9db9bdb-593d-4cef-9274-0db640fcff42@neurorx.com>
<e4382244-6b55-450b-a4f0-32959056ade4@ewie.name>
<e55c4930-f788-4bb0-a684-743621d6cfc5@neurorx.com>
On 2024-07-12 16:49 +0200, Said Assemlal wrote:
> > My initial idea, while writing the patch, was that one could replace the
> > matview without populating it and then run the concurrent refresh, like
> > this:
> >
> > CREATE OR REPLACE MATERIALIZED VIEW foo AS ... WITH NO DATA;
> > REFRESH MATERIALIZED VIEW CONCURRENTLY foo;
> >
> > But that won't work because concurrent refresh requires an already
> > populated matview.
> >
> > Right now the patch either populates the replaced matview or leaves it
> > in an unscannable state. Technically, it's also possible to skip the
> > refresh and leave the old data in place, perhaps by specifying
> > WITH *OLD* DATA. New columns would just be null. Of course you can't
> > tell if you got stale data without knowing how the matview was replaced.
> > Thoughts?
>
> I believe the expectation is to get materialized views updated whenever it
> gets replaced so likely to confuse users ?
I agree, that could be confusing -- unless it's well documented. The
attached 0003 implements WITH OLD DATA and states in the docs that this
is intended to be used before a concurrent refresh.
Patch 0001 now covers all matview cases in psql's tab completion. I
missed some of them with v1.
--
Erik
Attachments:
[text/x-diff] v2-0001-Add-CREATE-OR-REPLACE-MATERIALIZED-VIEW.patch (31.8K, ../7afe68b0-f983-4a9f-a1b4-32188cebbf38@ewie.name/2-v2-0001-Add-CREATE-OR-REPLACE-MATERIALIZED-VIEW.patch)
download | inline diff:
From a529a00af40be611e6bed49fe0341b7435a72930 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 21 May 2024 18:35:47 +0200
Subject: [PATCH v2 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW
---
.../sgml/ref/create_materialized_view.sgml | 15 +-
src/backend/commands/createas.c | 207 ++++++++++++++----
src/backend/commands/tablecmds.c | 8 +-
src/backend/commands/view.c | 106 ++++++---
src/backend/parser/gram.y | 15 ++
src/bin/psql/tab-complete.c | 15 +-
src/include/commands/view.h | 3 +
src/include/nodes/parsenodes.h | 2 +-
src/include/nodes/primnodes.h | 1 +
src/test/regress/expected/matview.out | 191 ++++++++++++++++
src/test/regress/sql/matview.sql | 108 +++++++++
11 files changed, 582 insertions(+), 89 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgmlindex 0d2fea2b97..b5a8e3441a 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.cindex 2c8a93b6e5..c5d78252a1 100644--- a/src/backend/commands/createas.c+++ b/src/backend/commands/createas.c@@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self);
static ObjectAddress
create_ctas_internal(List *attrList, IntoClause *into)
{
- CreateStmt *create = makeNode(CreateStmt);- bool is_matview;+ bool is_matview,+ replace = false;
char relkind;
- Datum toast_options;- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;+ Oid matviewOid = InvalidOid;
ObjectAddress intoRelationAddr;
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
is_matview = (into->viewQuery != NULL);
relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION;
- /*- * Create the target relation by faking up a CREATE TABLE parsetree and- * passing it to DefineRelation.- */- create->relation = into->rel;- create->tableElts = attrList;- create->inhRelations = NIL;- create->ofTypename = NULL;- create->constraints = NIL;- create->options = into->options;- create->oncommit = into->onCommit;- create->tablespacename = into->tableSpaceName;- create->if_not_exists = false;- create->accessMethod = into->accessMethod;+ /* Check if an existing materialized view needs to be replaced. */+ if (is_matview)+ {+ LOCKMODE lockmode;- /*- * Create the relation. (This will error out if there's an existing view,- * so we don't need more code to complain if "replace" is false.)- */- intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL);+ lockmode = into->replace ? AccessExclusiveLock : NoLock;+ (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode,+ &matviewOid);+ replace = OidIsValid(matviewOid) && into->replace;+ }- /*- * If necessary, create a TOAST table for the target table. Note that- * NewRelationCreateToastTable ends with CommandCounterIncrement(), so- * that the TOAST table will be visible for insertion.- */- CommandCounterIncrement();+ if (is_matview && replace)+ {+ Relation rel;+ List *atcmds = NIL;+ AlterTableCmd *atcmd;+ TupleDesc descriptor;++ rel = relation_open(matviewOid, NoLock);++ if (rel->rd_rel->relkind != RELKIND_MATVIEW)+ ereport(ERROR,+ errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is not a materialized view",+ RelationGetRelationName(rel)));++ CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW");++ descriptor = BuildDescForRelation(attrList);+ checkViewColumns(descriptor, rel->rd_att, true);++ /* Add new attributes via ALTER TABLE. */+ if (list_length(attrList) > rel->rd_att->natts)+ {+ ListCell *c;+ int skip = rel->rd_att->natts;++ foreach(c, attrList)+ {+ if (skip > 0)+ {+ skip--;+ continue;+ }+ atcmd = makeNode(AlterTableCmd);+ atcmd->subtype = AT_AddColumnToView;+ atcmd->def = (Node *) lfirst(c);+ atcmds = lappend(atcmds, atcmd);+ }+ }++ /* Set access method via ALTER TABLE. */+ if (into->accessMethod != NULL)+ {+ atcmd = makeNode(AlterTableCmd);+ atcmd->subtype = AT_SetAccessMethod;+ atcmd->name = into->accessMethod;+ atcmds = lappend(atcmds, atcmd);+ }++ /* Set tablespace via ALTER TABLE. */+ if (into->tableSpaceName != NULL)+ {+ atcmd = makeNode(AlterTableCmd);+ atcmd->subtype = AT_SetTableSpace;+ atcmd->name = into->tableSpaceName;+ atcmds = lappend(atcmds, atcmd);+ }++ /* Set storage parameters via ALTER TABLE. */+ if (into->options != NIL)+ {+ atcmd = makeNode(AlterTableCmd);+ atcmd->subtype = AT_ReplaceRelOptions;+ atcmd->def = (Node *) into->options;+ atcmds = lappend(atcmds, atcmd);+ }++ if (atcmds != NIL)+ {+ AlterTableInternal(matviewOid, atcmds, true);+ CommandCounterIncrement();+ }++ relation_close(rel, NoLock);++ ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid);+ }+ else+ {+ CreateStmt *create = makeNode(CreateStmt);+ Datum toast_options;+ static char *validnsps[] = HEAP_RELOPT_NAMESPACES;++ /*+ * Create the target relation by faking up a CREATE TABLE parsetree+ * and passing it to DefineRelation.+ */+ create->relation = into->rel;+ create->tableElts = attrList;+ create->inhRelations = NIL;+ create->ofTypename = NULL;+ create->constraints = NIL;+ create->options = into->options;+ create->oncommit = into->onCommit;+ create->tablespacename = into->tableSpaceName;+ create->if_not_exists = false;+ create->accessMethod = into->accessMethod;++ /*+ * Create the relation. (This will error out if there's an existing+ * view, so we don't need more code to complain if "replace" is+ * false.)+ */+ intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL,+ NULL);- /* parse and validate reloptions for the toast table */- toast_options = transformRelOptions((Datum) 0,- create->options,- "toast",- validnsps,- true, false);+ /*+ * If necessary, create a TOAST table for the target table. Note that+ * NewRelationCreateToastTable ends with CommandCounterIncrement(), so+ * that the TOAST table will be visible for insertion.+ */+ CommandCounterIncrement();++ /* parse and validate reloptions for the toast table */+ toast_options = transformRelOptions((Datum) 0,+ create->options,+ "toast",+ validnsps,+ true, false);- (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);+ (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);- NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options);+ NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options);+ }
/* Create the "view" part of a materialized view. */
if (is_matview)
@@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into)
/* StoreViewQuery scribbles on tree, so make a copy */
Query *query = (Query *) copyObject(into->viewQuery);
- StoreViewQuery(intoRelationAddr.objectId, query, false);+ StoreViewQuery(intoRelationAddr.objectId, query, replace);
CommandCounterIncrement();
}
@@ -234,7 +330,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
/* Check if the relation exists or not */
if (CreateTableAsRelExists(stmt))
+ {+ /* An existing materialized view can be replaced. */+ if (is_matview && into->replace)+ {+ RefreshMatViewStmt *refresh;++ /* Change the relation to match the new query and other options. */+ (void) create_ctas_nodata(query->targetList, into);++ /* Refresh the materialized view with a fake statement. */+ refresh = makeNode(RefreshMatViewStmt);+ refresh->relation = into->rel;+ refresh->skipData = into->skipData;+ refresh->concurrent = false;++ return ExecRefreshMatView(refresh, NULL, NULL, NULL);+ }+
return InvalidObjectAddress;
+ }
/*
* Create the tuple receiver object and insert info it will need
@@ -395,14 +510,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.
@@ -410,11 +526,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.cindex 0b2a52463f..5274daaf3a 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -4484,7 +4484,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 */
@@ -4795,8 +4795,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 */
@@ -5220,7 +5220,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.cindex fdad833832..76532aa35d 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
*
@@ -130,7 +128,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.yindex a043fd4c66..ed477806d1 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -4831,6 +4831,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.c b/src/bin/psql/tab-complete.cindex 891face1b6..f3291e79d8 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -1811,7 +1811,7 @@ psql_completion(const char *text, int start, int end)
/* 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 */
@@ -3619,13 +3619,16 @@ psql_completion(const char *text, int start, int end)
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 */- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))+ /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS */+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) ||+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny))
COMPLETE_WITH("AS");
- /* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS"))+ /* Complete "CREATE [ OR REPLACE ] MATERIALIZED VIEW <sth> AS with "SELECT" */+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||+ Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
/* CREATE EVENT TRIGGER */
diff --git a/src/include/commands/view.h b/src/include/commands/view.hindex d2d8588989..7eacdaaceb 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.hindex 85a62b538e..cbc4b608e3 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -2351,7 +2351,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.hindex ea47652adb..4b0ee5d10d 100644--- a/src/include/nodes/primnodes.h+++ b/src/include/nodes/primnodes.h@@ -168,6 +168,7 @@ typedef struct IntoClause
/* materialized view's SELECT query */
Node *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.outindex 038ab73517..e2e2a13396 100644--- a/src/test/regress/expected/matview.out+++ b/src/test/regress/expected/matview.out@@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists, skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+--+-- test CREATE OR REPLACE MATERIALIZED VIEW+--+-- matview does not already exist+DROP MATERIALIZED VIEW IF EXISTS mvtest_replace;+NOTICE: materialized view "mvtest_replace" does not exist, skipping+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 1 AS a;+SELECT * FROM mvtest_replace;+ a +---+ 1+(1 row)++-- replace query with data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 2 AS a;+SELECT * FROM mvtest_replace;+ a +---+ 2+(1 row)++-- replace query without data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 3 AS a+ WITH NO DATA;+SELECT * FROM mvtest_replace; -- error: not populated+ERROR: materialized view "mvtest_replace" has not been populated+HINT: Use the REFRESH MATERIALIZED VIEW command.+REFRESH MATERIALIZED VIEW mvtest_replace;+SELECT * FROM mvtest_replace;+ a +---+ 3+(1 row)++-- add column+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 4 AS a, 1 b;+SELECT * FROM mvtest_replace;+ a | b +---+---+ 4 | 1+(1 row)++-- replace table options+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname+ FROM mvtest_replace m+ CROSS JOIN pg_class c+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace+ LEFT JOIN pg_am a ON a.oid = c.relam+ WHERE c.relname = 'mvtest_replace';+ a | b | relname | reloptions | spcname | amname +---+---+----------------+------------+---------+--------+ 4 | 1 | mvtest_replace | | | heap+(1 row)++CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace+ USING heap2+ WITH (fillfactor = 50)+ TABLESPACE regress_tblspace+ AS SELECT 5 AS a, 1 AS b;+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname+ FROM mvtest_replace m+ CROSS JOIN pg_class c+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace+ LEFT JOIN pg_am a ON a.oid = c.relam+ WHERE c.relname = 'mvtest_replace';+ a | b | relname | reloptions | spcname | amname +---+---+----------------+-----------------+------------------+--------+ 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2+(1 row)++-- can replace matview that has a dependent view+CREATE VIEW mvtest_replace_v AS+ SELECT * FROM mvtest_replace;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 6 AS a, 1 AS b;+SELECT * FROM mvtest_replace, mvtest_replace_v;+ a | b | a | b +---+---+---+---+ 6 | 1 | 6 | 1+(1 row)++DROP VIEW mvtest_replace_v;+-- index gets rebuilt when replacing with data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 7 AS a, 1 AS b;+CREATE UNIQUE INDEX ON mvtest_replace (b);+SELECT * FROM mvtest_replace;+ a | b +---+---+ 7 | 1+(1 row)++SET enable_seqscan = off; -- force index scan+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;+ QUERY PLAN +---------------------------------------------------------+ Index Scan using mvtest_replace_b_idx on mvtest_replace+ Index Cond: (b = 1)+(2 rows)++SELECT * FROM mvtest_replace WHERE b = 1;+ a | b +---+---+ 7 | 1+(1 row)++RESET enable_seqscan;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 8 AS a, 1 AS b;+SET enable_seqscan = off; -- force index scan+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;+ QUERY PLAN +---------------------------------------------------------+ Index Scan using mvtest_replace_b_idx on mvtest_replace+ Index Cond: (b = 1)+(2 rows)++SELECT * FROM mvtest_replace WHERE b = 1;+ a | b +---+---+ 8 | 1+(1 row)++RESET enable_seqscan;+-- cannot change column data type+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 9 AS a, 'x' AS b; -- error+ERROR: cannot change data type of materialized view column "b" from integer to text+SELECT * FROM mvtest_replace;+ a | b +---+---+ 8 | 1+(1 row)++-- cannot rename column+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 10 AS a, 1 AS b2; -- error+ERROR: cannot change name of materialized view column "b" to "b2"+HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead.+SELECT * FROM mvtest_replace;+ a | b +---+---+ 8 | 1+(1 row)++CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;+SELECT * FROM mvtest_replace;+ a | b | c +----+---+---+ 11 | 1 | y+(1 row)++-- cannot change column collation+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error+ERROR: cannot change collation of materialized view column "c" from "C" to "POSIX"+SELECT * FROM mvtest_replace;+ a | b | c +----+---+---+ 11 | 1 | y+(1 row)++-- cannot drop column+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 13 AS a, 1 AS b; -- error+ERROR: cannot drop columns from materialized view+SELECT * FROM mvtest_replace;+ a | b | c +----+---+---+ 11 | 1 | y+(1 row)++-- must target a matview+CREATE VIEW mvtest_not_mv AS+ SELECT 1 AS a;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS+ SELECT 1 AS a; -- error+ERROR: "mvtest_not_mv" is not a materialized view+DROP VIEW mvtest_not_mv;+-- cannot use OR REPLACE with IF NOT EXISTS+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS+ SELECT 1 AS a;+ERROR: syntax error at or near "NOT"+LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...+ ^+DROP MATERIALIZED VIEW mvtest_replace;diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sqlindex b74ee305e0..c12f0243c9 100644--- a/src/test/regress/sql/matview.sql+++ b/src/test/regress/sql/matview.sql@@ -314,3 +314,111 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
++--+-- test CREATE OR REPLACE MATERIALIZED VIEW+--++-- matview does not already exist+DROP MATERIALIZED VIEW IF EXISTS mvtest_replace;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 1 AS a;+SELECT * FROM mvtest_replace;++-- replace query with data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 2 AS a;+SELECT * FROM mvtest_replace;++-- replace query without data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 3 AS a+ WITH NO DATA;+SELECT * FROM mvtest_replace; -- error: not populated+REFRESH MATERIALIZED VIEW mvtest_replace;+SELECT * FROM mvtest_replace;++-- add column+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 4 AS a, 1 b;+SELECT * FROM mvtest_replace;++-- replace table options+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname+ FROM mvtest_replace m+ CROSS JOIN pg_class c+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace+ LEFT JOIN pg_am a ON a.oid = c.relam+ WHERE c.relname = 'mvtest_replace';+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace+ USING heap2+ WITH (fillfactor = 50)+ TABLESPACE regress_tblspace+ AS SELECT 5 AS a, 1 AS b;+SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname+ FROM mvtest_replace m+ CROSS JOIN pg_class c+ LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace+ LEFT JOIN pg_am a ON a.oid = c.relam+ WHERE c.relname = 'mvtest_replace';++-- can replace matview that has a dependent view+CREATE VIEW mvtest_replace_v AS+ SELECT * FROM mvtest_replace;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 6 AS a, 1 AS b;+SELECT * FROM mvtest_replace, mvtest_replace_v;+DROP VIEW mvtest_replace_v;++-- index gets rebuilt when replacing with data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 7 AS a, 1 AS b;+CREATE UNIQUE INDEX ON mvtest_replace (b);+SELECT * FROM mvtest_replace;+SET enable_seqscan = off; -- force index scan+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;+SELECT * FROM mvtest_replace WHERE b = 1;+RESET enable_seqscan;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 8 AS a, 1 AS b;+SET enable_seqscan = off; -- force index scan+EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1;+SELECT * FROM mvtest_replace WHERE b = 1;+RESET enable_seqscan;++-- cannot change column data type+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 9 AS a, 'x' AS b; -- error+SELECT * FROM mvtest_replace;++-- cannot rename column+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 10 AS a, 1 AS b2; -- error+SELECT * FROM mvtest_replace;++CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c;+SELECT * FROM mvtest_replace;++-- cannot change column collation+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error+SELECT * FROM mvtest_replace;++-- cannot drop column+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 13 AS a, 1 AS b; -- error+SELECT * FROM mvtest_replace;++-- must target a matview+CREATE VIEW mvtest_not_mv AS+ SELECT 1 AS a;+CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS+ SELECT 1 AS a; -- error+DROP VIEW mvtest_not_mv;++-- cannot use OR REPLACE with IF NOT EXISTS+CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS+ SELECT 1 AS a;++DROP MATERIALIZED VIEW mvtest_replace;--
2.45.2
[text/x-diff] v2-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch (8.5K, ../7afe68b0-f983-4a9f-a1b4-32188cebbf38@ewie.name/3-v2-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch)
download | inline diff:
From f0ed9b74e50530abefb2edc51603c8df061eaa22 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 28 May 2024 02:19:53 +0200
Subject: [PATCH v2 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS
---
src/backend/parser/gram.y | 14 +++++++++++++
.../expected/test_extensions.out | 18 +++++++++++++++++
src/test/regress/expected/matview.out | 20 +++++++++++++++++++
3 files changed, 52 insertions(+)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex ed477806d1..b1100bdec1 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -4830,6 +4830,20 @@ CreateMatViewStmt:
$8->rel->relpersistence = $2;
$8->skipData = !($11);
$$ = (Node *) ctas;
++ if (ctas->into->rel->schemaname)+ ereport(WARNING,+ errmsg("IF NOT EXISTS is deprecated in materialized view creation"),+ errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.%s.",+ ctas->into->rel->schemaname,+ ctas->into->rel->relname),+ parser_errposition(@1));+ else+ ereport(WARNING,+ errmsg("IF NOT EXISTS is deprecated in materialized view creation"),+ errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.",+ ctas->into->rel->relname),+ parser_errposition(@1));
}
| CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data
{
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.outindex f357cc21aa..ecd453b29d 100644--- a/src/test/modules/test_extensions/expected/test_extensions.out+++ b/src/test/modules/test_extensions/expected/test_extensions.out@@ -377,41 +377,57 @@ Objects in extension "test_ext_cor"
CREATE COLLATION ext_cine_coll
( LC_COLLATE = "C", LC_CTYPE = "C" );
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: collation ext_cine_coll is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP COLLATION ext_cine_coll;
CREATE MATERIALIZED VIEW ext_cine_mv AS SELECT 11 AS f1;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: materialized view ext_cine_mv is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP MATERIALIZED VIEW ext_cine_mv;
CREATE FOREIGN DATA WRAPPER dummy;
CREATE SERVER ext_cine_srv FOREIGN DATA WRAPPER dummy;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: server ext_cine_srv is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP SERVER ext_cine_srv;
CREATE SCHEMA ext_cine_schema;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: schema ext_cine_schema is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP SCHEMA ext_cine_schema;
CREATE SEQUENCE ext_cine_seq;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: sequence ext_cine_seq is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP SEQUENCE ext_cine_seq;
CREATE TABLE ext_cine_tab1 (x int);
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: table ext_cine_tab1 is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP TABLE ext_cine_tab1;
CREATE TABLE ext_cine_tab2 AS SELECT 42 AS y;
CREATE EXTENSION test_ext_cine; -- fail
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
ERROR: table ext_cine_tab2 is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP TABLE ext_cine_tab2;
CREATE EXTENSION test_ext_cine;
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
\dx+ test_ext_cine
Objects in extension "test_ext_cine"
Object description
@@ -433,6 +449,8 @@ Objects in extension "test_ext_cine"
(14 rows)
ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv.
\dx+ test_ext_cine
Objects in extension "test_ext_cine"
Object description
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.outindex e2e2a13396..cefd0d442c 100644--- a/src/test/regress/expected/matview.out+++ b/src/test/regress/expected/matview.out@@ -565,6 +565,10 @@ CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
ERROR: relation "mvtest_mv_foo" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELE...+ ^+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW mvtest_mv_foo.
NOTICE: relation "mvtest_mv_foo" already exists, skipping
CREATE UNIQUE INDEX ON mvtest_mv_foo (i);
RESET ROLE;
@@ -662,12 +666,20 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
ERROR: relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS+ ^+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS+ ^+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW matview_ine_tab AS
@@ -676,6 +688,10 @@ ERROR: relation "matview_ine_tab" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS+ ^+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
QUERY PLAN
------------
@@ -688,6 +704,10 @@ ERROR: relation "matview_ine_tab" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
+WARNING: IF NOT EXISTS is deprecated in materialized view creation+LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS+ ^+HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab.
NOTICE: relation "matview_ine_tab" already exists, skipping
QUERY PLAN
------------
--
2.45.2
[text/x-diff] v2-0003-Replace-matview-WITH-OLD-DATA.patch (7.7K, ../7afe68b0-f983-4a9f-a1b4-32188cebbf38@ewie.name/4-v2-0003-Replace-matview-WITH-OLD-DATA.patch)
download | inline diff:
From c386742310dcf2429b796c2a2d32f12db2cc419f Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Fri, 26 Jul 2024 23:33:15 +0200
Subject: [PATCH v2 3/3] Replace matview WITH OLD DATA
---
.../sgml/ref/create_materialized_view.sgml | 16 +++++++++--
src/backend/commands/createas.c | 26 +++++++++++------
src/backend/parser/gram.y | 16 +++++++++++
src/include/nodes/primnodes.h | 1 +
src/test/regress/expected/matview.out | 28 +++++++++++++++++++
src/test/regress/sql/matview.sql | 15 ++++++++++
6 files changed, 90 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgmlindex b5a8e3441a..65633b8bfa 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>
@@ -160,7 +161,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
@@ -168,6 +169,15 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_nam
flagged as unscannable and cannot be queried until <command>REFRESH
MATERIALIZED VIEW</command> is used.
</para>
++ <para>+ The form <command>WITH OLD DATA</command> keeps the already stored data+ when replacing an existing materialized view to keep it populated. For+ newly created materialized views, this has the same effect as+ <command>WITH DATA</command>. Use this form if you want to use+ <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command> as it requires+ a populated materialized view.+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.cindex c5d78252a1..e2948ac966 100644--- a/src/backend/commands/createas.c+++ b/src/backend/commands/createas.c@@ -334,18 +334,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
/* An existing materialized view can be replaced. */
if (is_matview && into->replace)
{
- RefreshMatViewStmt *refresh;-
/* Change the relation to match the new query and other options. */
- (void) create_ctas_nodata(query->targetList, into);+ address = create_ctas_nodata(query->targetList, into);- /* Refresh the materialized view with a fake statement. */- refresh = makeNode(RefreshMatViewStmt);- refresh->relation = into->rel;- refresh->skipData = into->skipData;- refresh->concurrent = false;+ /*+ * Refresh the materialized view with a fake statement unless we+ * must keep the old data.+ */+ if (!into->keepData)+ {+ RefreshMatViewStmt *refresh;++ refresh = makeNode(RefreshMatViewStmt);+ refresh->relation = into->rel;+ refresh->skipData = into->skipData;+ refresh->concurrent = false;++ address = ExecRefreshMatView(refresh, NULL, NULL, NULL);+ }- return ExecRefreshMatView(refresh, NULL, NULL, NULL);+ return address;
}
return InvalidObjectAddress;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex b1100bdec1..0647955005 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -4860,6 +4860,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.hindex 4b0ee5d10d..ae84cb522e 100644--- a/src/include/nodes/primnodes.h+++ b/src/include/nodes/primnodes.h@@ -168,6 +168,7 @@ typedef struct IntoClause
/* materialized view's SELECT query */
Node *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.outindex cefd0d442c..47dfd88bff 100644--- a/src/test/regress/expected/matview.out+++ b/src/test/regress/expected/matview.out@@ -751,6 +751,23 @@ SELECT * FROM mvtest_replace;
3
(1 row)
+-- replace query but keep old data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 5 AS a+ WITH OLD DATA;+SELECT * FROM mvtest_replace;+ a +---+ 3+(1 row)++REFRESH MATERIALIZED VIEW mvtest_replace;+SELECT * FROM mvtest_replace;+ a +---+ 5+(1 row)+
-- add column
CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
SELECT 4 AS a, 1 b;
@@ -905,3 +922,14 @@ ERROR: syntax error at or near "NOT"
LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep...
^
DROP MATERIALIZED VIEW mvtest_replace;
+-- Create new matview WITH OLD DATA. This populates the new matview as if+-- WITH DATA had been specified.+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 17 AS a+ WITH OLD DATA;+SELECT * FROM mvtest_replace;+ a +----+ 17+(1 row)+diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sqlindex c12f0243c9..b268237c24 100644--- a/src/test/regress/sql/matview.sql+++ b/src/test/regress/sql/matview.sql@@ -338,6 +338,14 @@ SELECT * FROM mvtest_replace; -- error: not populated
REFRESH MATERIALIZED VIEW mvtest_replace;
SELECT * FROM mvtest_replace;
+-- replace query but keep old data+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 5 AS a+ WITH OLD DATA;+SELECT * FROM mvtest_replace;+REFRESH MATERIALIZED VIEW mvtest_replace;+SELECT * FROM mvtest_replace;+
-- add column
CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS
SELECT 4 AS a, 1 b;
@@ -422,3 +430,10 @@ CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS
SELECT 1 AS a;
DROP MATERIALIZED VIEW mvtest_replace;
++-- Create new matview WITH OLD DATA. This populates the new matview as if+-- WITH DATA had been specified.+CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS+ SELECT 17 AS a+ WITH OLD DATA;+SELECT * FROM mvtest_replace;--
2.45.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ewie@ewie.name, sassemlal@neurorx.com
Subject: Re: CREATE OR REPLACE MATERIALIZED VIEW
In-Reply-To: <7afe68b0-f983-4a9f-a1b4-32188cebbf38@ewie.name>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox