Hi!
Thanks, I did it.
I am attaching a new version of the patch with few more lines added to tests.
I noticed that there is no good summary of the latest patch, so let me
make it here:
So the latest version of the patch adds an option for "temporary"
materialized views. Such materialized views are automatically deleted
at the end of the session. Moreover, it also modifies the materialized
view creation logic so that now if any of the source relations are
temporary, the final materialized view is temporary as well. This now
makes materialized views more aligned with regular views.
Tests test that this really works, that refreshing of such views work,
and that refreshing can also work from a trigger.
Mitar
On Thu, Dec 27, 2018 at 5:15 AM Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
>
> On 2018-Dec-27, Mitar wrote:
>
> > 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?
>
> Yes, please add it yourself to the commitfest.
>
> --
> Á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..4ea6696ded 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -589,3 +589,90 @@ 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)
+
+DROP TABLE mvtest_t_temp CASCADE;
+NOTICE: drop cascades to materialized view mvtest_t_temp_temp_view
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index d96175aa26..8d3183a3b2 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -236,3 +236,52 @@ 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';
+
+DROP TABLE mvtest_t_temp CASCADE;
