Hi!
On Fri, Jan 18, 2019 at 7:18 AM Andreas Karlsson <[email protected]> wrote:
> These rules are usually pretty easy to add. Just take a look in
> src/bin/psql/tab-complete.c to see how it is usually done.
Thanks. I have added the auto-complete and attached a new patch.
> I might take a stab at refactoring this myself this weekend. Hopefully
> it is not too involved.
That would be great! I can afterwards update the patch accordingly.
Mitar
--
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 7185432763..c30f76b343 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 5ff6964d51..c855a0750d 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 c086235b25..996fdfc19e 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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index bca788c7a3..157ccbab66 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2426,7 +2426,7 @@ psql_completion(const char *text, int start, int end)
/* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
else if (TailMatches("CREATE", "TEMP|TEMPORARY"))
- COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW");
+ COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW", "MATERIALIZED VIEW");
/* Complete "CREATE UNLOGGED" with TABLE or MATVIEW */
else if (TailMatches("CREATE", "UNLOGGED"))
COMPLETE_WITH("TABLE", "MATERIALIZED VIEW");
@@ -2634,13 +2634,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", "TEMP|TEMPORARY|UNLOGGED", "MATERIALIZED"))
COMPLETE_WITH("VIEW");
/* Complete CREATE MATERIALIZED VIEW <name> with AS */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) ||
+ Matches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "MATERIALIZED", "VIEW", MatchAny))
COMPLETE_WITH("AS");
/* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS"))
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||
+ Matches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "MATERIALIZED", "VIEW", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
/* CREATE EVENT TRIGGER */
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 08cd4bea48..ebd50bbf9e 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 ORDER BY id;
+ 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 ORDER BY id;
+ 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 ORDER BY id;
+ 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..5de050f37f 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 ORDER BY id;
+
+-- 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 ORDER BY id;
+SELECT * FROM mvtest_t_temp_view ORDER BY id;
+
+-- 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;
