On Mon, 1 Apr 2019 12:11:22 +0900
Yugo Nagata <nag...@sraoss.co.jp> wrote:
> On Thu, 27 Dec 2018 21:57:26 +0900
> Yugo Nagata <nag...@sraoss.co.jp> wrote:
>
> > Hi,
> >
> > I would like to implement Incremental View Maintenance (IVM) on PostgreSQL.
> >
>
> I am now working on an initial patch for implementing IVM on PostgreSQL.
> This enables materialized views to be updated incrementally after one
> of their base tables is modified.
Attached is a WIP patch of Incremental View Maintenance (IVM).
Major part is written by me, and changes in syntax and pg_class
are Hoshiai-san's work.
Although this is sill a draft patch in work-in-progress, any
suggestions or thoughts would be appreciated.
* What it is
This allows a kind of Immediate Maintenance of materialized views. if a
materialized view is created by CRATE INCREMENTAL MATERIALIZED VIEW command,
the contents of the mateview is updated automatically and incrementally
after base tables are updated. Noted this syntax is just tentative, so it
may be changed.
====== Example 1 ======
postgres=# CREATE INCREMENTAL MATERIALIZED VIEW m AS SELECT * FROM t0;
SELECT 3
postgres=# SELECT * FROM m;
i
---
3
2
1
(3 rows)
postgres=# INSERT INTO t0 VALUES (4);
INSERT 0 1
postgres=# SELECt * FROM m; -- automatically updated
i
---
3
2
1
4
(4 rows)
=============================
This implementation also supports matviews including duplicate tuples or
DISTINCT clause in its view definition query. For example, even if a matview
is defined with DISTINCT to remove duplication of tuples in a base table, this
can perform incremental update of the matview properly. That is, the contents
of the matview doesn't change when exiting tuples are inserted into the base
tables, and a tuple in the matview is deleted only when duplicity of the
corresponding tuple in the base table becomes zero.
This is due to "colunting alogorithm" in which the number of each tuple is
stored in matviews as a special column value.
====== Example 2 ======
postgres=# SELECT * FROM t1;
id | t
----+---
1 | A
2 | B
3 | C
4 | A
(4 rows)
postgres=# CREATE INCREMENTAL MATERIALIZED VIEW m1 AS SELECT t FROM t1;
SELECT 3
postgres=# CREATE INCREMENTAL MATERIALIZED VIEW m2 AS SELECT DISTINCT t FROM t1;
SELECT 3
postgres=# SELECT * FROM m1; -- with duplicity
t
---
A
A
C
B
(4 rows)
postgres=# SELECT * FROM m2;
t
---
A
B
C
(3 rows)
postgres=# INSERT INTO t1 VALUES (5, 'B');
INSERT 0 1
postgres=# DELETE FROM t1 WHERE id IN (1,3); -- delete (1,A),(3,C)
DELETE 2
postgres=# SELECT * FROM m1; -- one A left and one more B
t
---
B
B
A
(3 rows)
postgres=# SELECT * FROM m2; -- only C is removed
t
---
B
A
(2 rows)
=============================
* How it works
1. Creating matview
When a matview is created, AFTER triggers are internally created
on its base tables. When the base tables is modified (INSERT, DELETE,
UPDATE), the matview is updated incrementally in the trigger function.
When populating the matview, GROUP BY and count(*) are added to the
view definition query before this is executed for counting duplicity
of tuples in the matview. The result of count is stored in the matview
as a special column named "__ivm_count__".
2. Maintenance of matview
When base tables are modified, the change set of the table can be
referred as Ephemeral Named Relations (ENRs) thanks to Transition Table
(a feature of trigger implemented since PG10). We can calculate the diff
set of the matview by replacing the base table in the view definition
query with the ENR (at least if it is Selection-Projection -Join view).
As well as view definition time, GROUP BY and count(*) is added in order
to count the duplicity of tuples in the diff set. As a result, two diff
sets (to be deleted from and to be inserted into the matview) are
calculated, and the results are stored into temporary tables respectively.
The matiview is updated by merging these change sets. Instead of executing
DELETE or INSERT simply, the values of __ivm_count__ column in the matview
is decreased or increased. When the values becomes zero, the corresponding
tuple is deleted from the matview.
3. Access to matview
When SELECT is issued for IVM matviews defined with DISTINCT, all columns
except to __ivm_count__ of each tuple in the matview are returned. This is
correct because duplicity of tuples are eliminated by GROUP BY.
When DISTINCT is not used, SELECT for the IVM matviews returns each tuple
__ivm_count__ times. Currently, this is implemented by rewriting the SELECT
query to replace the matview RTE with a subquery which joins the matview
and generate_series function as bellow.
SELECT mv.* FROM mv, generate_series(1, mv.__ivm_count__);
__ivm_count__ column is invisible for users when "SELECT * FROM ..." is
issued, but users can see the value by specifying in target list explicitly.
====== Example 3 ======
postgres=# \d+ m1
Materialized view "public.m1"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
---------------+--------+-----------+----------+---------+----------+--------------+-------------
t | text | | | | extended |
|
__ivm_count__ | bigint | | | | plain |
|
View definition:
SELECT t1.t
FROM t1;
Access method: heap
postgres=# \d+ m2
Materialized view "public.m2"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
---------------+--------+-----------+----------+---------+----------+--------------+-------------
t | text | | | | extended |
|
__ivm_count__ | bigint | | | | plain |
|
View definition:
SELECT DISTINCT t1.t
FROM t1;
Access method: heap
postgres=# SELECT *, __ivm_count__ FROM m1;
t | __ivm_count__
---+---------------
B | 2
B | 2
A | 1
(3 rows)
postgres=# SELECT *, __ivm_count__ FROM m2;
t | __ivm_count__
---+---------------
B | 2
A | 1
(2 rows)
postgres=# EXPLAIN SELECT * FROM m1;
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop (cost=0.00..61.03 rows=3000 width=2)
-> Seq Scan on m1 mv (cost=0.00..1.03 rows=3 width=10)
-> Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=0)
(3 rows)
=============================
* Simple Performance Evaluation
I confirmed that "incremental" update of matviews is more effective
than the standard REFRESH by using simple exapmle. I used tables
of pgbench (SF=100) here.
Create two matviews, that is, without and with IVM.
test=# CREATE MATERIALIZED VIEW bench1 AS
SELECT aid, bid, abalance, bbalance
FROM pgbench_accounts JOIN pgbench_branches USING (bid)
WHERE abalance > 0 OR bbalance > 0;
SELECT 5001054
test=# CREATE INCREMENTAL MATERIALIZED VIEW bench2 AS
SELECT aid, bid, abalance, bbalance
FROM pgbench_accounts JOIN pgbench_branches USING (bid)
WHERE abalance > 0 OR bbalance > 0;
SELECT 5001054
The standard REFRESH of bench1 took more than 10 seconds.
test=# \timing
Timing is on.
test=# REFRESH MATERIALIZED VIEW bench1 ;
REFRESH MATERIALIZED VIEW
Time: 11210.563 ms (00:11.211)
Create an index on the IVM matview (bench2).
test=# CREATE INDEX on bench2(aid,bid);
CREATE INDEX
Updating a tuple in pgbench_accounts took 18ms. After this, bench2
was updated automatically and correctly.
test=# SELECT * FROM bench2 WHERE aid = 1;
aid | bid | abalance | bbalance
-----+-----+----------+----------
1 | 1 | 10 | 10
(1 row)
Time: 2.498 ms
test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
UPDATE 1
Time: 18.634 ms
test=# SELECT * FROM bench2 WHERE aid = 1;
aid | bid | abalance | bbalance
-----+-----+----------+----------
1 | 1 | 1000 | 10
(1 row)
However, if there is not the index on bench2, it took 4 sec, so
appropriate indexes are needed on IVM matviews.
test=# DROP INDEX bench2_aid_bid_idx ;
DROP INDEX
Time: 10.613 ms
test=# UPDATE pgbench_accounts SET abalance = 2000 WHERE aid = 1;
UPDATE 1
Time: 3931.274 ms (00:03.931)
* Restrictions on view definition
This patch is still in Work-in-Progress and there are many restrictions
on the view definition query of matviews.
The current implementation supports views including selection, projection,
and inner join with or without DISTINCT. Aggregation and GROUP BY are not
supported yet, but I plan to deal with these by the first release.
Self-join, subqueries, OUTER JOIN, CTE, window functions are not
considered well, either. I need more investigation on these type of views
although I found some papers explaining how to handle sub-queries and
outer-joins.
These unsupported views should be checked when a matview is created, but
this is not implemented yet. Hoshiai-san are working on this.
* Timing of view maintenance
This patch implements a kind of Immediate Maintenance, that is, a matview
is updated immediately when a base table is modified. On other hand, in
"Deferred Maintenance", matviews are updated after the transaction, for
example, by the user command like REFRESH.
For implementing "deferred", it is need to implement a mechanism to maintain
logs for recording changes of base tables and an algorithm to compute the
delta to be applied to matviews.
In addition, there could be another implementation of Immediate Maintenance
in which matview is updated at the end of a transaction that modified base
table, rather than in AFTER trigger. Oracle supports this type of IVM. To
implement this, we will need a mechanism to maintain change logs on base
tables as well as Deferred maintenance.
* Counting algorithm implementation
There will be also discussions on counting-algorithm implementation.
Firstly, the current patch treats "__ivm_count__" as a special column name
in a somewhat ad hoc way. This is used when maintaining and accessing matviews,
and when "SELECT * FROM ..." is issued, __ivm_count__ column is invisible for
users. Maybe this name has to be inhibited in user tables. Is it acceptable
to use such columns for IVM, and is there better way, if not?
Secondly, a matview with duplicate tuples is replaces with a subquery which
uses generate_series function. It does not have to be generate_series, and we
can make a new set returning function for this. Anyway, this internal behaviour
is visible in EXPLAIN results as shown in Example 3. Also, there is a
performance impact because estimated rows number is wrong, and what is worse,
the cost of join is not small when the size of matview is large. Therefore, we
might have to add a new plan node for selecting from matviews rather than using
such a special set returning function.
Ragards,
--
Yugo Nagata <nag...@sraoss.co.jp>
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4c7e93892a..858b05b427 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1944,6 +1944,13 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<entry>True if table or index is a partition</entry>
</row>
+ <row>
+ <entry><structfield>relisivm</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>True if materialized view enables incremental view maintenance</entry>
+ </row>
+
<row>
<entry><structfield>relrewrite</structfield></entry>
<entry><type>oid</type></entry>
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index ec8847ed40..a23366a342 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 [ INCREMENTAL ] 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>] [, ... ] ) ]
@@ -54,6 +54,16 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>INCREMENTAL</literal></term>
+ <listitem>
+ <para>
+ If specified, a materialized view enables incremental view maintenance.
+ You can replace only the contents of a materialized view, which based rows are changed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index ee6b72e550..823486c968 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -891,6 +891,7 @@ InsertPgClassTuple(Relation pg_class_desc,
values[Anum_pg_class_relrewrite - 1] = ObjectIdGetDatum(rd_rel->relrewrite);
values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid);
values[Anum_pg_class_relminmxid - 1] = MultiXactIdGetDatum(rd_rel->relminmxid);
+ values[Anum_pg_class_relisivm - 1] = BoolGetDatum(rd_rel->relisivm);
if (relacl != (Datum) 0)
values[Anum_pg_class_relacl - 1] = relacl;
else
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index c8d22e1b65..57cd30718b 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -910,6 +910,7 @@ index_create(Relation heapRelation,
indexRelation->rd_rel->relowner = heapRelation->rd_rel->relowner;
indexRelation->rd_rel->relam = accessMethodObjectId;
indexRelation->rd_rel->relispartition = OidIsValid(parentIndexRelid);
+ indexRelation->rd_rel->relisivm = false;
/*
* store index's pg_class entry
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 43c2fa9124..0098531dfd 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -51,6 +51,14 @@
#include "utils/rls.h"
#include "utils/snapmgr.h"
+#include "catalog/pg_trigger.h"
+#include "catalog/pg_type.h"
+#include "commands/trigger.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "parser/parse_func.h"
+#include "nodes/print.h"
+
typedef struct
{
@@ -74,6 +82,8 @@ static bool intorel_receive(TupleTableSlot *slot, DestReceiver *self);
static void intorel_shutdown(DestReceiver *self);
static void intorel_destroy(DestReceiver *self);
+static void CreateIvmTrigger(Oid relOid, Oid viewOid, char *matviewname, int16 type);
+static void CreateIvmTriggersOnBaseTables(Query *qry, Node *jtnode, Oid matviewOid, char* matviewname);
/*
* create_ctas_internal
@@ -109,6 +119,8 @@ create_ctas_internal(List *attrList, IntoClause *into)
create->oncommit = into->onCommit;
create->tablespacename = into->tableSpaceName;
create->if_not_exists = false;
+ /* Using Materialized view only */
+ create->ivm = into->ivm;
create->accessMethod = into->accessMethod;
/*
@@ -239,6 +251,7 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
List *rewritten;
PlannedStmt *plan;
QueryDesc *queryDesc;
+ Query *copied_query;
if (stmt->if_not_exists)
{
@@ -319,7 +332,29 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
* and is executed repeatedly. (See also the same hack in EXPLAIN and
* PREPARE.)
*/
- rewritten = QueryRewrite(copyObject(query));
+
+ copied_query = copyObject(query);
+ if (is_matview && into->ivm)
+ {
+ TargetEntry *tle;
+ Node *node;
+ ParseState *pstate = make_parsestate(NULL);
+
+ FuncCall *fn = makeFuncCall(list_make1(makeString("count")), NIL, -1);
+ fn->agg_star = true;
+
+ copied_query->groupClause = transformDistinctClause(NULL, &copied_query->targetList, copied_query->sortClause, false);
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle = makeTargetEntry((Expr *) node,
+ list_length(copied_query->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ copied_query->targetList = lappend(copied_query->targetList, tle);
+ copied_query->hasAggs = true;
+ }
+
+ rewritten = QueryRewrite(copied_query);
/* SELECT should never rewrite to more or less than one SELECT query */
if (list_length(rewritten) != 1)
@@ -378,11 +413,65 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
/* Restore userid and security context */
SetUserIdAndSecContext(save_userid, save_sec_context);
+
+
+ if (into->ivm)
+ {
+ char *matviewname;
+ Oid matviewOid = address.objectId;
+ Relation matviewRel = heap_open(matviewOid, NoLock);
+ matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+ copied_query = copyObject(query);
+ AcquireRewriteLocks(copied_query, true, false);
+
+ CreateIvmTriggersOnBaseTables(copied_query, (Node *)copied_query->jointree, matviewOid, matviewname);
+
+ heap_close(matviewRel, NoLock);
+ }
}
return address;
}
+static void CreateIvmTriggersOnBaseTables(Query *qry, Node *jtnode, Oid matviewOid, char* matviewname)
+{
+
+ if (jtnode == NULL)
+ return;
+ if (IsA(jtnode, RangeTblRef))
+ {
+ int rti = ((RangeTblRef *) jtnode)->rtindex;
+ RangeTblEntry *rte = rt_fetch(rti, qry->rtable);
+
+ if (rte->rtekind == RTE_RELATION)
+ {
+ CreateIvmTrigger(rte->relid, matviewOid, matviewname, TRIGGER_TYPE_INSERT);
+ CreateIvmTrigger(rte->relid, matviewOid, matviewname, TRIGGER_TYPE_DELETE);
+ CreateIvmTrigger(rte->relid, matviewOid, matviewname, TRIGGER_TYPE_UPDATE);
+ }
+ else
+ elog(ERROR, "unsupported RTE kind: %d", (int) rte->rtekind);
+ }
+ else if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ ListCell *l;
+
+ foreach(l, f->fromlist)
+ CreateIvmTriggersOnBaseTables(qry, lfirst(l), matviewOid, matviewname);
+ }
+ else if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+
+ CreateIvmTriggersOnBaseTables(qry, j->larg, matviewOid, matviewname);
+ CreateIvmTriggersOnBaseTables(qry, j->rarg, matviewOid, matviewname);
+ }
+ else
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(jtnode));
+}
+
/*
* GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
*
@@ -547,6 +636,11 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
if (is_matview && !into->skipData)
SetMatViewPopulatedState(intoRelationDesc, true);
+ /*
+ * Mark relisivm field, if it's a matview and into->ivm is true.
+ */
+ if (is_matview && into->ivm)
+ SetMatViewIVMState(intoRelationDesc, true);
/*
* Fill private fields of myState for use by later routines
*/
@@ -619,3 +713,74 @@ intorel_destroy(DestReceiver *self)
{
pfree(self);
}
+
+static void
+CreateIvmTrigger(Oid relOid, Oid viewOid, char *matviewname, int16 type)
+{
+ CreateTrigStmt *ivm_trigger;
+ List *transitionRels = NIL;
+ ObjectAddress address, refaddr;
+
+ refaddr.classId = RelationRelationId;
+ refaddr.objectId = viewOid;
+ refaddr.objectSubId = 0;
+
+
+ ivm_trigger = makeNode(CreateTrigStmt);
+ ivm_trigger->relation = NULL;
+ ivm_trigger->row = false;
+ ivm_trigger->timing = TRIGGER_TYPE_AFTER;
+
+ ivm_trigger->events = type;
+
+ switch (type)
+ {
+ case TRIGGER_TYPE_INSERT:
+ ivm_trigger->trigname = "IVM_trigger_ins";
+ break;
+ case TRIGGER_TYPE_DELETE:
+ ivm_trigger->trigname = "IVM_trigger_del";
+ break;
+ case TRIGGER_TYPE_UPDATE:
+ ivm_trigger->trigname = "IVM_trigger_upd";
+ break;
+ }
+
+ if (type == TRIGGER_TYPE_INSERT || type == TRIGGER_TYPE_UPDATE)
+ {
+ TriggerTransition *n = makeNode(TriggerTransition);
+ n->name = "ivm_newtable";
+ n->isNew = true;
+ n->isTable = true;
+
+ transitionRels = lappend(transitionRels, n);
+ }
+ if (type == TRIGGER_TYPE_DELETE || type == TRIGGER_TYPE_UPDATE)
+ {
+ TriggerTransition *n = makeNode(TriggerTransition);
+ n->name = "ivm_oldtable";
+ n->isNew = false;
+ n->isTable = true;
+
+ transitionRels = lappend(transitionRels, n);
+ }
+
+ ivm_trigger->funcname = SystemFuncName("IVM_immediate_maintenance");
+
+ ivm_trigger->columns = NIL;
+ ivm_trigger->transitionRels = transitionRels;
+ ivm_trigger->whenClause = NULL;
+ ivm_trigger->isconstraint = false;
+ ivm_trigger->deferrable = false;
+ ivm_trigger->initdeferred = false;
+ ivm_trigger->constrrel = NULL;
+ ivm_trigger->args = list_make1(makeString(matviewname));
+
+ address = CreateTrigger(ivm_trigger, NULL, relOid, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid, NULL, true, false);
+
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_AUTO);
+
+ /* Make changes-so-far visible */
+ CommandCounterIncrement();
+}
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 99bf3c29f2..a403b93b4c 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -46,6 +46,11 @@
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/regproc.h"
+#include "nodes/makefuncs.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_func.h"
+
typedef struct
{
@@ -65,6 +70,7 @@ static bool transientrel_receive(TupleTableSlot *slot, DestReceiver *self);
static void transientrel_shutdown(DestReceiver *self);
static void transientrel_destroy(DestReceiver *self);
static uint64 refresh_matview_datafill(DestReceiver *dest, Query *query,
+ QueryEnvironment *queryEnv,
const char *queryString);
static char *make_temptable_name_n(char *tempname, int n);
static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
@@ -74,6 +80,9 @@ static bool is_usable_unique_index(Relation indexRel);
static void OpenMatViewIncrementalMaintenance(void);
static void CloseMatViewIncrementalMaintenance(void);
+static void apply_delta(Oid matviewOid, Oid tempOid_new, Oid tempOid_old,
+ Oid relowner, int save_sec_context);
+
/*
* SetMatViewPopulatedState
* Mark a materialized view as populated, or not.
@@ -114,6 +123,46 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
CommandCounterIncrement();
}
+/*
+ * SetMatViewIVMState
+ * Mark a materialized view as IVM, or not.
+ *
+ * NOTE: caller must be holding an appropriate lock on the relation.
+ */
+void
+SetMatViewIVMState(Relation relation, bool newstate)
+{
+ Relation pgrel;
+ HeapTuple tuple;
+
+ Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Update relation's pg_class entry. Crucial side-effect: other backends
+ * (and this one too!) are sent SI message to make them rebuild relcache
+ * entries.
+ */
+ pgrel = table_open(RelationRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopy1(RELOID,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u",
+ RelationGetRelid(relation));
+
+ ((Form_pg_class) GETSTRUCT(tuple))->relisivm = newstate;
+
+ CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
+
+ heap_freetuple(tuple);
+ table_close(pgrel, RowExclusiveLock);
+
+ /*
+ * Advance command counter to make the updated pg_class row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+}
+
/*
* ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
*
@@ -311,7 +360,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
/* Generate the data, if wanted. */
if (!stmt->skipData)
- processed = refresh_matview_datafill(dest, dataQuery, queryString);
+ processed = refresh_matview_datafill(dest, dataQuery, NULL, queryString);
/* Make the matview match the newly generated data. */
if (concurrent)
@@ -369,6 +418,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
*/
static uint64
refresh_matview_datafill(DestReceiver *dest, Query *query,
+ QueryEnvironment *queryEnv,
const char *queryString)
{
List *rewritten;
@@ -405,7 +455,7 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
/* Create a QueryDesc, redirecting output to our tuple receiver */
queryDesc = CreateQueryDesc(plan, queryString,
GetActiveSnapshot(), InvalidSnapshot,
- dest, NULL, NULL, 0);
+ dest, NULL, queryEnv ? queryEnv: NULL, 0);
/* call ExecutorStart to prepare the plan for execution */
ExecutorStart(queryDesc, 0);
@@ -926,3 +976,366 @@ CloseMatViewIncrementalMaintenance(void)
matview_maintenance_depth--;
Assert(matview_maintenance_depth >= 0);
}
+
+/*
+ * IVM trigger function
+ */
+
+Datum
+IVM_immediate_maintenance(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Relation rel;
+ Oid relid;
+ Oid matviewOid;
+ Query *query, *old_delta_qry, *new_delta_qry;
+ char* matviewname = trigdata->tg_trigger->tgargs[0];
+ List *names;
+ Relation matviewRel;
+ int old_depth = matview_maintenance_depth;
+
+ Oid tableSpace;
+ Oid relowner;
+ Oid OIDDelta_new = InvalidOid;
+ Oid OIDDelta_old = InvalidOid;
+ DestReceiver *dest_new = NULL, *dest_old = NULL;
+ char relpersistence;
+ Oid save_userid;
+ int save_sec_context;
+ int save_nestlevel;
+
+ ParseState *pstate;
+ QueryEnvironment *queryEnv = create_queryEnv();
+
+ /* Create a dummy ParseState for addRangeTableEntryForENR */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+
+ names = stringToQualifiedNameList(matviewname);
+ matviewOid = RangeVarGetRelid(makeRangeVarFromNameList(names), AccessShareLock, true);
+ matviewRel = heap_open(matviewOid, NoLock);
+
+ /* Make sure it is a materialized view. */
+ if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("\"%s\" is not a materialized view",
+ RelationGetRelationName(matviewRel))));
+
+ rel = trigdata->tg_relation;
+ relid = rel->rd_id;
+
+ query = get_view_query(matviewRel);
+
+ new_delta_qry = copyObject(query);
+ old_delta_qry = copyObject(query);
+
+ if (trigdata->tg_newtable)
+ {
+ RangeTblEntry *rte;
+ ListCell *lc;
+
+ TargetEntry *tle;
+ Node *node;
+ FuncCall *fn = makeFuncCall(list_make1(makeString("count")), NIL, -1);
+
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+
+ enr->md.name = trigdata->tg_trigger->tgnewtable;
+ enr->md.reliddesc = trigdata->tg_relation->rd_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(trigdata->tg_newtable);
+ enr->reldata = trigdata->tg_newtable;
+ register_ENR(queryEnv, enr);
+
+ rte = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ new_delta_qry->rtable = lappend(new_delta_qry->rtable, rte);
+
+ foreach(lc, new_delta_qry->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+ if (r->relid == relid)
+ {
+ lfirst(lc) = rte;
+ break;
+ }
+ }
+
+ fn->agg_star = true;
+ new_delta_qry->groupClause = transformDistinctClause(NULL, &new_delta_qry->targetList, new_delta_qry->sortClause, false);
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle = makeTargetEntry((Expr *) node,
+ list_length(new_delta_qry->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ new_delta_qry->targetList = lappend(new_delta_qry->targetList, tle);
+ new_delta_qry->hasAggs = true;
+ }
+
+ if (trigdata->tg_oldtable)
+ {
+ RangeTblEntry *rte;
+ ListCell *lc;
+
+ TargetEntry *tle;
+ Node *node;
+ FuncCall *fn = makeFuncCall(list_make1(makeString("count")), NIL, -1);
+
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+
+ enr->md.name = trigdata->tg_trigger->tgoldtable;
+ enr->md.reliddesc = trigdata->tg_relation->rd_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(trigdata->tg_oldtable);
+ enr->reldata = trigdata->tg_oldtable;
+ register_ENR(queryEnv, enr);
+
+ rte = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ old_delta_qry->rtable = lappend(old_delta_qry->rtable, rte);
+
+ foreach(lc, old_delta_qry->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+ if (r->relid == relid)
+ {
+ lfirst(lc) = rte;
+ break;
+ }
+ }
+
+ fn->agg_star = true;
+ old_delta_qry->groupClause = transformDistinctClause(NULL, &old_delta_qry->targetList, old_delta_qry->sortClause, false);
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle = makeTargetEntry((Expr *) node,
+ list_length(old_delta_qry->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ old_delta_qry->targetList = lappend(old_delta_qry->targetList, tle);
+ old_delta_qry->hasAggs = true;
+ }
+
+
+ /*
+ * Check for active uses of the relation in the current transaction, such
+ * as open scans.
+ *
+ * NB: We count on this to protect us against problems with refreshing the
+ * data using TABLE_INSERT_FROZEN.
+ */
+ CheckTableNotInUse(matviewRel, "REFRESH MATERIALIZED VIEW");
+
+ relowner = matviewRel->rd_rel->relowner;
+
+ /*
+ * Switch to the owner's userid, so that any functions are run as that
+ * user. Also arrange to make GUC variable changes local to this command.
+ * Don't lock it down too tight to create a temporary table just yet. We
+ * will switch modes when we are about to execute user code.
+ */
+ GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ SetUserIdAndSecContext(relowner,
+ save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+ save_nestlevel = NewGUCNestLevel();
+
+ tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
+ relpersistence = RELPERSISTENCE_TEMP;
+
+ /*
+ * Create the transient table that will receive the regenerated data. Lock
+ * it against access by any other process until commit (by which time it
+ * will be gone).
+ */
+ if (trigdata->tg_newtable)
+ {
+ OIDDelta_new = make_new_heap(matviewOid, tableSpace, relpersistence,
+ ExclusiveLock);
+ LockRelationOid(OIDDelta_new, AccessExclusiveLock);
+ dest_new = CreateTransientRelDestReceiver(OIDDelta_new);
+ }
+ if (trigdata->tg_oldtable)
+ {
+ if (trigdata->tg_newtable)
+ OIDDelta_old = make_new_heap(OIDDelta_new, tableSpace, relpersistence,
+ ExclusiveLock);
+ else
+ OIDDelta_old = make_new_heap(matviewOid, tableSpace, relpersistence,
+ ExclusiveLock);
+ LockRelationOid(OIDDelta_old, AccessExclusiveLock);
+ dest_old = CreateTransientRelDestReceiver(OIDDelta_old);
+ }
+
+ /*
+ * Now lock down security-restricted operations.
+ */
+ SetUserIdAndSecContext(relowner,
+ save_sec_context | SECURITY_RESTRICTED_OPERATION);
+
+ /* Generate the data. */
+ if (trigdata->tg_newtable)
+ refresh_matview_datafill(dest_new, new_delta_qry, queryEnv, NULL);
+ if (trigdata->tg_oldtable)
+ refresh_matview_datafill(dest_old, old_delta_qry, queryEnv, NULL);
+
+ PG_TRY();
+ {
+ apply_delta(matviewOid, OIDDelta_new, OIDDelta_old, relowner,
+ save_sec_context);
+ }
+ PG_CATCH();
+ {
+ matview_maintenance_depth = old_depth;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ heap_close(matviewRel, NoLock);
+
+ /* Roll back any GUC changes */
+ AtEOXact_GUC(false, save_nestlevel);
+
+ /* Restore userid and security context */
+ SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ return PointerGetDatum(NULL);
+}
+
+static void
+apply_delta(Oid matviewOid, Oid tempOid_new, Oid tempOid_old,
+ Oid relowner, int save_sec_context)
+{
+ StringInfoData querybuf;
+ StringInfoData mvatts_buf, diffatts_buf;
+ Relation matviewRel;
+ Relation tempRel_new = NULL, tempRel_old = NULL;
+ char *matviewname;
+ char *tempname_new = NULL, *tempname_old = NULL;
+ int i;
+
+
+ initStringInfo(&querybuf);
+ matviewRel = heap_open(matviewOid, NoLock);
+ matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+
+ if (OidIsValid(tempOid_new))
+ {
+ tempRel_new = heap_open(tempOid_new, NoLock);
+ tempname_new = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(tempRel_new)),
+ RelationGetRelationName(tempRel_new));
+ }
+ if (OidIsValid(tempOid_old))
+ {
+ tempRel_old = heap_open(tempOid_old, NoLock);
+ tempname_old = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(tempRel_old)),
+ RelationGetRelationName(tempRel_old));
+ }
+
+ initStringInfo(&mvatts_buf);
+ initStringInfo(&diffatts_buf);
+ for (i = 0; i < matviewRel->rd_att->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+
+ if (!strcmp(NameStr(attr->attname), "__ivm_count__"))
+ continue;
+
+ if (i > 0)
+ {
+ appendStringInfo(&mvatts_buf, ", ");
+ appendStringInfo(&diffatts_buf, ", ");
+ }
+ appendStringInfo(&mvatts_buf, "mv.%s", NameStr(attr->attname));
+ appendStringInfo(&diffatts_buf, "diff.%s", NameStr(attr->attname));
+ }
+
+ /* Open SPI context. */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /* Analyze the temp table with the new contents. */
+ if (tempname_new)
+ {
+ appendStringInfo(&querybuf, "ANALYZE %s", tempname_new);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+ }
+ if (tempname_old)
+ {
+ resetStringInfo(&querybuf);
+ appendStringInfo(&querybuf, "ANALYZE %s", tempname_old);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+ }
+
+ SetUserIdAndSecContext(relowner,
+ save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+
+ OpenMatViewIncrementalMaintenance();
+
+ if (tempname_old)
+ {
+ resetStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH t AS ("
+ " SELECT diff.__ivm_count__, (diff.__ivm_count__ = mv.__ivm_count__) AS for_dlt, mv.ctid"
+ " FROM %s AS mv, %s AS diff WHERE (%s) = (%s)"
+ "), updt AS ("
+ " UPDATE %s AS mv SET __ivm_count__ = mv.__ivm_count__ - t.__ivm_count__"
+ " FROM t WHERE mv.ctid = t.ctid AND NOT for_dlt"
+ ") DELETE FROM %s AS mv USING t WHERE mv.ctid = t.ctid AND for_dlt;",
+ matviewname, tempname_old, mvatts_buf.data, diffatts_buf.data, matviewname, matviewname);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+ }
+ if (tempname_new)
+ {
+ resetStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH updt AS ("
+ " UPDATE %s AS mv SET __ivm_count__ = mv.__ivm_count__ + diff.__ivm_count__"
+ " FROM %s AS diff WHERE (%s) = (%s)"
+ " RETURNING %s"
+ ") INSERT INTO %s (SELECT * FROM %s AS diff WHERE (%s) NOT IN (SELECT * FROM updt));",
+ matviewname, tempname_new, mvatts_buf.data, diffatts_buf.data, diffatts_buf.data, matviewname, tempname_new, diffatts_buf.data);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+ }
+
+ /* We're done maintaining the materialized view. */
+ CloseMatViewIncrementalMaintenance();
+
+ if (OidIsValid(tempOid_new))
+ heap_close(tempRel_new, NoLock);
+ if (OidIsValid(tempOid_old))
+ heap_close(tempRel_old, NoLock);
+
+ heap_close(matviewRel, NoLock);
+
+ /* Clean up temp tables. */
+ if (OidIsValid(tempOid_new))
+ {
+ resetStringInfo(&querybuf);
+ appendStringInfo(&querybuf, "DROP TABLE %s", tempname_new);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+ }
+ if (OidIsValid(tempOid_old))
+ {
+ resetStringInfo(&querybuf);
+ appendStringInfo(&querybuf, "DROP TABLE %s", tempname_old);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+ }
+
+ /* Close SPI context. */
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 780d7ab00b..7a43e57186 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2364,6 +2364,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_SCALAR_FIELD(relkind);
COPY_SCALAR_FIELD(rellockmode);
COPY_NODE_FIELD(tablesample);
+ COPY_SCALAR_FIELD(relisivm);
COPY_NODE_FIELD(subquery);
COPY_SCALAR_FIELD(security_barrier);
COPY_SCALAR_FIELD(jointype);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 4f2ebe5118..608d477bd5 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2641,6 +2641,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_SCALAR_FIELD(relkind);
COMPARE_SCALAR_FIELD(rellockmode);
COMPARE_NODE_FIELD(tablesample);
+ COMPARE_SCALAR_FIELD(relisivm);
COMPARE_NODE_FIELD(subquery);
COMPARE_SCALAR_FIELD(security_barrier);
COMPARE_SCALAR_FIELD(jointype);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 387e4b9b71..2321d3f685 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3045,6 +3045,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
WRITE_CHAR_FIELD(relkind);
WRITE_INT_FIELD(rellockmode);
WRITE_NODE_FIELD(tablesample);
+ WRITE_BOOL_FIELD(relisivm);
break;
case RTE_SUBQUERY:
WRITE_NODE_FIELD(subquery);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 3b96492b36..a7bb7dce81 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1366,6 +1366,7 @@ _readRangeTblEntry(void)
READ_CHAR_FIELD(relkind);
READ_INT_FIELD(rellockmode);
READ_NODE_FIELD(tablesample);
+ READ_BOOL_FIELD(relisivm);
break;
case RTE_SUBQUERY:
READ_NODE_FIELD(subquery);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3dc0e8a4fb..61704c5c90 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -418,6 +418,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <range> OptTempTableName
%type <into> into_clause create_as_target create_mv_target
+%type <boolean> incremental
%type <defelt> createfunc_opt_item common_func_opt_item dostmt_opt_item
%type <fun_param> func_arg func_arg_with_default table_func_column aggr_arg
@@ -645,7 +646,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
- INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
+ INCLUDING INCREMENT INCREMENTAL INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -4054,30 +4055,32 @@ opt_with_data:
*****************************************************************************/
CreateMatViewStmt:
- CREATE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data
+ CREATE OptNoLog incremental MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data
{
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
- ctas->query = $7;
- ctas->into = $5;
+ ctas->query = $8;
+ ctas->into = $6;
ctas->relkind = OBJECT_MATVIEW;
ctas->is_select_into = false;
ctas->if_not_exists = false;
/* cram additional flags into the IntoClause */
- $5->rel->relpersistence = $2;
- $5->skipData = !($8);
+ $6->rel->relpersistence = $2;
+ $6->skipData = !($9);
+ $6->ivm = $3;
$$ = (Node *) ctas;
}
- | CREATE OptNoLog MATERIALIZED VIEW IF_P NOT EXISTS create_mv_target AS SelectStmt opt_with_data
+ | CREATE OptNoLog incremental MATERIALIZED VIEW IF_P NOT EXISTS create_mv_target AS SelectStmt opt_with_data
{
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
- ctas->query = $10;
- ctas->into = $8;
+ ctas->query = $11;
+ ctas->into = $9;
ctas->relkind = OBJECT_MATVIEW;
ctas->is_select_into = false;
ctas->if_not_exists = true;
/* cram additional flags into the IntoClause */
- $8->rel->relpersistence = $2;
- $8->skipData = !($11);
+ $9->rel->relpersistence = $2;
+ $9->skipData = !($12);
+ $9->ivm = $3;
$$ = (Node *) ctas;
}
;
@@ -4094,9 +4097,14 @@ create_mv_target:
$$->tableSpaceName = $5;
$$->viewQuery = NULL; /* filled at analysis time */
$$->skipData = false; /* might get changed later */
+ $$->ivm = false;
}
;
+incremental: INCREMENTAL { $$ = true; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
+
OptNoLog: UNLOGGED { $$ = RELPERSISTENCE_UNLOGGED; }
| /*EMPTY*/ { $$ = RELPERSISTENCE_PERMANENT; }
;
@@ -15127,6 +15135,7 @@ unreserved_keyword:
| INCLUDE
| INCLUDING
| INCREMENT
+ | INCREMENTAL
| INDEX
| INDEXES
| INHERIT
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 0640d11fac..f65c507f49 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -37,6 +37,7 @@
#include "utils/syscache.h"
#include "utils/varlena.h"
+#include "commands/matview.h"
#define MAX_FUZZY_DISTANCE 3
@@ -1238,6 +1239,7 @@ addRangeTableEntry(ParseState *pstate,
rte->relid = RelationGetRelid(rel);
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ rte->relisivm = rel->rd_rel->relisivm;
/*
* Build the list of effective column names using user-supplied aliases
@@ -1317,6 +1319,7 @@ addRangeTableEntryForRelation(ParseState *pstate,
rte->relid = RelationGetRelid(rel);
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ rte->relisivm = rel->rd_rel->relisivm;
/*
* Build the list of effective column names using user-supplied aliases
@@ -2605,6 +2608,9 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
{
Form_pg_attribute attr = TupleDescAttr(tupdesc, varattno);
+ if (!strcmp("__ivm_count__", NameStr(attr->attname)) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
if (attr->attisdropped)
{
if (include_dropped)
diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c
index 6bd889461e..fbab6b41f7 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -765,7 +765,8 @@ checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect,
attr->atttypmod))));
}
- if (i != resultDesc->natts)
+ /* No check for materialized views since this could have special columns for IVM */
+ if ((!isSelect || requireColumnNameMatch) && i != resultDesc->natts)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
isSelect ?
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 39080776b0..38305003d5 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -41,6 +41,8 @@
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "parser/parser.h"
+#include "commands/matview.h"
/* We use a list of these to detect recursion in RewriteQuery */
typedef struct rewrite_event
@@ -1597,6 +1599,50 @@ ApplyRetrieveRule(Query *parsetree,
if (rule->qual != NULL)
elog(ERROR, "cannot handle qualified ON SELECT rule");
+ if (RelationIsIVM(relation))
+ {
+ rule_action = copyObject(linitial(rule->actions));
+
+ if (!rule_action->distinctClause)
+ {
+ StringInfoData str;
+ RawStmt *raw;
+ Query *sub;
+
+ if (rule_action->hasDistinctOn)
+ elog(ERROR, "DISTINCT ON is not supported in IVM");
+
+ initStringInfo(&str);
+ appendStringInfo(&str, "SELECT mv.*, __ivm_count__ FROM %s mv, generate_series(1, mv.__ivm_count__)",
+ quote_qualified_identifier(get_namespace_name(RelationGetNamespace(relation)),
+ RelationGetRelationName(relation)));
+
+ raw = (RawStmt*)linitial(raw_parser(str.data));
+ sub = transformStmt(make_parsestate(NULL),raw->stmt);
+
+ rte = rt_fetch(rt_index, parsetree->rtable);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = sub;
+ rte->security_barrier = RelationIsSecurityView(relation);
+ /* Clear fields that should not be set in a subquery RTE */
+ rte->relid = InvalidOid;
+ rte->relkind = 0;
+ rte->rellockmode = 0;
+ rte->tablesample = NULL;
+ rte->inh = false; /* must not be set for a subquery */
+
+ rte->requiredPerms = 0; /* no permission check on subquery itself */
+ rte->checkAsUser = InvalidOid;
+ rte->selectedCols = NULL;
+ rte->insertedCols = NULL;
+ rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
+ }
+
+ return parsetree;
+ }
+
if (rt_index == parsetree->resultRelation)
{
/*
@@ -1906,7 +1952,8 @@ fireRIRrules(Query *parsetree, List *activeRIRs)
* In that case this test would need to be postponed till after we've
* opened the rel, so that we could check its state.
*/
- if (rte->relkind == RELKIND_MATVIEW)
+ if (rte->relkind == RELKIND_MATVIEW &&
+ (!rte->relisivm || MatViewIncrementalMaintenanceIsEnabled() || parsetree->commandType != CMD_SELECT))
continue;
/*
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index b4f2d0f35a..82a2fd99bd 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1844,6 +1844,30 @@ get_rel_relispartition(Oid relid)
return false;
}
+/*
+ * get_rel_relisivm
+ *
+ * Returns the relisivm flag associated with a given relation.
+ */
+bool
+get_rel_relisivm(Oid relid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_class reltup = (Form_pg_class) GETSTRUCT(tp);
+ bool result;
+
+ result = reltup->relisivm;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return false;
+}
+
/*
* get_rel_tablespace
*
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index eaf2b18820..b3586cb273 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1861,6 +1861,8 @@ formrdesc(const char *relationName, Oid relationReltype,
/* ... and they're always populated, too */
relation->rd_rel->relispopulated = true;
+ /* ... and they're always no ivm, too */
+ relation->rd_rel->relisivm = false;
relation->rd_rel->relreplident = REPLICA_IDENTITY_NOTHING;
relation->rd_rel->relpages = 0;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index e4c03de221..7bead138d0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -985,6 +985,7 @@ static const pgsql_thing_t words_after_create[] = {
{"FOREIGN TABLE", NULL, NULL, NULL},
{"FUNCTION", NULL, NULL, Query_for_list_of_functions},
{"GROUP", Query_for_list_of_roles},
+ {"INCREMENTAL MATERIALIZED VIEW", NULL, NULL, &Query_for_list_of_matviews},
{"INDEX", NULL, NULL, &Query_for_list_of_indexes},
{"LANGUAGE", Query_for_list_of_languages},
{"LARGE OBJECT", NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
@@ -2467,7 +2468,7 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW");
/* Complete "CREATE UNLOGGED" with TABLE or MATVIEW */
else if (TailMatches("CREATE", "UNLOGGED"))
- COMPLETE_WITH("TABLE", "MATERIALIZED VIEW");
+ COMPLETE_WITH("TABLE", "MATERIALIZED VIEW", "INCREMENTAL MATERIALIZED VIEW");
/* Complete PARTITION BY with RANGE ( or LIST ( or ... */
else if (TailMatches("PARTITION", "BY"))
COMPLETE_WITH("RANGE (", "LIST (", "HASH (");
@@ -2672,13 +2673,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", "INCREMENTAL", "MATERIALIZED"))
COMPLETE_WITH("VIEW");
- /* Complete CREATE MATERIALIZED VIEW <name> with AS */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+ /* Complete CREATE MATERIALIZED VIEW <name> with AS */
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) ||
+ Matches("CREATE", "INCREMENTAL", "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", "INCREMENTAL", "MATERIALIZED", "VIEW", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
/* CREATE EVENT TRIGGER */
diff --git a/src/include/catalog/pg_class.dat b/src/include/catalog/pg_class.dat
index 9bcf28676d..7deda405af 100644
--- a/src/include/catalog/pg_class.dat
+++ b/src/include/catalog/pg_class.dat
@@ -27,7 +27,7 @@
relpersistence => 'p', relkind => 'r', relnatts => '31', relchecks => '0',
relhasrules => 'f', relhastriggers => 'f', relhassubclass => 'f',
relrowsecurity => 'f', relforcerowsecurity => 'f', relispopulated => 't',
- relreplident => 'n', relispartition => 'f', relfrozenxid => '3',
+ relreplident => 'n', relispartition => 'f', relisivm => 'f', relfrozenxid => '3',
relminmxid => '1', relacl => '_null_', reloptions => '_null_',
relpartbound => '_null_' },
{ oid => '1249',
@@ -37,7 +37,7 @@
relpersistence => 'p', relkind => 'r', relnatts => '25', relchecks => '0',
relhasrules => 'f', relhastriggers => 'f', relhassubclass => 'f',
relrowsecurity => 'f', relforcerowsecurity => 'f', relispopulated => 't',
- relreplident => 'n', relispartition => 'f', relfrozenxid => '3',
+ relreplident => 'n', relispartition => 'f', relisivm => 'f', relfrozenxid => '3',
relminmxid => '1', relacl => '_null_', reloptions => '_null_',
relpartbound => '_null_' },
{ oid => '1255',
@@ -47,17 +47,17 @@
relpersistence => 'p', relkind => 'r', relnatts => '29', relchecks => '0',
relhasrules => 'f', relhastriggers => 'f', relhassubclass => 'f',
relrowsecurity => 'f', relforcerowsecurity => 'f', relispopulated => 't',
- relreplident => 'n', relispartition => 'f', relfrozenxid => '3',
+ relreplident => 'n', relispartition => 'f', relisivm => 'f', relfrozenxid => '3',
relminmxid => '1', relacl => '_null_', reloptions => '_null_',
relpartbound => '_null_' },
{ oid => '1259',
relname => 'pg_class', reltype => 'pg_class', relam => 'heap',
relfilenode => '0', relpages => '0', reltuples => '0', relallvisible => '0',
reltoastrelid => '0', relhasindex => 'f', relisshared => 'f',
- relpersistence => 'p', relkind => 'r', relnatts => '33', relchecks => '0',
+ relpersistence => 'p', relkind => 'r', relnatts => '34', relchecks => '0',
relhasrules => 'f', relhastriggers => 'f', relhassubclass => 'f',
relrowsecurity => 'f', relforcerowsecurity => 'f', relispopulated => 't',
- relreplident => 'n', relispartition => 'f', relfrozenxid => '3',
+ relreplident => 'n', relispartition => 'f', relisivm => 'f', relfrozenxid => '3',
relminmxid => '1', relacl => '_null_', reloptions => '_null_',
relpartbound => '_null_' },
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 090b6ba907..ff535f5504 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -116,6 +116,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat
/* is relation a partition? */
bool relispartition;
+ /* is relation a matview with ivm? */
+ bool relisivm;
+
/* heap for rewrite during DDL, link to original rel */
Oid relrewrite BKI_DEFAULT(0);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87335248a0..5de996a72c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10677,4 +10677,9 @@
proname => 'pg_partition_root', prorettype => 'regclass',
proargtypes => 'regclass', prosrc => 'pg_partition_root' },
+# IVM
+{ oid => '784', descr => 'ivm trigger',
+ proname => 'IVM_immediate_maintenance', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_maintenance' },
+
]
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index d4461dd802..779037e3c3 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -23,6 +23,8 @@
extern void SetMatViewPopulatedState(Relation relation, bool newstate);
+extern void SetMatViewIVMState(Relation relation, bool newstate);
+
extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
ParamListInfo params, char *completionTag);
@@ -30,4 +32,6 @@ extern DestReceiver *CreateTransientRelDestReceiver(Oid oid);
extern bool MatViewIncrementalMaintenanceIsEnabled(void);
+extern Datum IVM_immediate_maintenance(PG_FUNCTION_ARGS);
+
#endif /* MATVIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 12e9730dd0..745d048d37 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1002,6 +1002,7 @@ typedef struct RangeTblEntry
char relkind; /* relation kind (see pg_class.relkind) */
int rellockmode; /* lock level that query requires on the rel */
struct TableSampleClause *tablesample; /* sampling info, or NULL */
+ bool relisivm;
/*
* Fields valid for a subquery RTE (else NULL):
@@ -2059,6 +2060,7 @@ typedef struct CreateStmt
char *tablespacename; /* table space to use, or NULL */
char *accessMethod; /* table access method */
bool if_not_exists; /* just do nothing if it already exists? */
+ bool ivm; /* incremental view maintenance is used by materialized view */
} CreateStmt;
/* ----------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index f9b1cf2df7..105c2af4ce 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -117,6 +117,7 @@ typedef struct IntoClause
char *tableSpaceName; /* table space to use, or NULL */
Node *viewQuery; /* materialized view's SELECT query */
bool skipData; /* true for WITH NO DATA */
+ bool ivm; /* true for WITH IVM */
} IntoClause;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 00ace8425e..d682ee11cc 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -198,6 +198,7 @@ PG_KEYWORD("in", IN_P, RESERVED_KEYWORD)
PG_KEYWORD("include", INCLUDE, UNRESERVED_KEYWORD)
PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD)
PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD)
+PG_KEYWORD("incremental", INCREMENTAL, UNRESERVED_KEYWORD)
PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD)
PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD)
PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 9606d021b1..c66cc81141 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -129,6 +129,7 @@ extern Oid get_rel_namespace(Oid relid);
extern Oid get_rel_type_id(Oid relid);
extern char get_rel_relkind(Oid relid);
extern bool get_rel_relispartition(Oid relid);
+extern bool get_rel_relisivm(Oid relid);
extern Oid get_rel_tablespace(Oid relid);
extern char get_rel_persistence(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index d7f33abce3..057f38d5de 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -562,6 +562,8 @@ typedef struct ViewOptions
*/
#define RelationIsPopulated(relation) ((relation)->rd_rel->relispopulated)
+#define RelationIsIVM(relation) ((relation)->rd_rel->relisivm)
+
/*
* RelationIsAccessibleInLogicalDecoding
* True if we need to log enough information to have access via
