Hi!
I made a new version of the patch. I added tests and changes to the
docs and made sure various other aspects of this change for as well. I
think this now makes temporary materialized views fully implemented
and that in my view patch is complete. If there is anything else to
add, please let me know, I do not yet have much experience
contributing here. What are next steps? Do I just wait for it to be
included into Commitfest? Do I add it there myself?
Mitar
On Wed, Dec 26, 2018 at 9:00 AM Alvaro Herrera <[email protected]> wrote:
>
> On 2018-Dec-25, Mitar wrote:
>
> > Sometimes materialized views are used to cache a complex query on
> > which a client works. But after client disconnects, the materialized
> > view could be deleted. Regular VIEWs and TABLEs both have support for
> > temporary versions which get automatically dropped at the end of the
> > session. It seems it is easy to add the same thing for materialized
> > views as well. See attached PoC patch.
>
> I think MVs that are dropped at session end are a sensible feature. I
> probably wouldn't go as far as allowing ON COMMIT actions, though, so
> this much effort is the right amount.
>
> I think if you really want to do this you should just use OptTemp, and
> delete OptNoLog. Of course, you need to add tests and patch the docs.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
http://mitar.tnode.com/
https://twitter.com/mitar_m
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index 7f31ab4d26..dd5ed8e3d0 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 [ TEMP | TEMPORARY ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
[ (<replaceable>column_name</replaceable> [, ...] ) ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -53,6 +53,26 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
+ <listitem>
+ <para>
+ If specified, the materialized view is created as a temporary materialized view.
+ Temporary materialized views are automatically dropped at the end of the
+ current session. Existing
+ permanent relations with the same name are not visible to the
+ current session while the temporary materialized view exists, unless they are
+ referenced with schema-qualified names.
+ </para>
+
+ <para>
+ If any of the tables referenced by the materialized view are temporary,
+ the materialized view is created as a temporary materialized view (whether
+ <literal>TEMPORARY</literal> is specified or not).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index d01b258b65..706b6a23e2 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -40,6 +40,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_clause.h"
+#include "parser/parse_relation.h"
#include "rewrite/rewriteHandler.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
@@ -85,6 +86,7 @@ create_ctas_internal(List *attrList, IntoClause *into)
{
CreateStmt *create = makeNode(CreateStmt);
bool is_matview;
+ RangeVar *relation;
char relkind;
Datum toast_options;
static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
@@ -94,11 +96,27 @@ create_ctas_internal(List *attrList, IntoClause *into)
is_matview = (into->viewQuery != NULL);
relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION;
+ /*
+ * If the user didn't explicitly ask for a temporary MV, check whether
+ * we need one implicitly. We allow TEMP to be inserted automatically as
+ * long as the CREATE command is consistent with that --- no explicit
+ * schema name.
+ */
+ relation = copyObject(into->rel); /* don't corrupt original command */
+ if (is_matview && relation->relpersistence == RELPERSISTENCE_PERMANENT
+ && isQueryUsingTempRelation((Query *) into->viewQuery))
+ {
+ relation->relpersistence = RELPERSISTENCE_TEMP;
+ ereport(NOTICE,
+ (errmsg("materialized view \"%s\" will be a temporary materialized view",
+ relation->relname)));
+ }
+
/*
* Create the target relation by faking up a CREATE TABLE parsetree and
* passing it to DefineRelation.
*/
- create->relation = into->rel;
+ create->relation = relation;
create->tableElts = attrList;
create->inhRelations = NIL;
create->ofTypename = NULL;
@@ -278,17 +296,12 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
Assert(query->commandType == CMD_SELECT);
/*
- * For materialized views, lock down security-restricted operations and
- * arrange to make GUC variable changes local to this command. This is
- * not necessary for security, but this keeps the behavior similar to
- * REFRESH MATERIALIZED VIEW. Otherwise, one could create a materialized
- * view not possible to refresh.
+ * For materialized views, arrange to make GUC variable changes local
+ * to this command.
*/
if (is_matview)
{
GetUserIdAndSecContext(&save_userid, &save_sec_context);
- SetUserIdAndSecContext(save_userid,
- save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
}
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 226927b7ab..cff199c16b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2551,16 +2551,6 @@ transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("materialized views must not use data-modifying statements in WITH")));
- /*
- * Check whether any temporary database objects are used in the
- * creation query. It would be hard to refresh data or incrementally
- * maintain it if a source disappeared.
- */
- if (isQueryUsingTempRelation(query))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("materialized views must not use temporary tables or views")));
-
/*
* A materialized view would either need to save parameters for use in
* maintaining/loading the data or prohibit them entirely. The latter
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 2c2208ffb7..54d2708bbb 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -420,7 +420,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <boolean> opt_trusted opt_restart_seqs
%type <ival> OptTemp
-%type <ival> OptNoLog
%type <oncommit> OnCommitOption
%type <ival> for_locking_strength
@@ -4054,7 +4053,7 @@ opt_with_data:
*****************************************************************************/
CreateMatViewStmt:
- CREATE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data
+ CREATE OptTemp MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data
{
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
ctas->query = $7;
@@ -4067,7 +4066,7 @@ CreateMatViewStmt:
$5->skipData = !($8);
$$ = (Node *) ctas;
}
- | CREATE OptNoLog MATERIALIZED VIEW IF_P NOT EXISTS create_mv_target AS SelectStmt opt_with_data
+ | CREATE OptTemp MATERIALIZED VIEW IF_P NOT EXISTS create_mv_target AS SelectStmt opt_with_data
{
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
ctas->query = $10;
@@ -4096,10 +4095,6 @@ create_mv_target:
}
;
-OptNoLog: UNLOGGED { $$ = RELPERSISTENCE_UNLOGGED; }
- | /*EMPTY*/ { $$ = RELPERSISTENCE_PERMANENT; }
- ;
-
/*****************************************************************************
*
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 08cd4bea48..b191392a06 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -589,3 +589,88 @@ SELECT * FROM mvtest2;
ERROR: materialized view "mvtest2" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
ROLLBACK;
+-- create temporary materialized view
+EXPLAIN (costs off)
+ CREATE TEMPORARY MATERIALIZED VIEW mvtest_t_temp_view AS SELECT * FROM mvtest_t ORDER BY id;
+ QUERY PLAN
+--------------------------------------------
+ Index Scan using mvtest_t_pkey on mvtest_t
+(1 row)
+
+CREATE TEMPORARY MATERIALIZED VIEW mvtest_t_temp_view AS SELECT * FROM mvtest_t ORDER BY id;
+CREATE UNIQUE INDEX mvtest_t_temp_view_id ON mvtest_t_temp_view (id);
+SELECT * FROM mvtest_t_temp_view;
+ id | type | amt
+----+------+-----
+ 1 | x | 2
+ 2 | x | 3
+ 3 | y | 5
+ 4 | y | 7
+ 5 | z | 11
+ 6 | z | 13
+(6 rows)
+
+-- check the description, it should be temporary
+SELECT relpersistence, relkind FROM pg_class WHERE relname='mvtest_t_temp_view';
+ relpersistence | relkind
+----------------+---------
+ t | m
+(1 row)
+
+-- we should be able to refresh it
+REFRESH MATERIALIZED VIEW mvtest_t_temp_view;
+REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_t_temp_view;
+-- we make it so that view is refreshed for every change of used table
+CREATE OR REPLACE FUNCTION refresh_view() RETURNS TRIGGER LANGUAGE plpgsql AS $$
+ DECLARE
+ view_name TEXT := TG_ARGV[0];
+ BEGIN
+ EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY "' || view_name || '"';
+ RETURN NULL;
+ END
+$$;
+CREATE TRIGGER refresh_view_mvtest_t_insert AFTER INSERT ON mvtest_t REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION refresh_view('mvtest_t_temp_view');
+INSERT INTO mvtest_t VALUES(7, 'z', 10);
+-- both should be updated, refresh inside a trigger should work
+SELECT * FROM mvtest_t;
+ id | type | amt
+----+------+-----
+ 1 | x | 2
+ 2 | x | 3
+ 3 | y | 5
+ 4 | y | 7
+ 5 | z | 11
+ 6 | z | 13
+ 7 | z | 10
+(7 rows)
+
+SELECT * FROM mvtest_t_temp_view;
+ id | type | amt
+----+------+-----
+ 1 | x | 2
+ 2 | x | 3
+ 3 | y | 5
+ 4 | y | 7
+ 5 | z | 11
+ 6 | z | 13
+ 7 | z | 10
+(7 rows)
+
+-- create a temporary table
+CREATE TEMPORARY TABLE mvtest_t_temp (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
+INSERT INTO mvtest_t_temp VALUES
+ (1, 'x', 2),
+ (2, 'x', 3),
+ (3, 'y', 5),
+ (4, 'y', 7),
+ (5, 'z', 11);
+-- this one should be temporary as well
+CREATE MATERIALIZED VIEW mvtest_t_temp_temp_view AS SELECT * FROM mvtest_t_temp ORDER BY id;
+NOTICE: materialized view "mvtest_t_temp_temp_view" will be a temporary materialized view
+-- check the description, it should be temporary
+SELECT relpersistence, relkind FROM pg_class WHERE relname='mvtest_t_temp_temp_view';
+ relpersistence | relkind
+----------------+---------
+ t | m
+(1 row)
+
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index d96175aa26..98c2aa90ea 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -236,3 +236,50 @@ SELECT mvtest_func();
SELECT * FROM mvtest1;
SELECT * FROM mvtest2;
ROLLBACK;
+
+-- create temporary materialized view
+EXPLAIN (costs off)
+ CREATE TEMPORARY MATERIALIZED VIEW mvtest_t_temp_view AS SELECT * FROM mvtest_t ORDER BY id;
+CREATE TEMPORARY MATERIALIZED VIEW mvtest_t_temp_view AS SELECT * FROM mvtest_t ORDER BY id;
+CREATE UNIQUE INDEX mvtest_t_temp_view_id ON mvtest_t_temp_view (id);
+SELECT * FROM mvtest_t_temp_view;
+
+-- check the description, it should be temporary
+SELECT relpersistence, relkind FROM pg_class WHERE relname='mvtest_t_temp_view';
+
+-- we should be able to refresh it
+REFRESH MATERIALIZED VIEW mvtest_t_temp_view;
+REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_t_temp_view;
+
+-- we make it so that view is refreshed for every change of used table
+CREATE OR REPLACE FUNCTION refresh_view() RETURNS TRIGGER LANGUAGE plpgsql AS $$
+ DECLARE
+ view_name TEXT := TG_ARGV[0];
+ BEGIN
+ EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY "' || view_name || '"';
+ RETURN NULL;
+ END
+$$;
+
+CREATE TRIGGER refresh_view_mvtest_t_insert AFTER INSERT ON mvtest_t REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION refresh_view('mvtest_t_temp_view');
+
+INSERT INTO mvtest_t VALUES(7, 'z', 10);
+
+-- both should be updated, refresh inside a trigger should work
+SELECT * FROM mvtest_t;
+SELECT * FROM mvtest_t_temp_view;
+
+-- create a temporary table
+CREATE TEMPORARY TABLE mvtest_t_temp (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
+INSERT INTO mvtest_t_temp VALUES
+ (1, 'x', 2),
+ (2, 'x', 3),
+ (3, 'y', 5),
+ (4, 'y', 7),
+ (5, 'z', 11);
+
+-- this one should be temporary as well
+CREATE MATERIALIZED VIEW mvtest_t_temp_temp_view AS SELECT * FROM mvtest_t_temp ORDER BY id;
+
+-- check the description, it should be temporary
+SELECT relpersistence, relkind FROM pg_class WHERE relname='mvtest_t_temp_temp_view';
