public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yugo NAGATA <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Greg Stark <[email protected]>
Cc: Zhihong Yu <[email protected]>
Cc: Julien Rouhaud <[email protected]>
Cc: [email protected] <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Andy Fan <[email protected]>
Cc: Tatsuo Ishii <[email protected]>
Cc: Thomas Munro <[email protected]>
Subject: Re: Implementing Incremental View Maintenance
Date: Fri, 22 Apr 2022 11:29:39 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAM-w4HM86ce16Sk9A9MRy4xsLk3a3WjNYZct78yEbaR3uajz4Q@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CALNJ-vTaGk4AvjrCPPkW_GyWQ+KeW_dFA=hakcxfy=rGF28TWQ@mail.gmail.com>
<CALNJ-vRSVtqZDXU6-DjdycFkjonxKs2TbBFb=W=W9Y7KG0rRwA@mail.gmail.com>
<OS0PR01MB56829A65926C0FF7CF00F5EB82D29@OS0PR01MB5682.jpnprd01.prod.outlook.com>
<[email protected]>
<OS0PR01MB5682B499319EBEB59F416AD582619@OS0PR01MB5682.jpnprd01.prod.outlook.com>
<[email protected]>
<20220113102342.mlhknuy6ppypflq3@jrouhaud>
<[email protected]>
<CALNJ-vRkr8YbHMwWCo_PDGetzHZ68kxO+9gHpMmVjL=ws=iKYg@mail.gmail.com>
<[email protected]>
<CALNJ-vQyjRkCUbdKXHV-ReBpH_sEkfC2OE3cjx9wKjPm41a8OQ@mail.gmail.com>
<[email protected]>
<CAM-w4HM86ce16Sk9A9MRy4xsLk3a3WjNYZct78yEbaR3uajz4Q@mail.gmail.com>
Hi,
On Fri, 1 Apr 2022 11:09:16 -0400
Greg Stark <[email protected]> wrote:
> This patch has bitrotted due to some other patch affecting trigger.c.
>
> Could you post a rebase?
>
> This is the last week of the CF before feature freeze so time is of the essence.
I attached a rebased patch-set.
Also, I made the folowing changes from the previous.
1. Fix to not use a new deptye
In the previous patch, we introduced a new deptye 'm' into pg_depend.
This deptype was used for looking for IVM triggers to be removed at
REFRESH WITH NO DATA. However, we decided to not use it for reducing
unnecessary change in the core code. Currently, the trigger name and
dependent objclass are used at that time instead of it.
As a result, the number of patches are reduced to nine from ten.
2. Bump the version numbers in psql and pg_dump
This feature's target is PG 16 now.
Regards,
Yugo Nagata
--
Yugo NAGATA <[email protected]>
Attachments:
[text/x-diff] v27-0001-Add-a-syntax-to-create-Incrementally-Maintainabl.patch (6.7K, ../[email protected]/2-v27-0001-Add-a-syntax-to-create-Incrementally-Maintainabl.patch)
download | inline diff:
From 5437965959d6dc512328ffee8ff9dea6b22e48f8 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Fri, 20 Dec 2019 10:05:02 +0900
Subject: [PATCH v27 1/9] Add a syntax to create Incrementally Maintainable
Materialized Views
Allow to create Incrementally Maintainable Materialized View (IMMV)
by using INCREMENTAL option in CREATE MATERIALIZED VIEW command
as follow:
CREATE [INCREMANTAL] MATERIALIZED VIEW xxxxx AS SELECT ....;
---
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/parser/gram.y | 32 +++++++++++++++++++++-----------
src/include/nodes/primnodes.h | 1 +
src/include/parser/kwlist.h | 1 +
7 files changed, 27 insertions(+), 11 deletions(-)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 836f427ea8..0490bce664 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1434,6 +1434,7 @@ _copyIntoClause(const IntoClause *from)
COPY_STRING_FIELD(tableSpaceName);
COPY_NODE_FIELD(viewQuery);
COPY_SCALAR_FIELD(skipData);
+ COPY_SCALAR_FIELD(ivm);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e013c1bbfe..55f41263ee 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -211,6 +211,7 @@ _equalIntoClause(const IntoClause *a, const IntoClause *b)
COMPARE_STRING_FIELD(tableSpaceName);
COMPARE_NODE_FIELD(viewQuery);
COMPARE_SCALAR_FIELD(skipData);
+ COMPARE_SCALAR_FIELD(ivm);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 6a02f81ad5..cfd3ce68b4 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1127,6 +1127,7 @@ _outIntoClause(StringInfo str, const IntoClause *node)
WRITE_STRING_FIELD(tableSpaceName);
WRITE_NODE_FIELD(viewQuery);
WRITE_BOOL_FIELD(skipData);
+ WRITE_BOOL_FIELD(ivm);
}
static void
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index ddf76ac778..5165fb3b93 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -605,6 +605,7 @@ _readIntoClause(void)
READ_STRING_FIELD(tableSpaceName);
READ_NODE_FIELD(viewQuery);
READ_BOOL_FIELD(skipData);
+ READ_BOOL_FIELD(ivm);
READ_DONE();
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c9941d9cb4..aa134eafc9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -468,6 +468,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
@@ -801,7 +802,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
@@ -4492,30 +4493,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->objtype = 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->objtype = 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;
}
;
@@ -4532,9 +4535,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; }
;
@@ -17007,6 +17015,7 @@ unreserved_keyword:
| INCLUDE
| INCLUDING
| INCREMENT
+ | INCREMENTAL
| INDEX
| INDEXES
| INHERIT
@@ -17588,6 +17597,7 @@ bare_label_keyword:
| INCLUDE
| INCLUDING
| INCREMENT
+ | INCREMENTAL
| INDEX
| INDEXES
| INHERIT
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 66d32fc006..acac38b91c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -126,6 +126,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 8a2ab405a2..7cd9b54a01 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -210,6 +210,7 @@ PG_KEYWORD("in", IN_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("include", INCLUDE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("incremental", INCREMENTAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD, BARE_LABEL)
--
2.17.1
[text/x-diff] v27-0002-Add-relisivm-column-to-pg_class-system-catalog.patch (4.7K, ../[email protected]/3-v27-0002-Add-relisivm-column-to-pg_class-system-catalog.patch)
download | inline diff:
From bd5bcbb73ab7ab535a71021fa80ef6c0a5543d6a Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Fri, 20 Dec 2019 10:07:23 +0900
Subject: [PATCH v27 2/9] Add relisivm column to pg_class system catalog
If this boolean column is true, a relations is Incrementally Maintainable
Materialized View (IMMV). This is set when IMMV is created.
---
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 1 +
src/backend/utils/cache/lsyscache.c | 24 ++++++++++++++++++++++++
src/backend/utils/cache/relcache.c | 2 ++
src/include/catalog/pg_class.h | 3 +++
src/include/utils/lsyscache.h | 1 +
src/include/utils/rel.h | 2 ++
7 files changed, 34 insertions(+)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 9b512ccd3c..a9a314fefe 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -920,6 +920,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 fd389c28d8..4d1c99a8bd 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -982,6 +982,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/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 1b7e11b93e..3275963886 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2023,6 +2023,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 43f14c233d..a67ba4d8b1 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1923,6 +1923,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/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 304e8c18d5..fbaa1438d6 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -119,6 +119,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat
/* is relation a partition? */
bool relispartition BKI_DEFAULT(f);
+ /* is relation a matview with ivm? */
+ bool relisivm BKI_DEFAULT(f);
+
/* link to original rel during table rewrite; otherwise 0 */
Oid relrewrite BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_class);
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index b8dd27d4a9..cc2f635122 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -137,6 +137,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 eadbd00904..0e28716e6b 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -661,6 +661,8 @@ RelationGetSmgr(Relation rel)
*/
#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
--
2.17.1
[text/x-diff] v27-0003-Allow-to-prolong-life-span-of-transition-tables-.patch (6.2K, ../[email protected]/4-v27-0003-Allow-to-prolong-life-span-of-transition-tables-.patch)
download | inline diff:
From 2ef59a56b5df4f2449e615af36a7245f1d8a9880 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Fri, 20 Dec 2019 10:09:45 +0900
Subject: [PATCH v27 3/9] Allow to prolong life span of transition tables until
transaction end
Originally, tuplestores of AFTER trigger's transition tables were
freed for each query depth. For our IVM implementation, we would like
to prolong life of the tuplestores because we have to preserve them
for a whole query assuming that some base tables might be changed
in some trigger functions.
---
src/backend/commands/trigger.c | 83 ++++++++++++++++++++++++++++++++--
src/include/commands/trigger.h | 2 +
2 files changed, 81 insertions(+), 4 deletions(-)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 13cb516752..aa1da7b279 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -3730,6 +3730,10 @@ typedef struct AfterTriggerEventList
* end of the list, so it is relatively easy to discard them. The event
* list chunks themselves are stored in event_cxt.
*
+ * prolonged_tuplestored is a list of transition table tuplestores whose
+ * life are prolonged to the end of the outmost query instead of each nested
+ * query.
+ *
* query_depth is the current depth of nested AfterTriggerBeginQuery calls
* (-1 when the stack is empty).
*
@@ -3795,6 +3799,7 @@ typedef struct AfterTriggersData
SetConstraintState state; /* the active S C state */
AfterTriggerEventList events; /* deferred-event list */
MemoryContext event_cxt; /* memory context for events, if any */
+ List *prolonged_tuplestores; /* list of prolonged tuplestores */
/* per-query-level data: */
AfterTriggersQueryData *query_stack; /* array of structs shown below */
@@ -3830,6 +3835,7 @@ struct AfterTriggersTableData
bool closed; /* true when no longer OK to add tuples */
bool before_trig_done; /* did we already queue BS triggers? */
bool after_trig_done; /* did we already queue AS triggers? */
+ bool prolonged; /* are transition tables prolonged? */
AfterTriggerEventList after_trig_events; /* if so, saved list pointer */
/*
@@ -3879,6 +3885,7 @@ static void TransitionTableAddTuple(EState *estate,
TupleTableSlot *original_insert_tuple,
Tuplestorestate *tuplestore);
static void AfterTriggerFreeQuery(AfterTriggersQueryData *qs);
+static void release_or_prolong_tuplestore(Tuplestorestate *ts, bool prolonged);
static SetConstraintState SetConstraintStateCreate(int numalloc);
static SetConstraintState SetConstraintStateCopy(SetConstraintState state);
static SetConstraintState SetConstraintStateAddItem(SetConstraintState state,
@@ -4725,6 +4732,45 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events,
}
+/*
+ * SetTransitionTablePreserved
+ *
+ * Prolong lifespan of transition tables corresponding specified relid and
+ * command type to the end of the outmost query instead of each nested query.
+ * This enables to use nested AFTER trigger's transition tables from outer
+ * query's triggers. Currently, only immediate incremental view maintenance
+ * uses this.
+ */
+void
+SetTransitionTablePreserved(Oid relid, CmdType cmdType)
+{
+ AfterTriggersTableData *table;
+ AfterTriggersQueryData *qs;
+ bool found = false;
+ ListCell *lc;
+
+ /* Check state, like AfterTriggerSaveEvent. */
+ if (afterTriggers.query_depth < 0)
+ elog(ERROR, "SetTransitionTablePreserved() called outside of query");
+
+ qs = &afterTriggers.query_stack[afterTriggers.query_depth];
+
+ foreach(lc, qs->tables)
+ {
+ table = (AfterTriggersTableData *) lfirst(lc);
+ if (table->relid == relid && table->cmdType == cmdType &&
+ table->closed)
+ {
+ table->prolonged = true;
+ found = true;
+ }
+ }
+
+ if (!found)
+ elog(ERROR,"could not find table with OID %d and command type %d", relid, cmdType);
+}
+
+
/*
* GetAfterTriggersTableData
*
@@ -4933,6 +4979,7 @@ AfterTriggerBeginXact(void)
*/
afterTriggers.firing_counter = (CommandId) 1; /* mustn't be 0 */
afterTriggers.query_depth = -1;
+ afterTriggers.prolonged_tuplestores = NIL;
/*
* Verify that there is no leftover state remaining. If these assertions
@@ -5093,19 +5140,19 @@ AfterTriggerFreeQuery(AfterTriggersQueryData *qs)
ts = table->old_upd_tuplestore;
table->old_upd_tuplestore = NULL;
if (ts)
- tuplestore_end(ts);
+ release_or_prolong_tuplestore(ts, table->prolonged);
ts = table->new_upd_tuplestore;
table->new_upd_tuplestore = NULL;
if (ts)
- tuplestore_end(ts);
+ release_or_prolong_tuplestore(ts, table->prolonged);
ts = table->old_del_tuplestore;
table->old_del_tuplestore = NULL;
if (ts)
- tuplestore_end(ts);
+ release_or_prolong_tuplestore(ts, table->prolonged);
ts = table->new_ins_tuplestore;
table->new_ins_tuplestore = NULL;
if (ts)
- tuplestore_end(ts);
+ release_or_prolong_tuplestore(ts, table->prolonged);
if (table->storeslot)
ExecDropSingleTupleTableSlot(table->storeslot);
}
@@ -5117,6 +5164,34 @@ AfterTriggerFreeQuery(AfterTriggersQueryData *qs)
*/
qs->tables = NIL;
list_free_deep(tables);
+
+ /* Release prolonged tuplestores at the end of the outmost query */
+ if (afterTriggers.query_depth == 0)
+ {
+ foreach(lc, afterTriggers.prolonged_tuplestores)
+ {
+ ts = (Tuplestorestate *) lfirst(lc);
+ if (ts)
+ tuplestore_end(ts);
+ }
+ afterTriggers.prolonged_tuplestores = NIL;
+ }
+}
+
+/*
+ * Release the tuplestore, or append it to the prolonged tuplestores list.
+ */
+static void
+release_or_prolong_tuplestore(Tuplestorestate *ts, bool prolonged)
+{
+ if (prolonged && afterTriggers.query_depth > 0)
+ {
+ MemoryContext oldcxt = MemoryContextSwitchTo(CurTransactionContext);
+ afterTriggers.prolonged_tuplestores = lappend(afterTriggers.prolonged_tuplestores, ts);
+ MemoryContextSwitchTo(oldcxt);
+ }
+ else
+ tuplestore_end(ts);
}
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 194e8d5bc1..2e9f1ac8c7 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -261,6 +261,8 @@ extern void AfterTriggerEndSubXact(bool isCommit);
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
extern bool AfterTriggerPendingOnRel(Oid relid);
+extern void SetTransitionTablePreserved(Oid relid, CmdType cmdType);
+
/*
* in utils/adt/ri_triggers.c
--
2.17.1
[text/x-diff] v27-0004-Add-Incremental-View-Maintenance-support-to-pg_d.patch (4.1K, ../[email protected]/5-v27-0004-Add-Incremental-View-Maintenance-support-to-pg_d.patch)
download | inline diff:
From df9faf0cc923c6aaa26f13b9334444add42f4dbe Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Wed, 11 Nov 2020 17:01:25 +0900
Subject: [PATCH v27 4/9] Add Incremental View Maintenance support to pg_dump
Support CREATE INCREMENTAL MATERIALIZED VIEW syntax.
---
src/bin/pg_dump/pg_dump.c | 18 +++++++++++++++---
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 15 +++++++++++++++
3 files changed, 31 insertions(+), 3 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 969e2a7a46..3c764076d2 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6089,6 +6089,7 @@ getTables(Archive *fout, int *numTables)
int i_relacl;
int i_acldefault;
int i_ispartition;
+ int i_isivm;
/*
* Find all the tables and table-like objects.
@@ -6191,10 +6192,17 @@ getTables(Archive *fout, int *numTables)
if (fout->remoteVersion >= 100000)
appendPQExpBufferStr(query,
- "c.relispartition AS ispartition ");
+ "c.relispartition AS ispartition, ");
else
appendPQExpBufferStr(query,
- "false AS ispartition ");
+ "false AS ispartition, ");
+
+ if (fout->remoteVersion >= 160000)
+ appendPQExpBufferStr(query,
+ "c.relisivm AS isivm ");
+ else
+ appendPQExpBufferStr(query,
+ "false AS isivm ");
/*
* Left join to pg_depend to pick up dependency info linking sequences to
@@ -6303,6 +6311,7 @@ getTables(Archive *fout, int *numTables)
i_relacl = PQfnumber(res, "relacl");
i_acldefault = PQfnumber(res, "acldefault");
i_ispartition = PQfnumber(res, "ispartition");
+ i_isivm = PQfnumber(res, "isivm");
if (dopt->lockWaitTimeout)
{
@@ -6380,6 +6389,7 @@ getTables(Archive *fout, int *numTables)
tblinfo[i].amname = pg_strdup(PQgetvalue(res, i, i_amname));
tblinfo[i].is_identity_sequence = (strcmp(PQgetvalue(res, i, i_is_identity_sequence), "t") == 0);
tblinfo[i].ispartition = (strcmp(PQgetvalue(res, i, i_ispartition), "t") == 0);
+ tblinfo[i].isivm = (strcmp(PQgetvalue(res, i, i_isivm), "t") == 0);
/* other fields were zeroed above */
@@ -15115,9 +15125,11 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
binary_upgrade_set_pg_class_oids(fout, q,
tbinfo->dobj.catId.oid, false);
- appendPQExpBuffer(q, "CREATE %s%s %s",
+ appendPQExpBuffer(q, "CREATE %s%s%s %s",
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
"UNLOGGED " : "",
+ tbinfo->relkind == RELKIND_MATVIEW && tbinfo->isivm ?
+ "INCREMENTAL " : "",
reltypename,
qualrelname);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 1d21c2906f..0b798ad5de 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -318,6 +318,7 @@ typedef struct _tableInfo
bool dummy_view; /* view's real definition must be postponed */
bool postponed_def; /* matview must be postponed into post-data */
bool ispartition; /* is table a partition? */
+ bool isivm; /* is incrementally maintainable materialized view? */
/*
* These fields are computed only if we decide the table is interesting
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index c65c92bfb0..3655727472 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2295,6 +2295,21 @@ my %tests = (
{ exclude_dump_test_schema => 1, no_toast_compression => 1, },
},
+ 'CREATE MATERIALIZED VIEW matview_ivm' => {
+ create_order => 21,
+ create_sql => 'CREATE INCREMENTAL MATERIALIZED VIEW dump_test.matview_ivm (col1) AS
+ SELECT col1 FROM dump_test.test_table;',
+ regexp => qr/^
+ \QCREATE INCREMENTAL MATERIALIZED VIEW dump_test.matview_ivm AS\E
+ \n\s+\QSELECT test_table.col1\E
+ \n\s+\QFROM dump_test.test_table\E
+ \n\s+\QWITH NO DATA;\E
+ /xm,
+ like =>
+ { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
+ unlike => { exclude_dump_test_schema => 1, },
+ },
+
'CREATE POLICY p1 ON test_table' => {
create_order => 22,
create_sql => 'CREATE POLICY p1 ON dump_test.test_table
--
2.17.1
[text/x-diff] v27-0005-Add-Incremental-View-Maintenance-support-to-psql.patch (4.8K, ../[email protected]/6-v27-0005-Add-Incremental-View-Maintenance-support-to-psql.patch)
download | inline diff:
From 33a9b0fcb9c62d7a1ce23b3fc0bce47f86ac9c3d Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Fri, 20 Dec 2019 10:21:54 +0900
Subject: [PATCH v27 5/9] Add Incremental View Maintenance support to psql
Add tab completion and meta-command output for IVM.
---
src/bin/psql/describe.c | 32 +++++++++++++++++++++++++++++++-
src/bin/psql/tab-complete.c | 14 +++++++++-----
2 files changed, 40 insertions(+), 6 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 583817b0cc..aacfe8b82d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1492,6 +1492,7 @@ describeOneTableDetails(const char *schemaname,
char relpersistence;
char relreplident;
char *relam;
+ bool isivm;
} tableinfo;
bool show_column_details = false;
@@ -1504,7 +1505,26 @@ describeOneTableDetails(const char *schemaname,
initPQExpBuffer(&tmpbuf);
/* Get general table info */
- if (pset.sversion >= 120000)
+ if (pset.sversion >= 160000)
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
+ "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
+ "false AS relhasoids, c.relispartition, %s, c.reltablespace, "
+ "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
+ "c.relpersistence, c.relreplident, am.amname, "
+ "c.relisivm\n"
+ "FROM pg_catalog.pg_class c\n "
+ "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
+ "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n"
+ "WHERE c.oid = '%s';",
+ (verbose ?
+ "pg_catalog.array_to_string(c.reloptions || "
+ "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
+ : "''"),
+ oid);
+ }
+ else if (pset.sversion >= 120000)
{
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
@@ -1624,6 +1644,10 @@ describeOneTableDetails(const char *schemaname,
(char *) NULL : pg_strdup(PQgetvalue(res, 0, 14));
else
tableinfo.relam = NULL;
+ if (pset.sversion >= 160000)
+ tableinfo.isivm = strcmp(PQgetvalue(res, 0, 15), "t") == 0;
+ else
+ tableinfo.isivm = false;
PQclear(res);
res = NULL;
@@ -3424,6 +3448,12 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, _("Access method: %s"), tableinfo.relam);
printTableAddFooter(&cont, buf.data);
}
+
+ /* Incremental view maintance info */
+ if (verbose && tableinfo.relkind == RELKIND_MATVIEW && tableinfo.isivm)
+ {
+ printTableAddFooter(&cont, _("Incremental view maintenance: yes"));
+ }
}
/* reloptions, if verbose */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 588c0841fe..87b5d3e107 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1221,6 +1221,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, NULL, THING_NO_DROP | THING_NO_ALTER},
{"INDEX", NULL, NULL, &Query_for_list_of_indexes},
{"LANGUAGE", Query_for_list_of_languages},
{"LARGE OBJECT", NULL, NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
@@ -3074,7 +3075,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 (");
@@ -3390,13 +3391,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 */
--
2.17.1
[text/x-diff] v27-0006-Add-Incremental-View-Maintenance-support.patch (91.9K, ../[email protected]/7-v27-0006-Add-Incremental-View-Maintenance-support.patch)
download | inline diff:
From ec38ae79124c7bccdbd6b93727cda1d26152a16a Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Thu, 21 Apr 2022 11:58:53 +0900
Subject: [PATCH v27 6/9] Add Incremental View Maintenance support
In this implementation, AFTER triggers are used to collect
tuplestores containing transition table contents. When multiple tables
are changed, multiple AFTER triggers are invoked, then the final AFTER
trigger performs actual update of the matview. In addition, BEFORE
triggers are also used to handle global information for view
maintenance. To calculate view deltas, we need both pre-state and
post-state of base tables. Post-update states are available in AFTER
trigger, and pre-update states can be calculated by filtering inserted
tuples using cmin/xmin system columns, and append deleted tuples which
are contained in an old transition table.
This patch also allows self-join, simultaneous updates of more than
one base table, and multiple updates of the same base table.
Incrementally Maintainable Materialized Views (IMMV) can contain
duplicated tuples. Also, DISTINCT clause is supported. When IMMV is
created with DISTINCT, multiplicity of tuples is counted and stored
in "__ivm_count__" column, which is a hidden column of IMMV.
The value in __ivm_count__ is updated when IMMV is maintained
incrementally. A tuple in IMMV can be removed if and only if the
count becomes zero.
---
src/backend/access/transam/xact.c | 5 +
src/backend/commands/createas.c | 751 +++++++++++++
src/backend/commands/indexcmds.c | 40 +
src/backend/commands/matview.c | 1577 ++++++++++++++++++++++++++-
src/backend/commands/tablecmds.c | 9 +
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/parser/parse_relation.c | 18 +-
src/backend/rewrite/rewriteDefine.c | 3 +-
src/include/catalog/pg_proc.dat | 8 +
src/include/commands/createas.h | 6 +
src/include/commands/matview.h | 8 +
src/include/nodes/parsenodes.h | 2 +
15 files changed, 2391 insertions(+), 40 deletions(-)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 53f3e7fd1a..0377f5a88b 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -36,6 +36,7 @@
#include "catalog/pg_enum.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/matview.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
#include "common/pg_prng.h"
@@ -2792,6 +2793,7 @@ AbortTransaction(void)
AtAbort_Notify();
AtEOXact_RelationMap(false, is_parallel_worker);
AtAbort_Twophase();
+ AtAbort_IVM();
/*
* Advertise the fact that we aborted in pg_xact (assuming that we got as
@@ -5032,6 +5034,9 @@ AbortSubTransaction(void)
AbortBufferIO();
UnlockBuffers();
+ /* Clean up hash entries for incremental view maintenance */
+ AtAbort_IVM();
+
/* Reset WAL record construction state */
XLogResetInsertion();
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 9abbb6b555..1224a3b075 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -32,24 +32,41 @@
#include "access/xact.h"
#include "access/xlog.h"
#include "catalog/namespace.h"
+#include "catalog/index.h"
+#include "catalog/pg_constraint.h"
+#include "catalog/pg_inherits.h"
+#include "catalog/pg_trigger.h"
+#include "catalog/pg_type.h"
#include "catalog/toasting.h"
#include "commands/createas.h"
+#include "commands/defrem.h"
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "commands/view.h"
#include "miscadmin.h"
+#include "optimizer/clauses.h"
+#include "optimizer/optimizer.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
#include "parser/parse_clause.h"
+#include "parser/parse_func.h"
+#include "parser/parse_type.h"
#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/rls.h"
#include "utils/snapmgr.h"
+#include "utils/syscache.h"
typedef struct
{
@@ -73,6 +90,13 @@ static bool intorel_receive(TupleTableSlot *slot, DestReceiver *self);
static void intorel_shutdown(DestReceiver *self);
static void intorel_destroy(DestReceiver *self);
+static void CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid matviewOid,
+ Relids *relids, bool ex_lock);
+static void CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock);
+static void check_ivm_restriction(Node *node);
+static bool check_ivm_restriction_walker(Node *node, void *context);
+static Bitmapset *get_primary_key_attnos_from_query(Query *qry, List **constraintList, bool is_create);
+static Bitmapset *get_primary_key_attnos_from_query(Query *query, List **constraintList, bool is_create);
/*
* create_ctas_internal
@@ -108,6 +132,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;
/*
@@ -282,6 +308,21 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
save_nestlevel = NewGUCNestLevel();
}
+ if (is_matview && into->ivm)
+ {
+ /* check if the query is supported in IMMV definition */
+ if (contain_mutable_functions((Node *) query))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("mutable function is not supported on incrementally maintainable materialized view"),
+ errhint("functions must be marked IMMUTABLE")));
+
+ check_ivm_restriction((Node *) query);
+
+ /* For IMMV, we need to rewrite matview query */
+ query = rewriteQueryForIMMV(query, into->colNames);
+ }
+
if (into->skipData)
{
/*
@@ -358,11 +399,74 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
/* Restore userid and security context */
SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ if (into->ivm)
+ {
+ Oid matviewOid = address.objectId;
+ Relation matviewRel = table_open(matviewOid, NoLock);
+
+ /*
+ * Mark relisivm field, if it's a matview and into->ivm is true.
+ */
+ SetMatViewIVMState(matviewRel, true);
+
+ if (!into->skipData)
+ {
+ /* Create an index on incremental maintainable materialized view, if possible */
+ CreateIndexOnIMMV((Query *) into->viewQuery, matviewRel, true);
+
+ /* Create triggers on incremental maintainable materialized view */
+ CreateIvmTriggersOnBaseTables((Query *) into->viewQuery, matviewOid, true);
+ }
+ table_close(matviewRel, NoLock);
+ }
}
return address;
}
+/*
+ * rewriteQueryForIMMV -- rewrite view definition query for IMMV
+ *
+ * count(*) is added for counting distinct tuples in views.
+ */
+Query *
+rewriteQueryForIMMV(Query *query, List *colNames)
+{
+ Query *rewritten;
+
+ TargetEntry *tle;
+ Node *node;
+ ParseState *pstate = make_parsestate(NULL);
+ FuncCall *fn;
+
+ rewritten = copyObject(query);
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ /*
+ * Convert DISTINCT to GROUP BY and add count(*) for counting distinct
+ * tuples in views.
+ */
+ if (rewritten->distinctClause)
+ {
+ rewritten->groupClause = transformDistinctClause(NULL, &rewritten->targetList, rewritten->sortClause, false);
+
+ fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
+ fn->agg_star = true;
+
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle = makeTargetEntry((Expr *) node,
+ list_length(rewritten->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ rewritten->targetList = lappend(rewritten->targetList, tle);
+ rewritten->hasAggs = true;
+ }
+
+ return rewritten;
+}
+
/*
* GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
*
@@ -623,3 +727,650 @@ intorel_destroy(DestReceiver *self)
{
pfree(self);
}
+
+/*
+ * CreateIvmTriggersOnBaseTables -- create IVM triggers on all base tables
+ */
+void
+CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid, bool is_create)
+{
+ Relids relids = NULL;
+ bool ex_lock = false;
+ Index first_rtindex = is_create ? 1 : PRS2_NEW_VARNO + 1;
+ RangeTblEntry *rte;
+
+ /* Immediately return if we don't have any base tables. */
+ if (list_length(qry->rtable) < first_rtindex)
+ return;
+
+ /*
+ * If the view has more than one base tables, we need an exclusive lock
+ * on the view so that the view would be maintained serially to avoid
+ * the inconsistency that occurs when two base tables are modified in
+ * concurrent transactions. However, if the view has only one table,
+ * we can use a weaker lock.
+ *
+ * The type of lock should be determined here, because if we check the
+ * view definition at maintenance time, we need to acquire a weaker lock,
+ * and upgrading the lock level after this increases probability of
+ * deadlock.
+ */
+
+ rte = list_nth(qry->rtable, first_rtindex - 1);
+ if (list_length(qry->rtable) > first_rtindex ||
+ rte->rtekind != RTE_RELATION)
+ ex_lock = true;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, (Node *)qry, matviewOid, &relids, ex_lock);
+
+ bms_free(relids);
+}
+
+static void
+CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid matviewOid,
+ Relids *relids, bool ex_lock)
+{
+ if (node == NULL)
+ return;
+
+ /* This can recurse, so check for excessive recursion */
+ check_stack_depth();
+
+ switch (nodeTag(node))
+ {
+ case T_Query:
+ {
+ Query *query = (Query *) node;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, (Node *)query->jointree, matviewOid, relids, ex_lock);
+ }
+ break;
+
+ case T_RangeTblRef:
+ {
+ int rti = ((RangeTblRef *) node)->rtindex;
+ RangeTblEntry *rte = rt_fetch(rti, qry->rtable);
+
+ if (rte->rtekind == RTE_RELATION && !bms_is_member(rte->relid, *relids))
+ {
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_AFTER, ex_lock);
+
+ *relids = bms_add_member(*relids, rte->relid);
+ }
+ }
+ break;
+
+ case T_FromExpr:
+ {
+ FromExpr *f = (FromExpr *) node;
+ ListCell *l;
+
+ foreach(l, f->fromlist)
+ CreateIvmTriggersOnBaseTablesRecurse(qry, lfirst(l), matviewOid, relids, ex_lock);
+ }
+ break;
+
+ case T_JoinExpr:
+ {
+ JoinExpr *j = (JoinExpr *) node;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, j->larg, matviewOid, relids, ex_lock);
+ CreateIvmTriggersOnBaseTablesRecurse(qry, j->rarg, matviewOid, relids, ex_lock);
+ }
+ break;
+
+ default:
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
+ }
+}
+
+/*
+ * CreateIvmTrigger -- create IVM trigger on a base table
+ */
+static void
+CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock)
+{
+ ObjectAddress refaddr;
+ ObjectAddress address;
+ CreateTrigStmt *ivm_trigger;
+ List *transitionRels = NIL;
+
+ Assert(timing == TRIGGER_TYPE_BEFORE || timing == TRIGGER_TYPE_AFTER);
+
+ refaddr.classId = RelationRelationId;
+ refaddr.objectId = viewOid;
+ refaddr.objectSubId = 0;
+
+ ivm_trigger = makeNode(CreateTrigStmt);
+ ivm_trigger->relation = NULL;
+ ivm_trigger->row = false;
+
+ ivm_trigger->timing = timing;
+ ivm_trigger->events = type;
+
+ switch (type)
+ {
+ case TRIGGER_TYPE_INSERT:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_ins_before" : "IVM_trigger_ins_after");
+ break;
+ case TRIGGER_TYPE_DELETE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_del_before" : "IVM_trigger_del_after");
+ break;
+ case TRIGGER_TYPE_UPDATE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_upd_before" : "IVM_trigger_upd_after");
+ break;
+ default:
+ elog(ERROR, "unsupported trigger type");
+ }
+
+ if (timing == TRIGGER_TYPE_AFTER)
+ {
+ 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 =
+ (timing == TRIGGER_TYPE_BEFORE ? SystemFuncName("IVM_immediate_before") : 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_make2(
+ makeString(DatumGetPointer(DirectFunctionCall1(oidout, ObjectIdGetDatum(viewOid)))),
+ makeString(DatumGetPointer(DirectFunctionCall1(boolout, BoolGetDatum(ex_lock))))
+ );
+
+ 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();
+}
+
+/*
+ * check_ivm_restriction --- look for specify nodes in the query tree
+ */
+static void
+check_ivm_restriction(Node *node)
+{
+ check_ivm_restriction_walker(node, NULL);
+}
+
+static bool
+check_ivm_restriction_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ /*
+ * We currently don't support Sub-Query.
+ */
+ if (IsA(node, SubPlan) || IsA(node, SubLink))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("subquery is not supported on incrementally maintainable materialized view")));
+
+ /* This can recurse, so check for excessive recursion */
+ check_stack_depth();
+
+ switch (nodeTag(node))
+ {
+ case T_Query:
+ {
+ Query *qry = (Query *)node;
+ ListCell *lc;
+ List *vars;
+
+ /* if contained CTE, return error */
+ if (qry->cteList != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("CTE is not supported on incrementally maintainable materialized view")));
+ if (qry->havingQual != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg(" HAVING clause is not supported on incrementally maintainable materialized view")));
+ if (qry->sortClause != NIL) /* There is a possibility that we don't need to return an error */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ORDER BY clause is not supported on incrementally maintainable materialized view")));
+ if (qry->limitOffset != NULL || qry->limitCount != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view")));
+ if (qry->hasDistinctOn)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DISTINCT ON is not supported on incrementally maintainable materialized view")));
+ if (qry->hasWindowFuncs)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("window functions are not supported on incrementally maintainable materialized view")));
+ if (qry->groupingSets != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view")));
+ if (qry->setOperations != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view")));
+ if (list_length(qry->targetList) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("empty target list is not supported on incrementally maintainable materialized view")));
+ if (qry->rowMarks != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view")));
+
+ /* system column restrictions */
+ vars = pull_vars_of_level((Node *) qry, 0);
+ foreach(lc, vars)
+ {
+ if (IsA(lfirst(lc), Var))
+ {
+ Var *var = (Var *) lfirst(lc);
+ /* if system column, return error */
+ if (var->varattno < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("system column is not supported on incrementally maintainable materialized view")));
+ }
+ }
+
+ /* restrictions for rtable */
+ foreach(lc, qry->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ if (rte->subquery)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("subquery is not supported on incrementally maintainable materialized view")));
+
+ if (rte->tablesample != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("TABLESAMPLE clause is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("partitioned table is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_RELATION && has_superclass(rte->relid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("partitions is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_RELATION && find_inheritance_children(rte->relid, NoLock) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("inheritance parent is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign table is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_VIEW ||
+ rte->relkind == RELKIND_MATVIEW)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view")));
+
+ if (rte->rtekind == RTE_VALUES)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VALUES is not supported on incrementally maintainable materialized view")));
+
+ }
+
+ query_tree_walker(qry, check_ivm_restriction_walker, NULL, QTW_IGNORE_RANGE_TABLE);
+
+ break;
+ }
+ case T_TargetEntry:
+ {
+ TargetEntry *tle = (TargetEntry *)node;
+ if (isIvmName(tle->resname))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column name %s is not supported on incrementally maintainable materialized view", tle->resname)));
+
+ expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ break;
+ }
+ case T_JoinExpr:
+ {
+ JoinExpr *joinexpr = (JoinExpr *)node;
+
+ if (joinexpr->jointype > JOIN_INNER)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("OUTER JOIN is not supported on incrementally maintainable materialized view")));
+
+ expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ }
+ break;
+ case T_Aggref:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("aggregate function is not supported on incrementally maintainable materialized view")));
+ break;
+ default:
+ expression_tree_walker(node, check_ivm_restriction_walker, (void *) context);
+ break;
+ }
+ return false;
+}
+
+/*
+ * CreateIndexOnIMMV
+ *
+ * Create a unique index on incremental maintainable materialized view.
+ * If the view definition query has a GROUP BY clause, the index is created
+ * on the columns of GROUP BY expressions. Otherwise, if the view contains
+ * all primary key attritubes of its base tables in the target list, the index
+ * is created on these attritubes. In other cases, no index is created.
+ */
+void
+CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create)
+{
+ ListCell *lc;
+ IndexStmt *index;
+ ObjectAddress address;
+ List *constraintList = NIL;
+ char idxname[NAMEDATALEN];
+ List *indexoidlist = RelationGetIndexList(matviewRel);
+ ListCell *indexoidscan;
+
+ snprintf(idxname, sizeof(idxname), "%s_index", RelationGetRelationName(matviewRel));
+
+ index = makeNode(IndexStmt);
+
+ /*
+ * We consider null values not distinct to make sure that views with DISTINCT
+ * or GROUP BY don't contain multiple NULL rows when NULL is inserted to
+ * a base table concurrently.
+ */
+ index->nulls_not_distinct = true;
+
+ index->unique = true;
+ index->primary = false;
+ index->isconstraint = false;
+ index->deferrable = false;
+ index->initdeferred = false;
+ index->idxname = idxname;
+ index->relation =
+ makeRangeVar(get_namespace_name(RelationGetNamespace(matviewRel)),
+ pstrdup(RelationGetRelationName(matviewRel)),
+ -1);
+ index->accessMethod = DEFAULT_INDEX_TYPE;
+ index->options = NIL;
+ index->tableSpace = get_tablespace_name(matviewRel->rd_rel->reltablespace);
+ index->whereClause = NULL;
+ index->indexParams = NIL;
+ index->indexIncludingParams = NIL;
+ index->excludeOpNames = NIL;
+ index->idxcomment = NULL;
+ index->indexOid = InvalidOid;
+ index->oldNode = InvalidOid;
+ index->oldCreateSubid = InvalidSubTransactionId;
+ index->oldFirstRelfilenodeSubid = InvalidSubTransactionId;
+ index->transformed = true;
+ index->concurrent = false;
+ index->if_not_exists = false;
+
+ if (query->distinctClause)
+ {
+ /* create unique constraint on all columns */
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ else
+ {
+ Bitmapset *key_attnos;
+
+ /* create index on the base tables' primary key columns */
+ key_attnos = get_primary_key_attnos_from_query(query, &constraintList, is_create);
+ if (key_attnos)
+ {
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+
+ if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, key_attnos))
+ {
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ }
+ else
+ {
+ /* create no index, just notice that an appropriate index is necessary for efficient IVM */
+ ereport(NOTICE,
+ (errmsg("could not create an index on materialized view \"%s\" automatically",
+ RelationGetRelationName(matviewRel)),
+ errdetail("This target list does not have all the primary key columns, "
+ "or this view does not contain DISTINCT clause."),
+ errhint("Create an index on the materialized view for efficient incremental maintenance.")));
+ return;
+ }
+ }
+
+ /* If we have a compatible index, we don't need to create another. */
+ foreach(indexoidscan, indexoidlist)
+ {
+ Oid indexoid = lfirst_oid(indexoidscan);
+ Relation indexRel;
+ bool hasCompatibleIndex = false;
+
+ indexRel = index_open(indexoid, AccessShareLock);
+
+ if (CheckIndexCompatible(indexRel->rd_id,
+ index->accessMethod,
+ index->indexParams,
+ index->excludeOpNames))
+ hasCompatibleIndex = true;
+
+ index_close(indexRel, AccessShareLock);
+
+ if (hasCompatibleIndex)
+ return;
+ }
+
+ address = DefineIndex(RelationGetRelid(matviewRel),
+ index,
+ InvalidOid,
+ InvalidOid,
+ InvalidOid,
+ false, true, false, false, true);
+
+ ereport(NOTICE,
+ (errmsg("created index \"%s\" on materialized view \"%s\"",
+ idxname, RelationGetRelationName(matviewRel))));
+
+ /*
+ * Make dependencies so that the index is dropped if any base tables's
+ * primary key is dropped.
+ */
+ foreach(lc, constraintList)
+ {
+ Oid constraintOid = lfirst_oid(lc);
+ ObjectAddress refaddr;
+
+ refaddr.classId = ConstraintRelationId;
+ refaddr.objectId = constraintOid;
+ refaddr.objectSubId = 0;
+
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_NORMAL);
+ }
+}
+
+
+/*
+ * get_primary_key_attnos_from_query
+ *
+ * Identify the columns in base tables' primary keys in the target list.
+ *
+ * Returns a Bitmapset of the column attnos of the primary key's columns of
+ * tables that used in the query. The attnos are offset by
+ * FirstLowInvalidHeapAttributeNumber as same as get_primary_key_attnos.
+ *
+ * If any table has no primary key or any primary key's columns is not in
+ * the target list, return NULL. We also return NULL if any pkey constraint
+ * is deferrable.
+ *
+ * constraintList is set to a list of the OIDs of the pkey constraints.
+ */
+static Bitmapset *
+get_primary_key_attnos_from_query(Query *query, List **constraintList, bool is_create)
+{
+ List *key_attnos_list = NIL;
+ ListCell *lc;
+ int i;
+ Bitmapset *keys = NULL;
+ Relids rels_in_from;
+ PlannerInfo root;
+
+
+ /*
+ * Collect primary key attributes from all tables used in query. The key attributes
+ * sets for each table are stored in key_attnos_list in order by RTE index.
+ */
+ i = 1;
+ foreach(lc, query->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+ Bitmapset *key_attnos;
+ bool has_pkey = true;
+ Index first_rtindex = is_create ? 1 : PRS2_NEW_VARNO + 1;
+
+ /* skip NEW/OLD entries */
+ if (i >= first_rtindex)
+ {
+ /* for tables, call get_primary_key_attnos */
+ if (r->rtekind == RTE_RELATION)
+ {
+ Oid constraintOid;
+ key_attnos = get_primary_key_attnos(r->relid, false, &constraintOid);
+ *constraintList = lappend_oid(*constraintList, constraintOid);
+ has_pkey = (key_attnos != NULL);
+ }
+ /* for other RTEs, store NULL into key_attnos_list */
+ else
+ key_attnos = NULL;
+ }
+ else
+ key_attnos = NULL;
+
+ /*
+ * If any table or subquery has no primary key or its pkey constraint is deferrable,
+ * we cannot get key attributes for this query, so return NULL.
+ */
+ if (!has_pkey)
+ return NULL;
+
+ key_attnos_list = lappend(key_attnos_list, key_attnos);
+ i++;
+ }
+
+ /* Collect key attributes appearing in the target list */
+ i = 1;
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) flatten_join_alias_vars(query, lfirst(lc));
+
+ if (IsA(tle->expr, Var))
+ {
+ Var *var = (Var*) tle->expr;
+ Bitmapset *attnos = list_nth(key_attnos_list, var->varno - 1);
+
+ /* check if this attribute is from a base table's primary key */
+ if (bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber, attnos))
+ {
+ /*
+ * Remove found key attributes from key_attnos_list, and add this
+ * to the result list.
+ */
+ bms_del_member(attnos, var->varattno - FirstLowInvalidHeapAttributeNumber);
+ keys = bms_add_member(keys, i - FirstLowInvalidHeapAttributeNumber);
+ }
+ }
+ i++;
+ }
+
+ /* Collect relations appearing in the FROM clause */
+ rels_in_from = pull_varnos_of_level(&root, (Node *)query->jointree, 0);
+
+ /*
+ * Check if all key attributes of relations in FROM are appearing in the target
+ * list. If an attribute remains in key_attnos_list in spite of the table is used
+ * in FROM clause, the target is missing this key attribute, so we return NULL.
+ */
+ i = 1;
+ foreach(lc, key_attnos_list)
+ {
+ Bitmapset *bms = (Bitmapset *)lfirst(lc);
+ if (!bms_is_empty(bms) && bms_is_member(i, rels_in_from))
+ return NULL;
+ i++;
+ }
+
+ return keys;
+}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index cd30f15eba..a94b8ffd7d 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -36,6 +36,7 @@
#include "commands/dbcommands.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
+#include "commands/matview.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -1055,6 +1056,45 @@ DefineIndex(Oid relationId,
safe_index = indexInfo->ii_Expressions == NIL &&
indexInfo->ii_Predicate == NIL;
+ /*
+ * We disallow unique indexes on IVM columns of IMMVs.
+ */
+ if (RelationIsIVM(rel) && stmt->unique)
+ {
+ for (i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++)
+ {
+ AttrNumber attno = indexInfo->ii_IndexAttrNumbers[i];
+ if (attno > 0)
+ {
+ char *name = NameStr(TupleDescAttr(rel->rd_att, attno - 1)->attname);
+ if (name && isIvmName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unique index creation on IVM columns is not supported")));
+ }
+ }
+
+ if (indexInfo->ii_Expressions)
+ {
+ Bitmapset *indexattrs = NULL;
+ int varno = -1;
+
+ pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
+
+ while ((varno = bms_next_member(indexattrs, varno)) >= 0)
+ {
+ int attno = varno + FirstLowInvalidHeapAttributeNumber;
+ char *name = NameStr(TupleDescAttr(rel->rd_att, attno - 1)->attname);
+ if (name && isIvmName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unique index creation on IVM columns is not supported")));
+ }
+
+ }
+ }
+
+
/*
* Report index creation if appropriate (delay this till after most of the
* error checks)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 9ab248d25e..cb713328a0 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -25,26 +25,48 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_depend.h"
+#include "catalog/pg_trigger.h"
+#include "catalog/pg_type.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "commands/cluster.h"
+#include "commands/defrem.h"
#include "commands/matview.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/createas.h"
#include "executor/executor.h"
#include "executor/spi.h"
+#include "executor/tstoreReceiver.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/optimizer.h"
+#include "parser/analyze.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_collate.h"
+#include "parser/parse_func.h"
#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
+#include "rewrite/rowsecurity.h"
#include "storage/lmgr.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/typcache.h"
typedef struct
@@ -58,6 +80,50 @@ typedef struct
BulkInsertState bistate; /* bulk insert state */
} DR_transientrel;
+#define MV_INIT_QUERYHASHSIZE 16
+
+/*
+ * MV_TriggerHashEntry
+ *
+ * Hash entry for base tables on which IVM trigger is invoked
+ */
+typedef struct MV_TriggerHashEntry
+{
+ Oid matview_id; /* OID of the materialized view */
+ int before_trig_count; /* count of before triggers invoked */
+ int after_trig_count; /* count of after triggers invoked */
+
+ TransactionId xid; /* Transaction id before the first table is modified*/
+ CommandId cid; /* Command id before the first table is modified */
+
+ List *tables; /* List of MV_TriggerTable */
+ bool has_old; /* tuples are deleted from any table? */
+ bool has_new; /* tuples are inserted into any table? */
+} MV_TriggerHashEntry;
+
+/*
+ * MV_TriggerTable
+ *
+ * IVM related data for tables on which the trigger is invoked.
+ */
+typedef struct MV_TriggerTable
+{
+ Oid table_id; /* OID of the modified table */
+ List *old_tuplestores; /* tuplestores for deleted tuples */
+ List *new_tuplestores; /* tuplestores for inserted tuples */
+ List *old_rtes; /* RTEs of ENRs for old_tuplestores*/
+ List *new_rtes; /* RTEs of ENRs for new_tuplestores */
+
+ List *rte_indexes; /* List of RTE index of the modified table */
+ RangeTblEntry *original_rte; /* the original RTE saved before rewriting query */
+} MV_TriggerTable;
+
+static HTAB *mv_trigger_info = NULL;
+
+/* ENR name for materialized view delta */
+#define NEW_DELTA_ENRNAME "new_delta"
+#define OLD_DELTA_ENRNAME "old_delta"
+
static int matview_maintenance_depth = 0;
static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
@@ -65,7 +131,9 @@ 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,
- const char *queryString);
+ QueryEnvironment *queryEnv,
+ TupleDesc *resultTupleDesc,
+ 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,
int save_sec_context);
@@ -73,6 +141,45 @@ static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersist
static bool is_usable_unique_index(Relation indexRel);
static void OpenMatViewIncrementalMaintenance(void);
static void CloseMatViewIncrementalMaintenance(void);
+static Query *get_matview_query(Relation matviewRel);
+
+static Query *rewrite_query_for_preupdate_state(Query *query, List *tables,
+ TransactionId xid, CommandId cid,
+ ParseState *pstate);
+static void register_delta_ENRs(ParseState *pstate, Query *query, List *tables);
+static char *make_delta_enr_name(const char *prefix, Oid relid, int count);
+static RangeTblEntry *get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *table,
+ TransactionId xid, CommandId cid,
+ QueryEnvironment *queryEnv);
+static RangeTblEntry *union_ENRs(RangeTblEntry *rte, Oid relid, List *enr_rtes, const char *prefix,
+ QueryEnvironment *queryEnv);
+static Query *rewrite_query_for_distinct(Query *query, ParseState *pstate);
+
+static void calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
+ DestReceiver *dest_old, DestReceiver *dest_new,
+ TupleDesc *tupdesc_old, TupleDesc *tupdesc_new,
+ QueryEnvironment *queryEnv);
+static Query *rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte_index);
+
+static void apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
+ TupleDesc tupdesc_old, TupleDesc tupdesc_new,
+ Query *query, bool use_count, char *count_colname);
+static void apply_old_delta(const char *matviewname, const char *deltaname_old,
+ List *keys);
+static void apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
+ List *keys, const char *count_colname);
+static void apply_new_delta(const char *matviewname, const char *deltaname_new,
+ StringInfo target_list);
+static void apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
+ List *keys, StringInfo target_list, const char* count_colname);
+static char *get_matching_condition_string(List *keys);
+static void generate_equal(StringInfo querybuf, Oid opttype,
+ const char *leftop, const char *rightop);
+
+static void mv_InitHashTables(void);
+static void clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry);
+
+static List *get_securityQuals(Oid relId, int rt_index, Query *query);
/*
* SetMatViewPopulatedState
@@ -114,6 +221,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
*
@@ -140,9 +287,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
{
Oid matviewOid;
Relation matviewRel;
- RewriteRule *rule;
- List *actions;
Query *dataQuery;
+ Query *viewQuery;
Oid tableSpace;
Oid relowner;
Oid OIDNewHeap;
@@ -155,6 +301,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
int save_sec_context;
int save_nestlevel;
ObjectAddress address;
+ bool oldPopulated;
/* Determine strength of lock needed. */
concurrent = stmt->concurrent;
@@ -167,6 +314,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
lockmode, 0,
RangeVarCallbackOwnsTable, NULL);
matviewRel = table_open(matviewOid, NoLock);
+ oldPopulated = RelationIsPopulated(matviewRel);
/* Make sure it is a materialized view. */
if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW)
@@ -188,32 +336,14 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errmsg("%s and %s options cannot be used together",
"CONCURRENTLY", "WITH NO DATA")));
- /*
- * Check that everything is correct for a refresh. Problems at this point
- * are internal errors, so elog is sufficient.
- */
- if (matviewRel->rd_rel->relhasrules == false ||
- matviewRel->rd_rules->numLocks < 1)
- elog(ERROR,
- "materialized view \"%s\" is missing rewrite information",
- RelationGetRelationName(matviewRel));
-
- if (matviewRel->rd_rules->numLocks > 1)
- elog(ERROR,
- "materialized view \"%s\" has too many rules",
- RelationGetRelationName(matviewRel));
- rule = matviewRel->rd_rules->rules[0];
- if (rule->event != CMD_SELECT || !(rule->isInstead))
- elog(ERROR,
- "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
- RelationGetRelationName(matviewRel));
+ viewQuery = get_matview_query(matviewRel);
- actions = rule->actions;
- if (list_length(actions) != 1)
- elog(ERROR,
- "the rule for materialized view \"%s\" is not a single action",
- RelationGetRelationName(matviewRel));
+ /* For IMMV, we need to rewrite matview query */
+ if (!stmt->skipData && RelationIsIVM(matviewRel))
+ dataQuery = rewriteQueryForIMMV(viewQuery,NIL);
+ else
+ dataQuery = viewQuery;
/*
* Check that there is a unique index with no WHERE clause on one or more
@@ -248,12 +378,6 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errhint("Create a unique index with no WHERE clause on one or more columns of the materialized view.")));
}
- /*
- * The stored query was rewritten at the time of the MV definition, but
- * has not been scribbled on by the planner.
- */
- dataQuery = linitial_node(Query, actions);
-
/*
* Check for active uses of the relation in the current transaction, such
* as open scans.
@@ -294,6 +418,74 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
relpersistence = matviewRel->rd_rel->relpersistence;
}
+ /* delete IMMV triggers. */
+ if (RelationIsIVM(matviewRel) && stmt->skipData )
+ {
+ Relation tgRel;
+ Relation depRel;
+ ScanKeyData key;
+ SysScanDesc scan;
+ HeapTuple tup;
+ ObjectAddresses *immv_triggers;
+
+ immv_triggers = new_object_addresses();
+
+ tgRel = table_open(TriggerRelationId, RowExclusiveLock);
+ depRel = table_open(DependRelationId, RowExclusiveLock);
+
+ /* search triggers that depends on IMMV. */
+ ScanKeyInit(&key,
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(matviewOid));
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ NULL, 1, &key);
+ while ((tup = systable_getnext(scan)) != NULL)
+ {
+ ObjectAddress obj;
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(tup);
+
+ if (foundDep->classid == TriggerRelationId)
+ {
+ HeapTuple tgtup;
+ ScanKeyData tgkey[1];
+ SysScanDesc tgscan;
+ Form_pg_trigger tgform;
+
+ /* Find the trigger name. */
+ ScanKeyInit(&tgkey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(foundDep->objid));
+
+ tgscan = systable_beginscan(tgRel, TriggerOidIndexId, true,
+ NULL, 1, tgkey);
+ tgtup = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(tgtup))
+ elog(ERROR, "could not find tuple for immv trigger %u", foundDep->objid);
+
+ tgform = (Form_pg_trigger) GETSTRUCT(tgtup);
+
+ /* If trigger is created by IMMV, delete it. */
+ if (strncmp(NameStr(tgform->tgname), "IVM_trigger_", 12) == 0)
+ {
+ obj.classId = foundDep->classid;
+ obj.objectId = foundDep->objid;
+ obj.objectSubId = foundDep->refobjsubid;
+ add_exact_object_address(&obj, immv_triggers);
+ }
+ systable_endscan(tgscan);
+ }
+ }
+ systable_endscan(scan);
+
+ performMultipleDeletions(immv_triggers, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);
+
+ table_close(depRel, RowExclusiveLock);
+ table_close(tgRel, RowExclusiveLock);
+ free_object_addresses(immv_triggers);
+ }
+
/*
* Create the transient table that will receive the regenerated data. Lock
* it against access by any other process until commit (by which time it
@@ -313,7 +505,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, NULL, queryString);
/* Make the matview match the newly generated data. */
if (concurrent)
@@ -348,6 +540,12 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
pgstat_count_heap_insert(matviewRel, processed);
}
+ if (!stmt->skipData && RelationIsIVM(matviewRel) && !oldPopulated)
+ {
+ CreateIndexOnIMMV(viewQuery, matviewRel, false);
+ CreateIvmTriggersOnBaseTables(viewQuery, matviewOid, false);
+ }
+
table_close(matviewRel, NoLock);
/* Roll back any GUC changes */
@@ -382,6 +580,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
*/
static uint64
refresh_matview_datafill(DestReceiver *dest, Query *query,
+ QueryEnvironment *queryEnv,
+ TupleDesc *resultTupleDesc,
const char *queryString)
{
List *rewritten;
@@ -418,7 +618,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);
@@ -428,6 +628,9 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
processed = queryDesc->estate->es_processed;
+ if (resultTupleDesc)
+ *resultTupleDesc = CreateTupleDescCopy(queryDesc->tupDesc);
+
/* and clean up */
ExecutorFinish(queryDesc);
ExecutorEnd(queryDesc);
@@ -942,3 +1145,1307 @@ CloseMatViewIncrementalMaintenance(void)
matview_maintenance_depth--;
Assert(matview_maintenance_depth >= 0);
}
+
+/*
+ * get_matview_query - get the Query from a matview's _RETURN rule.
+ */
+static Query *
+get_matview_query(Relation matviewRel)
+{
+ RewriteRule *rule;
+ List * actions;
+
+ /*
+ * Check that everything is correct for a refresh. Problems at this point
+ * are internal errors, so elog is sufficient.
+ */
+ if (matviewRel->rd_rel->relhasrules == false ||
+ matviewRel->rd_rules->numLocks < 1)
+ elog(ERROR,
+ "materialized view \"%s\" is missing rewrite information",
+ RelationGetRelationName(matviewRel));
+
+ if (matviewRel->rd_rules->numLocks > 1)
+ elog(ERROR,
+ "materialized view \"%s\" has too many rules",
+ RelationGetRelationName(matviewRel));
+
+ rule = matviewRel->rd_rules->rules[0];
+ if (rule->event != CMD_SELECT || !(rule->isInstead))
+ elog(ERROR,
+ "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
+ RelationGetRelationName(matviewRel));
+
+ actions = rule->actions;
+ if (list_length(actions) != 1)
+ elog(ERROR,
+ "the rule for materialized view \"%s\" is not a single action",
+ RelationGetRelationName(matviewRel));
+
+ /*
+ * The stored query was rewritten at the time of the MV definition, but
+ * has not been scribbled on by the planner.
+ */
+ return linitial_node(Query, actions);
+}
+
+
+/* ----------------------------------------------------
+ * Incremental View Maintenance routines
+ * ---------------------------------------------------
+ */
+
+/*
+ * IVM_immediate_before
+ *
+ * IVM trigger function invoked before base table is modified. If this is
+ * invoked firstly in the same statement, we save the transaction id and the
+ * command id at that time.
+ */
+Datum
+IVM_immediate_before(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ char *matviewOid_text = trigdata->tg_trigger->tgargs[0];
+ char *ex_lock_text = trigdata->tg_trigger->tgargs[1];
+ Oid matviewOid;
+ MV_TriggerHashEntry *entry;
+ bool found;
+ bool ex_lock;
+
+ matviewOid = DatumGetObjectId(DirectFunctionCall1(oidin, CStringGetDatum(matviewOid_text)));
+ ex_lock = DatumGetBool(DirectFunctionCall1(boolin, CStringGetDatum(ex_lock_text)));
+
+ /* If the view has more than one tables, we have to use an exclusive lock. */
+ if (ex_lock)
+ {
+ /*
+ * Wait for concurrent transactions which update this materialized view at
+ * READ COMMITED. This is needed to see changes committed in other
+ * transactions. No wait and raise an error at REPEATABLE READ or
+ * SERIALIZABLE to prevent update anomalies of matviews.
+ * XXX: dead-lock is possible here.
+ */
+ if (!IsolationUsesXactSnapshot())
+ LockRelationOid(matviewOid, ExclusiveLock);
+ else if (!ConditionalLockRelationOid(matviewOid, ExclusiveLock))
+ {
+ /* try to throw error by name; relation could be deleted... */
+ char *relname = get_rel_name(matviewOid);
+
+ if (!relname)
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on materialized view during incremental maintenance")));
+
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on materialized view \"%s\" during incremental maintenance",
+ relname)));
+ }
+ }
+ else
+ LockRelationOid(matviewOid, RowExclusiveLock);
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_trigger_info)
+ mv_InitHashTables();
+
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_ENTER, &found);
+
+ /* On the first BEFORE to update the view, initialize trigger data */
+ if (!found)
+ {
+ Snapshot snapshot = GetActiveSnapshot();
+
+ entry->matview_id = matviewOid;
+ entry->before_trig_count = 0;
+ entry->after_trig_count = 0;
+ entry->xid = GetCurrentTransactionId();
+ entry->cid = snapshot->curcid;
+ entry->tables = NIL;
+ entry->has_old = false;
+ entry->has_new = false;
+ }
+
+ entry->before_trig_count++;
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * IVM_immediate_before
+ *
+ * IVM trigger function invoked after base table is modified.
+ * For each table, tuplestores of transition tables are collected.
+ * and after the last modification
+ */
+Datum
+IVM_immediate_maintenance(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Relation rel;
+ Oid relid;
+ Oid matviewOid;
+ Query *query;
+ Query *rewritten = NULL;
+ char *matviewOid_text = trigdata->tg_trigger->tgargs[0];
+ Relation matviewRel;
+ int old_depth = matview_maintenance_depth;
+
+ Oid relowner;
+ Tuplestorestate *old_tuplestore = NULL;
+ Tuplestorestate *new_tuplestore = NULL;
+ DestReceiver *dest_new = NULL, *dest_old = NULL;
+ Oid save_userid;
+ int save_sec_context;
+ int save_nestlevel;
+
+ MV_TriggerHashEntry *entry;
+ MV_TriggerTable *table;
+ bool found;
+
+ ParseState *pstate;
+ QueryEnvironment *queryEnv = create_queryEnv();
+ MemoryContext oldcxt;
+ ListCell *lc;
+ int i;
+
+
+ /* Create a ParseState for rewriting the view definition query */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ rel = trigdata->tg_relation;
+ relid = rel->rd_id;
+
+ matviewOid = DatumGetObjectId(DirectFunctionCall1(oidin, CStringGetDatum(matviewOid_text)));
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_trigger_info)
+ mv_InitHashTables();
+
+ /* get the entry for this materialized view */
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_FIND, &found);
+ Assert (found && entry != NULL);
+ entry->after_trig_count++;
+
+ /* search the entry for the modified table and create new entry if not found */
+ found = false;
+ foreach(lc, entry->tables)
+ {
+ table = (MV_TriggerTable *) lfirst(lc);
+ if (table->table_id == relid)
+ {
+ found = true;
+ break;
+ }
+ }
+ if (!found)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ table = (MV_TriggerTable *) palloc0(sizeof(MV_TriggerTable));
+ table->table_id = relid;
+ table->old_tuplestores = NIL;
+ table->new_tuplestores = NIL;
+ table->old_rtes = NIL;
+ table->new_rtes = NIL;
+ table->rte_indexes = NIL;
+ entry->tables = lappend(entry->tables, table);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /* Save the transition tables and make a request to not free immediately */
+ if (trigdata->tg_oldtable)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+ table->old_tuplestores = lappend(table->old_tuplestores, trigdata->tg_oldtable);
+ entry->has_old = true;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (trigdata->tg_newtable)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+ table->new_tuplestores = lappend(table->new_tuplestores, trigdata->tg_newtable);
+ entry->has_new = true;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (entry->has_new || entry->has_old)
+ {
+ CmdType cmd;
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ cmd = CMD_INSERT;
+ else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
+ cmd = CMD_DELETE;
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ cmd = CMD_UPDATE;
+ else
+ elog(ERROR,"unsupported trigger type");
+
+ /* Prolong lifespan of transition tables to the end of the last AFTER trigger */
+ SetTransitionTablePreserved(relid, cmd);
+ }
+
+
+ /* If this is not the last AFTER trigger call, immediately exit. */
+ Assert (entry->before_trig_count >= entry->after_trig_count);
+ if (entry->before_trig_count != entry->after_trig_count)
+ return PointerGetDatum(NULL);
+
+ /*
+ * If this is the last AFTER trigger call, continue and update the view.
+ */
+
+ /*
+ * Advance command counter to make the updated base table row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+
+ matviewRel = table_open(matviewOid, NoLock);
+
+ /* get view query*/
+ query = get_matview_query(matviewRel);
+
+ /* Make sure it is a materialized view. */
+ Assert(matviewRel->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Get and push the latast snapshot to see any changes which is committed
+ * during waiting in other transactions at READ COMMITTED level.
+ */
+ PushActiveSnapshot(GetTransactionSnapshot());
+
+ /*
+ * 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 a materialized view incrementally");
+
+ /*
+ * 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.
+ * We will switch modes when we are about to execute user code.
+ */
+ relowner = matviewRel->rd_rel->relowner;
+ GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ SetUserIdAndSecContext(relowner,
+ save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ save_nestlevel = NewGUCNestLevel();
+
+ /*
+ * rewrite query for calculating deltas
+ */
+
+ rewritten = copyObject(query);
+
+ /* Replace resnames in a target list with materialized view's attnames */
+ i = 0;
+ foreach (lc, rewritten->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ tle->resname = pstrdup(resname);
+ i++;
+ }
+
+ /* Set all tables in the query to pre-update state */
+ rewritten = rewrite_query_for_preupdate_state(rewritten, entry->tables,
+ entry->xid, entry->cid,
+ pstate);
+ /* Rewrite for DISTINCT clause */
+ rewritten = rewrite_query_for_distinct(rewritten, pstate);
+
+ /* Create tuplestores to store view deltas */
+ if (entry->has_old)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ dest_old = CreateDestReceiver(DestTuplestore);
+ SetTuplestoreDestReceiverParams(dest_old,
+ old_tuplestore,
+ TopTransactionContext,
+ false,
+ NULL,
+ NULL);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (entry->has_new)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ dest_new = CreateDestReceiver(DestTuplestore);
+ SetTuplestoreDestReceiverParams(dest_new,
+ new_tuplestore,
+ TopTransactionContext,
+ false,
+ NULL,
+ NULL);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /* for all modified tables */
+ foreach(lc, entry->tables)
+ {
+ ListCell *lc2;
+
+ table = (MV_TriggerTable *) lfirst(lc);
+
+ /* loop for self-join */
+ foreach(lc2, table->rte_indexes)
+ {
+ int rte_index = lfirst_int(lc2);
+ TupleDesc tupdesc_old;
+ TupleDesc tupdesc_new;
+ bool use_count = false;
+ char *count_colname = NULL;
+
+ count_colname = pstrdup("__ivm_count__");
+
+ if (query->distinctClause)
+ use_count = true;
+
+ /* calculate delta tables */
+ calc_delta(table, rte_index, rewritten, dest_old, dest_new,
+ &tupdesc_old, &tupdesc_new, queryEnv);
+
+ /* Set the table in the query to post-update state */
+ rewritten = rewrite_query_for_postupdate_state(rewritten, table, rte_index);
+
+ PG_TRY();
+ {
+ /* apply the delta tables to the materialized view */
+ apply_delta(matviewOid, old_tuplestore, new_tuplestore,
+ tupdesc_old, tupdesc_new, query, use_count,
+ count_colname);
+ }
+ PG_CATCH();
+ {
+ matview_maintenance_depth = old_depth;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ /* clear view delta tuplestores */
+ if (old_tuplestore)
+ tuplestore_clear(old_tuplestore);
+ if (new_tuplestore)
+ tuplestore_clear(new_tuplestore);
+ }
+ }
+
+ /* Clean up hash entry and delete tuplestores */
+ clean_up_IVM_hash_entry(entry);
+ if (old_tuplestore)
+ {
+ dest_old->rDestroy(dest_old);
+ tuplestore_end(old_tuplestore);
+ }
+ if (new_tuplestore)
+ {
+ dest_new->rDestroy(dest_new);
+ tuplestore_end(new_tuplestore);
+ }
+
+ /* Pop the original snapshot. */
+ PopActiveSnapshot();
+
+ table_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);
+}
+
+/*
+ * rewrite_query_for_preupdate_state
+ *
+ * Rewrite the query so that base tables' RTEs will represent "pre-update"
+ * state of tables. This is necessary to calculate view delta after multiple
+ * tables are modified. xid and cid are the transaction id and command id
+ * before the first table was modified.
+ */
+static Query*
+rewrite_query_for_preupdate_state(Query *query, List *tables,
+ TransactionId xid, CommandId cid,
+ ParseState *pstate)
+{
+ ListCell *lc;
+ int num_rte = list_length(query->rtable);
+ int i;
+
+
+ /* register delta ENRs */
+ register_delta_ENRs(pstate, query, tables);
+
+ /* XXX: Is necessary? Is this right timing? */
+ AcquireRewriteLocks(query, true, false);
+
+ i = 1;
+ foreach(lc, query->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+
+ ListCell *lc2;
+ foreach(lc2, tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc2);
+ /*
+ * if the modified table is found then replace the original RTE with
+ * "pre-state" RTE and append its index to the list.
+ */
+ if (r->relid == table->table_id)
+ {
+ lfirst(lc) = get_prestate_rte(r, table, xid, cid, pstate->p_queryEnv);
+ table->rte_indexes = lappend_int(table->rte_indexes, i);
+ break;
+ }
+ }
+
+ /* finish the loop if we processed all RTE included in the original query */
+ if (i++ >= num_rte)
+ break;
+ }
+
+ return query;
+}
+
+/*
+ * register_delta_ENRs
+ *
+ * For all modified tables, make ENRs for their transition tables
+ * and register them to the queryEnv. ENR's RTEs are also appended
+ * into the list in query tree.
+ */
+static void
+register_delta_ENRs(ParseState *pstate, Query *query, List *tables)
+{
+ QueryEnvironment *queryEnv = pstate->p_queryEnv;
+ ListCell *lc;
+ RangeTblEntry *rte;
+
+ foreach(lc, tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc);
+ ListCell *lc2;
+ int count;
+
+ count = 0;
+ foreach(lc2, table->old_tuplestores)
+ {
+ Tuplestorestate *oldtable = (Tuplestorestate *) lfirst(lc2);
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+ ParseNamespaceItem *nsitem;
+
+ enr->md.name = make_delta_enr_name("old", table->table_id, count);
+ enr->md.reliddesc = table->table_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(oldtable);
+ enr->reldata = oldtable;
+ register_ENR(queryEnv, enr);
+
+ nsitem = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ rte = nsitem->p_rte;
+ /* if base table has RLS, set security condition to enr */
+ rte->securityQuals = get_securityQuals(table->table_id, list_length(query->rtable) + 1, query);
+
+ query->rtable = lappend(query->rtable, rte);
+ table->old_rtes = lappend(table->old_rtes, rte);
+
+ count++;
+ }
+
+ count = 0;
+ foreach(lc2, table->new_tuplestores)
+ {
+ Tuplestorestate *newtable = (Tuplestorestate *) lfirst(lc2);
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+ ParseNamespaceItem *nsitem;
+
+ enr->md.name = make_delta_enr_name("new", table->table_id, count);
+ enr->md.reliddesc = table->table_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(newtable);
+ enr->reldata = newtable;
+ register_ENR(queryEnv, enr);
+
+ nsitem = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ rte = nsitem->p_rte;
+ /* if base table has RLS, set security condition to enr*/
+ rte->securityQuals = get_securityQuals(table->table_id, list_length(query->rtable) + 1, query);
+
+ query->rtable = lappend(query->rtable, rte);
+ table->new_rtes = lappend(table->new_rtes, rte);
+
+ count++;
+ }
+ }
+}
+
+/*
+ * get_prestate_rte
+ *
+ * Rewrite RTE of the modified table to a subquery which represents
+ * "pre-state" table. The original RTE is saved in table->rte_original.
+ */
+static RangeTblEntry*
+get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *table,
+ TransactionId xid, CommandId cid,
+ QueryEnvironment *queryEnv)
+{
+ StringInfoData str;
+ RawStmt *raw;
+ Query *sub;
+ Relation rel;
+ ParseState *pstate;
+ char *relname;
+ int i;
+
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ /*
+ * We can use NoLock here since AcquireRewriteLocks should
+ * have locked the rel already.
+ */
+ rel = table_open(table->table_id, NoLock);
+ relname = quote_qualified_identifier(
+ get_namespace_name(RelationGetNamespace(rel)),
+ RelationGetRelationName(rel));
+ table_close(rel, NoLock);
+
+ initStringInfo(&str);
+ appendStringInfo(&str,
+ "SELECT t.* FROM %s t"
+ " WHERE (age(t.xmin) - age(%u::text::xid) > 0) OR"
+ " (t.xmin = %u AND t.cmin::text::int < %u)",
+ relname, xid, xid, cid);
+
+ for (i = 0; i < list_length(table->old_tuplestores); i++)
+ {
+ appendStringInfo(&str, " UNION ALL ");
+ appendStringInfo(&str," SELECT * FROM %s",
+ make_delta_enr_name("old", table->table_id, i));
+ }
+
+ raw = (RawStmt*)linitial(raw_parser(str.data, RAW_PARSE_DEFAULT));
+ sub = transformStmt(pstate, raw->stmt);
+
+ /* If this query has setOperations, RTEs in rtables has a subquery which contains ENR */
+ if (sub->setOperations != NULL)
+ {
+ ListCell *lc;
+
+ /* add securityQuals for tuplestores */
+ foreach (lc, sub->rtable)
+ {
+ RangeTblEntry *rte;
+ RangeTblEntry *sub_rte;
+
+ rte = (RangeTblEntry *)lfirst(lc);
+ Assert(rte->subquery != NULL);
+
+ sub_rte = (RangeTblEntry *)linitial(rte->subquery->rtable);
+ if (sub_rte->rtekind == RTE_NAMEDTUPLESTORE)
+ /* rt_index is always 1, bacause subquery has enr_rte only */
+ sub_rte->securityQuals = get_securityQuals(sub_rte->relid, 1, sub);
+ }
+ }
+
+ /* save the original RTE */
+ table->original_rte = copyObject(rte);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = sub;
+ rte->security_barrier = false;
+ /* 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 rte;
+}
+
+/*
+ * make_delta_enr_name
+ *
+ * Make a name for ENR of a transition table from the base table's oid.
+ * prefix will be "new" or "old" depending on its transition table kind..
+ */
+static char*
+make_delta_enr_name(const char *prefix, Oid relid, int count)
+{
+ char buf[NAMEDATALEN];
+ char *name;
+
+ snprintf(buf, NAMEDATALEN, "__ivm_%s_%u_%u", prefix, relid, count);
+ name = pstrdup(buf);
+
+ return name;
+}
+
+/*
+ * union_ENRs
+ *
+ * Make a single table delta by unionning all transition tables of the modified table
+ * whose RTE is specified by
+ */
+static RangeTblEntry*
+union_ENRs(RangeTblEntry *rte, Oid relid, List *enr_rtes, const char *prefix,
+ QueryEnvironment *queryEnv)
+{
+ StringInfoData str;
+ ParseState *pstate;
+ RawStmt *raw;
+ Query *sub;
+ int i;
+ RangeTblEntry *enr_rte;
+
+ /* Create a ParseState for rewriting the view definition query */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ initStringInfo(&str);
+
+ for (i = 0; i < list_length(enr_rtes); i++)
+ {
+ if (i > 0)
+ appendStringInfo(&str, " UNION ALL ");
+
+ appendStringInfo(&str,
+ " SELECT * FROM %s",
+ make_delta_enr_name(prefix, relid, i));
+ }
+
+ raw = (RawStmt*)linitial(raw_parser(str.data, RAW_PARSE_DEFAULT));
+ sub = transformStmt(pstate, raw->stmt);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = sub;
+ rte->security_barrier = false;
+ /* 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;
+ /* if base table has RLS, set security condition to enr*/
+ enr_rte = (RangeTblEntry *)linitial(sub->rtable);
+ /* rt_index is always 1, bacause subquery has enr_rte only */
+ enr_rte->securityQuals = get_securityQuals(relid, 1, sub);
+
+ return rte;
+}
+
+/*
+ * rewrite_query_for_distinct
+ *
+ * Rewrite query for counting DISTINCT clause.
+ */
+static Query *
+rewrite_query_for_distinct(Query *query, ParseState *pstate)
+{
+ TargetEntry *tle_count;
+ FuncCall *fn;
+ Node *node;
+
+ /* Add count(*) for counting distinct tuples in views */
+ fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
+ fn->agg_star = true;
+ if (!query->groupClause && !query->hasAggs)
+ query->groupClause = transformDistinctClause(NULL, &query->targetList, query->sortClause, false);
+
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle_count = makeTargetEntry((Expr *) node,
+ list_length(query->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ query->targetList = lappend(query->targetList, tle_count);
+ query->hasAggs = true;
+
+ return query;
+}
+
+/*
+ * calc_delta
+ *
+ * Calculate view deltas generated under the modification of a table specified
+ * by the RTE index.
+ */
+static void
+calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
+ DestReceiver *dest_old, DestReceiver *dest_new,
+ TupleDesc *tupdesc_old, TupleDesc *tupdesc_new,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc = list_nth_cell(query->rtable, rte_index - 1);
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ /* Generate old delta */
+ if (list_length(table->old_rtes) > 0)
+ {
+ /* Replace the modified table with the old delta table and calculate the old view delta. */
+ lfirst(lc) = union_ENRs(rte, table->table_id, table->old_rtes, "old", queryEnv);
+ refresh_matview_datafill(dest_old, query, queryEnv, tupdesc_old, "");
+ }
+
+ /* Generate new delta */
+ if (list_length(table->new_rtes) > 0)
+ {
+ /* Replace the modified table with the new delta table and calculate the new view delta*/
+ lfirst(lc) = union_ENRs(rte, table->table_id, table->new_rtes, "new", queryEnv);
+ refresh_matview_datafill(dest_new, query, queryEnv, tupdesc_new, "");
+ }
+}
+
+/*
+ * rewrite_query_for_postupdate_state
+ *
+ * Rewrite the query so that the specified base table's RTEs will represent
+ * "post-update" state of tables. This is called after the view delta
+ * calculation due to changes on this table finishes.
+ */
+static Query*
+rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte_index)
+{
+ ListCell *lc = list_nth_cell(query->rtable, rte_index - 1);
+
+ /* Retore the original RTE */
+ lfirst(lc) = table->original_rte;
+
+ return query;
+}
+
+/*
+ * apply_delta
+ *
+ * Apply deltas to the materialized view. In outer join cases, this requires
+ * the view maintenance graph.
+ */
+static void
+apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
+ TupleDesc tupdesc_old, TupleDesc tupdesc_new,
+ Query *query, bool use_count, char *count_colname)
+{
+ StringInfoData querybuf;
+ StringInfoData target_list_buf;
+ Relation matviewRel;
+ char *matviewname;
+ ListCell *lc;
+ int i;
+ List *keys = NIL;
+
+
+ /*
+ * get names of the materialized view and delta tables
+ */
+
+ matviewRel = table_open(matviewOid, NoLock);
+ matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+
+ /*
+ * Build parts of the maintenance queries
+ */
+
+ initStringInfo(&querybuf);
+ initStringInfo(&target_list_buf);
+
+ /* build string of target list */
+ for (i = 0; i < matviewRel->rd_att->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ if (i != 0)
+ appendStringInfo(&target_list_buf, ", ");
+ appendStringInfo(&target_list_buf, "%s", quote_qualified_identifier(NULL, resname));
+ }
+
+ i = 0;
+ foreach (lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+
+ i++;
+
+ if (tle->resjunk)
+ continue;
+
+ keys = lappend(keys, attr);
+ }
+
+ /* Start maintaining the materialized view. */
+ OpenMatViewIncrementalMaintenance();
+
+ /* Open SPI context. */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /* For tuple deletion */
+ if (old_tuplestores && tuplestore_tuple_count(old_tuplestores) > 0)
+ {
+ EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ int rc;
+
+ /* convert tuplestores to ENR, and register for SPI */
+ enr->md.name = pstrdup(OLD_DELTA_ENRNAME);
+ enr->md.reliddesc = InvalidOid;
+ enr->md.tupdesc = tupdesc_old;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(old_tuplestores);
+ enr->reldata = old_tuplestores;
+
+ rc = SPI_register_relation(enr);
+ if (rc != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register failed");
+
+ if (use_count)
+ /* apply old delta and get rows to be recalculated */
+ apply_old_delta_with_count(matviewname, OLD_DELTA_ENRNAME,
+ keys, count_colname);
+ else
+ apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys);
+
+ }
+ /* For tuple insertion */
+ if (new_tuplestores && tuplestore_tuple_count(new_tuplestores) > 0)
+ {
+ EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ int rc;
+
+ /* convert tuplestores to ENR, and register for SPI */
+ enr->md.name = pstrdup(NEW_DELTA_ENRNAME);
+ enr->md.reliddesc = InvalidOid;
+ enr->md.tupdesc = tupdesc_new;;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(new_tuplestores);
+ enr->reldata = new_tuplestores;
+
+ rc = SPI_register_relation(enr);
+ if (rc != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register failed");
+
+ /* apply new delta */
+ if (use_count)
+ apply_new_delta_with_count(matviewname, NEW_DELTA_ENRNAME,
+ keys, &target_list_buf, count_colname);
+ else
+ apply_new_delta(matviewname, NEW_DELTA_ENRNAME, &target_list_buf);
+ }
+
+ /* We're done maintaining the materialized view. */
+ CloseMatViewIncrementalMaintenance();
+
+ table_close(matviewRel, NoLock);
+
+ /* Close SPI context. */
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+}
+
+/*
+ * apply_old_delta_with_count
+ *
+ * Execute a query for applying a delta table given by deltname_old
+ * which contains tuples to be deleted from to a materialized view given by
+ * matviewname. This is used when counting is required, that is, the view
+ * has aggregate or distinct.
+ */
+static void
+apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
+ List *keys, const char *count_colname)
+{
+ StringInfoData querybuf;
+ char *match_cond;
+
+ /* build WHERE condition for searching tuples to be deleted */
+ match_cond = get_matching_condition_string(keys);
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH t AS (" /* collecting tid of target tuples in the view */
+ "SELECT diff.%s, " /* count column */
+ "(diff.%s OPERATOR(pg_catalog.=) mv.%s) AS for_dlt, "
+ "mv.ctid "
+ "FROM %s AS mv, %s AS diff "
+ "WHERE %s" /* tuple matching condition */
+ "), updt AS (" /* update a tuple if this is not to be deleted */
+ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.-) t.%s "
+ "FROM t WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND NOT for_dlt "
+ ")"
+ /* delete a tuple if this is to be deleted */
+ "DELETE FROM %s AS mv USING t "
+ "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt",
+ count_colname,
+ count_colname, count_colname,
+ matviewname, deltaname_old,
+ match_cond,
+ matviewname, count_colname, count_colname, count_colname,
+ matviewname);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_old_delta
+ *
+ * Execute a query for applying a delta table given by deltname_old
+ * which contains tuples to be deleted from to a materialized view given by
+ * matviewname. This is used when counting is not required.
+ */
+static void
+apply_old_delta(const char *matviewname, const char *deltaname_old,
+ List *keys)
+{
+ StringInfoData querybuf;
+ StringInfoData keysbuf;
+ char *match_cond;
+ ListCell *lc;
+
+ /* build WHERE condition for searching tuples to be deleted */
+ match_cond = get_matching_condition_string(keys);
+
+ /* build string of keys list */
+ initStringInfo(&keysbuf);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&keysbuf, "%s", quote_qualified_identifier("mv", resname));
+ if (lnext(keys, lc))
+ appendStringInfo(&keysbuf, ", ");
+ }
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "DELETE FROM %s WHERE ctid IN ("
+ "SELECT tid FROM (SELECT row_number() over (partition by %s) AS \"__ivm_row_number__\","
+ "mv.ctid AS tid,"
+ "diff.\"__ivm_count__\""
+ "FROM %s AS mv, %s AS diff "
+ "WHERE %s) v "
+ "WHERE v.\"__ivm_row_number__\" OPERATOR(pg_catalog.<=) v.\"__ivm_count__\")",
+ matviewname,
+ keysbuf.data,
+ matviewname, deltaname_old,
+ match_cond);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_new_delta_with_count
+ *
+ * Execute a query for applying a delta table given by deltname_new
+ * which contains tuples to be inserted into a materialized view given by
+ * matviewname. This is used when counting is required, that is, the view
+ * has aggregate or distinct. Also, when a table in EXISTS sub queries
+ * is modified.
+ */
+static void
+apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
+ List *keys, StringInfo target_list, const char* count_colname)
+{
+ StringInfoData querybuf;
+ StringInfoData returning_keys;
+ ListCell *lc;
+ char *match_cond = "";
+
+ /* build WHERE condition for searching tuples to be updated */
+ match_cond = get_matching_condition_string(keys);
+
+ /* build string of keys list */
+ initStringInfo(&returning_keys);
+ if (keys)
+ {
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&returning_keys, "%s", quote_qualified_identifier("mv", resname));
+ if (lnext(keys, lc))
+ appendStringInfo(&returning_keys, ", ");
+ }
+ }
+ else
+ appendStringInfo(&returning_keys, "NULL");
+
+ /* Search for matching tuples from the view and update if found or insert if not. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH updt AS (" /* update a tuple if this exists in the view */
+ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.+) diff.%s "
+ "FROM %s AS diff "
+ "WHERE %s " /* tuple matching condition */
+ "RETURNING %s" /* returning keys of updated tuples */
+ ") INSERT INTO %s (%s)" /* insert a new tuple if this doesn't existw */
+ "SELECT %s FROM %s AS diff "
+ "WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE %s);",
+ matviewname, count_colname, count_colname, count_colname,
+ deltaname_new,
+ match_cond,
+ returning_keys.data,
+ matviewname, target_list->data,
+ target_list->data, deltaname_new,
+ match_cond);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_new_delta
+ *
+ * Execute a query for applying a delta table given by deltname_new
+ * which contains tuples to be inserted into a materialized view given by
+ * matviewname. This is used when counting is not required.
+ */
+static void
+apply_new_delta(const char *matviewname, const char *deltaname_new,
+ StringInfo target_list)
+{
+ StringInfoData querybuf;
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "INSERT INTO %s (%s) SELECT %s FROM ("
+ "SELECT diff.*, generate_series(1, diff.\"__ivm_count__\") "
+ "FROM %s AS diff) AS v",
+ matviewname, target_list->data, target_list->data,
+ deltaname_new);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * get_matching_condition_string
+ *
+ * Build a predicate string for looking for a tuple with given keys.
+ */
+static char *
+get_matching_condition_string(List *keys)
+{
+ StringInfoData match_cond;
+ ListCell *lc;
+
+ /* If there is no key columns, the condition is always true. */
+ if (keys == NIL)
+ return "true";
+
+ initStringInfo(&match_cond);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ char *mv_resname = quote_qualified_identifier("mv", resname);
+ char *diff_resname = quote_qualified_identifier("diff", resname);
+ Oid typid = attr->atttypid;
+
+ /* Considering NULL values, we can not use simple = operator. */
+ appendStringInfo(&match_cond, "(");
+ generate_equal(&match_cond, typid, mv_resname, diff_resname);
+ appendStringInfo(&match_cond, " OR (%s IS NULL AND %s IS NULL))",
+ mv_resname, diff_resname);
+
+ if (lnext(keys, lc))
+ appendStringInfo(&match_cond, " AND ");
+ }
+
+ return match_cond.data;
+}
+
+/*
+ * generate_equals
+ *
+ * Generate an equality clause using given operands' default equality
+ * operator.
+ */
+static void
+generate_equal(StringInfo querybuf, Oid opttype,
+ const char *leftop, const char *rightop)
+{
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(opttype, TYPECACHE_EQ_OPR);
+ if (!OidIsValid(typentry->eq_opr))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality operator for type %s",
+ format_type_be(opttype))));
+
+ generate_operator_clause(querybuf,
+ leftop, opttype,
+ typentry->eq_opr,
+ rightop, opttype);
+}
+
+/*
+ * mv_InitHashTables
+ */
+static void
+mv_InitHashTables(void)
+{
+ HASHCTL ctl;
+
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(MV_TriggerHashEntry);
+ mv_trigger_info = hash_create("MV trigger info",
+ MV_INIT_QUERYHASHSIZE,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+}
+
+/*
+ * AtAbort_IVM
+ *
+ * Clean up hash entries for all materialized views. This is called at
+ * transaction abort.
+ */
+void
+AtAbort_IVM()
+{
+ HASH_SEQ_STATUS seq;
+ MV_TriggerHashEntry *entry;
+
+ if (mv_trigger_info)
+ {
+ hash_seq_init(&seq, mv_trigger_info);
+ while ((entry = hash_seq_search(&seq)) != NULL)
+ clean_up_IVM_hash_entry(entry);
+ }
+}
+
+/*
+ * clean_up_IVM_hash_entry
+ *
+ * Clean up tuple stores and hash entries for a materialized view after its
+ * maintenance finished.
+ */
+static void
+clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry)
+{
+ bool found;
+ ListCell *lc;
+
+ foreach(lc, entry->tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc);
+
+ list_free(table->old_tuplestores);
+ list_free(table->new_tuplestores);
+ }
+ list_free(entry->tables);
+
+ hash_search(mv_trigger_info, (void *) &entry->matview_id, HASH_REMOVE, &found);
+}
+
+/*
+ * isIvmName
+ *
+ * Check if this is a IVM hidden column from the name.
+ */
+bool
+isIvmName(const char *s)
+{
+ if (s)
+ return (strncmp(s, "__ivm_", 6) == 0);
+ return false;
+}
+
+/*
+ * get_securityQuals
+ *
+ * Get row security policy on a relation.
+ * This is used by IVM for copying RLS from base table to enr.
+ */
+static List *
+get_securityQuals(Oid relId, int rt_index, Query *query)
+{
+ ParseState *pstate;
+ Relation rel;
+ ParseNamespaceItem *nsitem;
+ RangeTblEntry *rte;
+ List *securityQuals;
+ List *withCheckOptions;
+ bool hasRowSecurity;
+ bool hasSubLinks;
+
+ securityQuals = NIL;
+ pstate = make_parsestate(NULL);
+
+ rel = table_open(relId, NoLock);
+ nsitem = addRangeTableEntryForRelation(pstate, rel, AccessShareLock, NULL, false, false);
+ rte = nsitem->p_rte;
+
+ get_row_security_policies(query, rte, rt_index,
+ &securityQuals, &withCheckOptions,
+ &hasRowSecurity, &hasSubLinks);
+
+ /*
+ * Make sure the query is marked correctly if row level security
+ * applies, or if the new quals had sublinks.
+ */
+ if (hasRowSecurity)
+ query->hasRowSecurity = true;
+ if (hasSubLinks)
+ query->hasSubLinks = true;
+
+ table_close(rel, NoLock);
+
+ return securityQuals;
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2cd8546d47..1c85df888e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -52,6 +52,7 @@
#include "commands/cluster.h"
#include "commands/comment.h"
#include "commands/defrem.h"
+#include "commands/matview.h"
#include "commands/event_trigger.h"
#include "commands/policy.h"
#include "commands/sequence.h"
@@ -3441,6 +3442,14 @@ renameatt_internal(Oid myrelid,
targetrelation = relation_open(myrelid, AccessExclusiveLock);
renameatt_check(myrelid, RelationGetForm(targetrelation), recursing);
+ /*
+ * Don't rename IVM columns.
+ */
+ if (RelationIsIVM(targetrelation) && isIvmName(oldattname))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("IVM column can not be renamed")));
+
/*
* if the 'recurse' flag is set then we are supposed to rename this
* attribute in all classes that inherit from 'relname' (as well as in
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 0490bce664..4afc8f5826 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2949,6 +2949,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 55f41263ee..08d7805b7c 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -3131,6 +3131,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 cfd3ce68b4..e5a9467e99 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3436,6 +3436,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 5165fb3b93..50cc852c32 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1669,6 +1669,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/parse_relation.c b/src/backend/parser/parse_relation.c
index 5448cb01fa..b08c742678 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -36,6 +36,7 @@
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
+#include "commands/matview.h"
/*
@@ -79,7 +80,7 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int count, int offset,
int rtindex, int sublevels_up,
int location, bool include_dropped,
- List **colnames, List **colvars);
+ List **colnames, List **colvars, bool is_ivm);
static int specialAttNum(const char *attname);
static bool isQueryUsingTempRelation_walker(Node *node, void *context);
@@ -1444,6 +1445,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
@@ -1532,6 +1534,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
@@ -2688,7 +2691,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
expandTupleDesc(tupdesc, rte->eref,
rtfunc->funccolcount, atts_done,
rtindex, sublevels_up, location,
- include_dropped, colnames, colvars);
+ include_dropped, colnames, colvars, false);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
@@ -2956,7 +2959,7 @@ expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up,
expandTupleDesc(rel->rd_att, eref, rel->rd_att->natts, 0,
rtindex, sublevels_up,
location, include_dropped,
- colnames, colvars);
+ colnames, colvars, RelationIsIVM(rel));
relation_close(rel, AccessShareLock);
}
@@ -2973,7 +2976,7 @@ static void
expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
int rtindex, int sublevels_up,
int location, bool include_dropped,
- List **colnames, List **colvars)
+ List **colnames, List **colvars, bool is_ivm)
{
ListCell *aliascell;
int varattno;
@@ -2986,6 +2989,9 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
{
Form_pg_attribute attr = TupleDescAttr(tupdesc, varattno);
+ if (is_ivm && isIvmName(NameStr(attr->attname)) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
if (attr->attisdropped)
{
if (include_dropped)
@@ -3140,6 +3146,10 @@ expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem,
Var *varnode = (Var *) lfirst(var);
TargetEntry *te;
+ /* if transform * into columnlist with IMMV, remove IVM columns */
+ if (rte->relisivm && isIvmName(label) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
te = makeTargetEntry((Expr *) varnode,
(AttrNumber) pstate->p_next_resno++,
label,
diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c
index 185bf5fbff..d8a8b66196 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -776,7 +776,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/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6d378ff785..7d88a84682 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11882,4 +11882,12 @@
prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
prosrc => 'brin_minmax_multi_summary_send' },
+# IVM
+{ oid => '786', descr => 'ivm trigger (before)',
+ proname => 'IVM_immediate_before', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_before' },
+{ oid => '787', descr => 'ivm trigger (after)',
+ proname => 'IVM_immediate_maintenance', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_maintenance' },
+
]
diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h
index 54a38491fb..c369b3ba5e 100644
--- a/src/include/commands/createas.h
+++ b/src/include/commands/createas.h
@@ -16,6 +16,7 @@
#include "catalog/objectaddress.h"
#include "nodes/params.h"
+#include "nodes/pathnodes.h"
#include "parser/parse_node.h"
#include "tcop/dest.h"
#include "utils/queryenvironment.h"
@@ -25,6 +26,11 @@ extern ObjectAddress ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *st
ParamListInfo params, QueryEnvironment *queryEnv,
QueryCompletion *qc);
+extern void CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid, bool is_create);
+extern void CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create);
+
+extern Query *rewriteQueryForIMMV(Query *query, List *colNames);
+
extern int GetIntoRelEFlags(IntoClause *intoClause);
extern DestReceiver *CreateIntoRelDestReceiver(IntoClause *intoClause);
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index a067da39d2..ec479db513 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -15,6 +15,7 @@
#define MATVIEW_H
#include "catalog/objectaddress.h"
+#include "fmgr.h"
#include "nodes/params.h"
#include "nodes/parsenodes.h"
#include "tcop/dest.h"
@@ -23,6 +24,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, QueryCompletion *qc);
@@ -30,4 +33,9 @@ extern DestReceiver *CreateTransientRelDestReceiver(Oid oid);
extern bool MatViewIncrementalMaintenanceIsEnabled(void);
+extern Datum IVM_immediate_before(PG_FUNCTION_ARGS);
+extern Datum IVM_immediate_maintenance(PG_FUNCTION_ARGS);
+extern void AtAbort_IVM(void);
+extern bool isIvmName(const char *s);
+
#endif /* MATVIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index da02658c81..d1a754936b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1042,6 +1042,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):
@@ -2545,6 +2546,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;
/* ----------
--
2.17.1
[text/x-diff] v27-0007-Add-aggregates-support-in-IVM.patch (55.7K, ../[email protected]/8-v27-0007-Add-aggregates-support-in-IVM.patch)
download | inline diff:
From 3d0a4063f0eae1c6dd5938d0eb982ae7f0682f14 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Mon, 2 Aug 2021 14:59:27 +0900
Subject: [PATCH v27 7/9] Add aggregates support in IVM
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Currently, count, sum, avg, min and max are supported.
As a restriction, expressions specified in GROUP BY must appear in
the target list because tuples to be updated in IMMV are identified
by using this group key. However, in the case of aggregates without
GROUP BY, there is only one tuple in the view, so keys are not uses
to identify tuples.
When creating a IMMV, in addition to __ivm_count column, some hidden
columns for each aggregate are added to the target list. For example,
names of these hidden columns are ivm_count_avg and ivm_sum_avg for
the average function, and so on.
In the case of views without aggregate functions, only the number of
tuple multiplicities in __ivm_count__ column are updated at incremental
maintenance. On the other hand, in the case of view with aggregates,
the aggregated values and related hidden columns are also updated. The
way of update depends the kind of aggregate function.　Specifically,
sum and count are updated by simply adding or subtracting delta value
calculated from delta tables. avg is updated by using values of sum
and count stored in views as hidden columns and deltas calculated
from delta tables.
In min or max cases, it becomes more complicated. For an example of min,
when tuples are inserted, the smaller value between the current min value
in the view and the value calculated from the new delta table is used.
When tuples are deleted, if the current min value in the view equals to
the min in the old delta table, we need re-computation the latest min
value from base tables. Otherwise, the current value in the view remains.
As to sum, avg, min, and max (any aggregate functions except count),
NULL in input values is ignored, and this returns a null value when no
rows are selected. To support this specification, the number of not-NULL
input values is counted and stored in views as a hidden column. In the
case of count(), count(x) returns zero when no rows are selected, and
count(*) doesn't ignore NULL input. These specification are also supported.
---
src/backend/commands/createas.c | 299 ++++++++-
src/backend/commands/matview.c | 1023 ++++++++++++++++++++++++++++++-
src/include/commands/createas.h | 1 +
3 files changed, 1281 insertions(+), 42 deletions(-)
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 1224a3b075..d2ae50d5ee 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -63,6 +63,7 @@
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/regproc.h"
#include "utils/rel.h"
#include "utils/rls.h"
#include "utils/snapmgr.h"
@@ -80,6 +81,11 @@ typedef struct
BulkInsertState bistate; /* bulk insert state */
} DR_intorel;
+typedef struct
+{
+ bool has_agg;
+} check_ivm_restriction_context;
+
/* utility functions for CTAS definition creation */
static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into);
static ObjectAddress create_ctas_nodata(List *tlist, IntoClause *into);
@@ -94,9 +100,9 @@ static void CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid mat
Relids *relids, bool ex_lock);
static void CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock);
static void check_ivm_restriction(Node *node);
-static bool check_ivm_restriction_walker(Node *node, void *context);
-static Bitmapset *get_primary_key_attnos_from_query(Query *qry, List **constraintList, bool is_create);
+static bool check_ivm_restriction_walker(Node *node, check_ivm_restriction_context *context);
static Bitmapset *get_primary_key_attnos_from_query(Query *query, List **constraintList, bool is_create);
+static bool check_aggregate_supports_ivm(Oid aggfnoid);
/*
* create_ctas_internal
@@ -429,6 +435,7 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
* rewriteQueryForIMMV -- rewrite view definition query for IMMV
*
* count(*) is added for counting distinct tuples in views.
+ * Also, additional hidden columns are added for aggregate values.
*/
Query *
rewriteQueryForIMMV(Query *query, List *colNames)
@@ -443,14 +450,46 @@ rewriteQueryForIMMV(Query *query, List *colNames)
rewritten = copyObject(query);
pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
- /*
- * Convert DISTINCT to GROUP BY and add count(*) for counting distinct
- * tuples in views.
- */
- if (rewritten->distinctClause)
+ /* group keys must be in targetlist */
+ if (rewritten->groupClause)
{
+ ListCell *lc;
+ foreach(lc, rewritten->groupClause)
+ {
+ SortGroupClause *scl = (SortGroupClause *) lfirst(lc);
+ TargetEntry *tle = get_sortgroupclause_tle(scl, rewritten->targetList);
+
+ if (tle->resjunk)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("GROUP BY expression not appearing in select list is not supported on incrementally maintainable materialized view")));
+ }
+ }
+ /* Convert DISTINCT to GROUP BY. count(*) will be added afterward. */
+ else if (!rewritten->hasAggs && rewritten->distinctClause)
rewritten->groupClause = transformDistinctClause(NULL, &rewritten->targetList, rewritten->sortClause, false);
+ /* Add additional columns for aggregate values */
+ if (rewritten->hasAggs)
+ {
+ ListCell *lc;
+ List *aggs = NIL;
+ AttrNumber next_resno = list_length(rewritten->targetList) + 1;
+
+ foreach(lc, rewritten->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ char *resname = (colNames == NIL ? tle->resname : strVal(list_nth(colNames, tle->resno - 1)));
+
+ if (IsA(tle->expr, Aggref))
+ makeIvmAggColumn(pstate, (Aggref *)tle->expr, resname, &next_resno, &aggs);
+ }
+ rewritten->targetList = list_concat(rewritten->targetList, aggs);
+ }
+
+ /* Add count(*) for counting distinct tuples in views */
+ if (rewritten->distinctClause || rewritten->hasAggs)
+ {
fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
fn->agg_star = true;
@@ -467,6 +506,91 @@ rewriteQueryForIMMV(Query *query, List *colNames)
return rewritten;
}
+/*
+ * makeIvmAggColumn -- make additional aggregate columns for IVM
+ *
+ * For an aggregate column specified by aggref, additional aggregate columns
+ * are added, which are used to calculate the new aggregate value in IMMV.
+ * An additional aggregate columns has a name based on resname
+ * (ex. ivm_count_resname), and resno specified by next_resno. The created
+ * columns are returned to aggs, and the resno for the next column is also
+ * returned to next_resno.
+ *
+ * Currently, an additional count() is created for aggref other than count.
+ * In addition, sum() is created for avg aggregate column.
+ */
+void
+makeIvmAggColumn(ParseState *pstate, Aggref *aggref, char *resname, AttrNumber *next_resno, List **aggs)
+{
+ TargetEntry *tle_count;
+ Node *node;
+ FuncCall *fn;
+ Const *dmy_arg = makeConst(INT4OID,
+ -1,
+ InvalidOid,
+ sizeof(int32),
+ Int32GetDatum(1),
+ false,
+ true); /* pass by value */
+ const char *aggname = get_func_name(aggref->aggfnoid);
+
+ /*
+ * For aggregate functions except count, add count() func with the same arg parameters.
+ * This count result is used for determining if the aggregate value should be NULL or not.
+ * Also, add sum() func for avg because we need to calculate an average value as sum/count.
+ *
+ * XXX: If there are same expressions explicitly in the target list, we can use this instead
+ * of adding new duplicated one.
+ */
+ if (strcmp(aggname, "count") != 0)
+ {
+ fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
+
+ /* Make a Func with a dummy arg, and then override this by the original agg's args. */
+ node = ParseFuncOrColumn(pstate, fn->funcname, list_make1(dmy_arg), NULL, fn, false, -1);
+ ((Aggref *)node)->args = aggref->args;
+
+ tle_count = makeTargetEntry((Expr *) node,
+ *next_resno,
+ pstrdup(makeObjectName("__ivm_count",resname, "_")),
+ false);
+ *aggs = lappend(*aggs, tle_count);
+ (*next_resno)++;
+ }
+ if (strcmp(aggname, "avg") == 0)
+ {
+ List *dmy_args = NIL;
+ ListCell *lc;
+ foreach(lc, aggref->aggargtypes)
+ {
+ Oid typeid = lfirst_oid(lc);
+ Type type = typeidType(typeid);
+
+ Const *con = makeConst(typeid,
+ -1,
+ typeTypeCollation(type),
+ typeLen(type),
+ (Datum) 0,
+ true,
+ typeByVal(type));
+ dmy_args = lappend(dmy_args, con);
+ ReleaseSysCache(type);
+ }
+ fn = makeFuncCall(list_make1(makeString("sum")), NIL, COERCE_EXPLICIT_CALL, -1);
+
+ /* Make a Func with dummy args, and then override this by the original agg's args. */
+ node = ParseFuncOrColumn(pstate, fn->funcname, dmy_args, NULL, fn, false, -1);
+ ((Aggref *)node)->args = aggref->args;
+
+ tle_count = makeTargetEntry((Expr *) node,
+ *next_resno,
+ pstrdup(makeObjectName("__ivm_sum",resname, "_")),
+ false);
+ *aggs = lappend(*aggs, tle_count);
+ (*next_resno)++;
+ }
+}
+
/*
* GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
*
@@ -920,11 +1044,13 @@ CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock
static void
check_ivm_restriction(Node *node)
{
- check_ivm_restriction_walker(node, NULL);
+ check_ivm_restriction_context context = {false};
+
+ check_ivm_restriction_walker(node, &context);
}
static bool
-check_ivm_restriction_walker(Node *node, void *context)
+check_ivm_restriction_walker(Node *node, check_ivm_restriction_context *context)
{
if (node == NULL)
return false;
@@ -1005,6 +1131,8 @@ check_ivm_restriction_walker(Node *node, void *context)
}
}
+ context->has_agg |= qry->hasAggs;
+
/* restrictions for rtable */
foreach(lc, qry->rtable)
{
@@ -1053,7 +1181,7 @@ check_ivm_restriction_walker(Node *node, void *context)
}
- query_tree_walker(qry, check_ivm_restriction_walker, NULL, QTW_IGNORE_RANGE_TABLE);
+ query_tree_walker(qry, check_ivm_restriction_walker, (void *) context, QTW_IGNORE_RANGE_TABLE);
break;
}
@@ -1064,8 +1192,12 @@ check_ivm_restriction_walker(Node *node, void *context)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("column name %s is not supported on incrementally maintainable materialized view", tle->resname)));
+ if (context->has_agg && !IsA(tle->expr, Aggref) && contain_aggs_of_level((Node *) tle->expr, 0))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("expression containing an aggregate in it is not supported on incrementally maintainable materialized view")));
- expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ expression_tree_walker(node, check_ivm_restriction_walker, (void *) context);
break;
}
case T_JoinExpr:
@@ -1077,14 +1209,36 @@ check_ivm_restriction_walker(Node *node, void *context)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("OUTER JOIN is not supported on incrementally maintainable materialized view")));
- expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ expression_tree_walker(node, check_ivm_restriction_walker, (void *) context);
+ break;
}
- break;
case T_Aggref:
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("aggregate function is not supported on incrementally maintainable materialized view")));
- break;
+ {
+ /* Check if this supports IVM */
+ Aggref *aggref = (Aggref *) node;
+ const char *aggname = format_procedure(aggref->aggfnoid);
+
+ if (aggref->aggfilter != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("aggregate function with FILTER clause is not supported on incrementally maintainable materialized view")));
+
+ if (aggref->aggdistinct != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view")));
+
+ if (aggref->aggorder != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("aggregate function with ORDER clause is not supported on incrementally maintainable materialized view")));
+
+ if (!check_aggregate_supports_ivm(aggref->aggfnoid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("aggregate function %s is not supported on incrementally maintainable materialized view", aggname)));
+ break;
+ }
default:
expression_tree_walker(node, check_ivm_restriction_walker, (void *) context);
break;
@@ -1092,6 +1246,91 @@ check_ivm_restriction_walker(Node *node, void *context)
return false;
}
+/*
+ * check_aggregate_supports_ivm
+ *
+ * Check if the given aggregate function is supporting IVM
+ */
+static bool
+check_aggregate_supports_ivm(Oid aggfnoid)
+{
+ switch (aggfnoid)
+ {
+ /* count */
+ case F_COUNT_ANY:
+ case F_COUNT_:
+
+ /* sum */
+ case F_SUM_INT8:
+ case F_SUM_INT4:
+ case F_SUM_INT2:
+ case F_SUM_FLOAT4:
+ case F_SUM_FLOAT8:
+ case F_SUM_MONEY:
+ case F_SUM_INTERVAL:
+ case F_SUM_NUMERIC:
+
+ /* avg */
+ case F_AVG_INT8:
+ case F_AVG_INT4:
+ case F_AVG_INT2:
+ case F_AVG_NUMERIC:
+ case F_AVG_FLOAT4:
+ case F_AVG_FLOAT8:
+ case F_AVG_INTERVAL:
+
+ /* min */
+ case F_MIN_ANYARRAY:
+ case F_MIN_INT8:
+ case F_MIN_INT4:
+ case F_MIN_INT2:
+ case F_MIN_OID:
+ case F_MIN_FLOAT4:
+ case F_MIN_FLOAT8:
+ case F_MIN_DATE:
+ case F_MIN_TIME:
+ case F_MIN_TIMETZ:
+ case F_MIN_MONEY:
+ case F_MIN_TIMESTAMP:
+ case F_MIN_TIMESTAMPTZ:
+ case F_MIN_INTERVAL:
+ case F_MIN_TEXT:
+ case F_MIN_NUMERIC:
+ case F_MIN_BPCHAR:
+ case F_MIN_TID:
+ case F_MIN_ANYENUM:
+ case F_MIN_INET:
+ case F_MIN_PG_LSN:
+
+ /* max */
+ case F_MAX_ANYARRAY:
+ case F_MAX_INT8:
+ case F_MAX_INT4:
+ case F_MAX_INT2:
+ case F_MAX_OID:
+ case F_MAX_FLOAT4:
+ case F_MAX_FLOAT8:
+ case F_MAX_DATE:
+ case F_MAX_TIME:
+ case F_MAX_TIMETZ:
+ case F_MAX_MONEY:
+ case F_MAX_TIMESTAMP:
+ case F_MAX_TIMESTAMPTZ:
+ case F_MAX_INTERVAL:
+ case F_MAX_TEXT:
+ case F_MAX_NUMERIC:
+ case F_MAX_BPCHAR:
+ case F_MAX_TID:
+ case F_MAX_ANYENUM:
+ case F_MAX_INET:
+ case F_MAX_PG_LSN:
+ return true;
+
+ default:
+ return false;
+ }
+}
+
/*
* CreateIndexOnIMMV
*
@@ -1149,7 +1388,29 @@ CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create)
index->concurrent = false;
index->if_not_exists = false;
- if (query->distinctClause)
+ if (query->groupClause)
+ {
+ /* create unique constraint on GROUP BY expression columns */
+ foreach(lc, query->groupClause)
+ {
+ SortGroupClause *scl = (SortGroupClause *) lfirst(lc);
+ TargetEntry *tle = get_sortgroupclause_tle(scl, query->targetList);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ else if (query->distinctClause)
{
/* create unique constraint on all columns */
foreach(lc, query->targetList)
@@ -1207,7 +1468,7 @@ CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create)
(errmsg("could not create an index on materialized view \"%s\" automatically",
RelationGetRelationName(matviewRel)),
errdetail("This target list does not have all the primary key columns, "
- "or this view does not contain DISTINCT clause."),
+ "or this view does not contain GROUP BY or DISTINCT clause."),
errhint("Create an index on the materialized view for efficient incremental maintenance.")));
return;
}
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index cb713328a0..721d91c009 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -82,6 +82,32 @@ typedef struct
#define MV_INIT_QUERYHASHSIZE 16
+/* MV query type codes */
+#define MV_PLAN_RECALC 1
+#define MV_PLAN_SET_VALUE 2
+
+/*
+ * MI_QueryKey
+ *
+ * The key identifying a prepared SPI plan in our query hashtable
+ */
+typedef struct MV_QueryKey
+{
+ Oid matview_id; /* OID of materialized view */
+ int32 query_type; /* query type ID, see MV_PLAN_XXX above */
+} MV_QueryKey;
+
+/*
+ * MV_QueryHashEntry
+ *
+ * Hash entry for cached plans used to maintain materialized views.
+ */
+typedef struct MV_QueryHashEntry
+{
+ MV_QueryKey key;
+ SPIPlanPtr plan;
+} MV_QueryHashEntry;
+
/*
* MV_TriggerHashEntry
*
@@ -118,8 +144,16 @@ typedef struct MV_TriggerTable
RangeTblEntry *original_rte; /* the original RTE saved before rewriting query */
} MV_TriggerTable;
+static HTAB *mv_query_cache = NULL;
static HTAB *mv_trigger_info = NULL;
+/* kind of IVM operation for the view */
+typedef enum
+{
+ IVM_ADD,
+ IVM_SUB
+} IvmOp;
+
/* ENR name for materialized view delta */
#define NEW_DELTA_ENRNAME "new_delta"
#define OLD_DELTA_ENRNAME "old_delta"
@@ -153,7 +187,7 @@ static RangeTblEntry *get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *tabl
QueryEnvironment *queryEnv);
static RangeTblEntry *union_ENRs(RangeTblEntry *rte, Oid relid, List *enr_rtes, const char *prefix,
QueryEnvironment *queryEnv);
-static Query *rewrite_query_for_distinct(Query *query, ParseState *pstate);
+static Query *rewrite_query_for_distinct_and_aggregates(Query *query, ParseState *pstate);
static void calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
DestReceiver *dest_old, DestReceiver *dest_new,
@@ -164,19 +198,48 @@ static Query *rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *
static void apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
TupleDesc tupdesc_old, TupleDesc tupdesc_new,
Query *query, bool use_count, char *count_colname);
+static void append_set_clause_for_count(const char *resname, StringInfo buf_old,
+ StringInfo buf_new,StringInfo aggs_list);
+static void append_set_clause_for_sum(const char *resname, StringInfo buf_old,
+ StringInfo buf_new, StringInfo aggs_list);
+static void append_set_clause_for_avg(const char *resname, StringInfo buf_old,
+ StringInfo buf_new, StringInfo aggs_list,
+ const char *aggtype);
+static void append_set_clause_for_minmax(const char *resname, StringInfo buf_old,
+ StringInfo buf_new, StringInfo aggs_list,
+ bool is_min);
+static char *get_operation_string(IvmOp op, const char *col, const char *arg1, const char *arg2,
+ const char* count_col, const char *castType);
+static char *get_null_condition_string(IvmOp op, const char *arg1, const char *arg2,
+ const char* count_col);
static void apply_old_delta(const char *matviewname, const char *deltaname_old,
List *keys);
static void apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
- List *keys, const char *count_colname);
+ List *keys, StringInfo aggs_list, StringInfo aggs_set,
+ List *minmax_list, List *is_min_list,
+ const char *count_colname,
+ SPITupleTable **tuptable_recalc, uint64 *num_recalc);
static void apply_new_delta(const char *matviewname, const char *deltaname_new,
StringInfo target_list);
static void apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
- List *keys, StringInfo target_list, const char* count_colname);
+ List *keys, StringInfo target_list, StringInfo aggs_set,
+ const char* count_colname);
static char *get_matching_condition_string(List *keys);
+static char *get_returning_string(List *minmax_list, List *is_min_list, List *keys);
+static char *get_minmax_recalc_condition_string(List *minmax_list, List *is_min_list);
+static char *get_select_for_recalc_string(List *keys);
+static void recalc_and_set_values(SPITupleTable *tuptable_recalc, int64 num_tuples,
+ List *namelist, List *keys, Relation matviewRel);
+static SPIPlanPtr get_plan_for_recalc(Oid matviewOid, List *namelist, List *keys, Oid *keyTypes);
+static SPIPlanPtr get_plan_for_set_values(Oid matviewOid, char *matviewname, List *namelist,
+ Oid *valTypes);
static void generate_equal(StringInfo querybuf, Oid opttype,
const char *leftop, const char *rightop);
static void mv_InitHashTables(void);
+static SPIPlanPtr mv_FetchPreparedPlan(MV_QueryKey *key);
+static void mv_HashPreparedPlan(MV_QueryKey *key, SPIPlanPtr plan);
+static void mv_BuildQueryKey(MV_QueryKey *key, Oid matview_id, int32 query_type);
static void clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry);
static List *get_securityQuals(Oid relId, int rt_index, Query *query);
@@ -1470,8 +1533,8 @@ IVM_immediate_maintenance(PG_FUNCTION_ARGS)
rewritten = rewrite_query_for_preupdate_state(rewritten, entry->tables,
entry->xid, entry->cid,
pstate);
- /* Rewrite for DISTINCT clause */
- rewritten = rewrite_query_for_distinct(rewritten, pstate);
+ /* Rewrite for DISTINCT clause and aggregates functions */
+ rewritten = rewrite_query_for_distinct_and_aggregates(rewritten, pstate);
/* Create tuplestores to store view deltas */
if (entry->has_old)
@@ -1522,7 +1585,7 @@ IVM_immediate_maintenance(PG_FUNCTION_ARGS)
count_colname = pstrdup("__ivm_count__");
- if (query->distinctClause)
+ if (query->hasAggs || query->distinctClause)
use_count = true;
/* calculate delta tables */
@@ -1884,17 +1947,34 @@ union_ENRs(RangeTblEntry *rte, Oid relid, List *enr_rtes, const char *prefix,
}
/*
- * rewrite_query_for_distinct
+ * rewrite_query_for_distinct_and_aggregates
*
- * Rewrite query for counting DISTINCT clause.
+ * Rewrite query for counting DISTINCT clause and aggregate functions.
*/
static Query *
-rewrite_query_for_distinct(Query *query, ParseState *pstate)
+rewrite_query_for_distinct_and_aggregates(Query *query, ParseState *pstate)
{
TargetEntry *tle_count;
FuncCall *fn;
Node *node;
+ /* For aggregate views */
+ if (query->hasAggs)
+ {
+ ListCell *lc;
+ List *aggs = NIL;
+ AttrNumber next_resno = list_length(query->targetList) + 1;
+
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+
+ if (IsA(tle->expr, Aggref))
+ makeIvmAggColumn(pstate, (Aggref *)tle->expr, tle->resname, &next_resno, &aggs);
+ }
+ query->targetList = list_concat(query->targetList, aggs);
+ }
+
/* Add count(*) for counting distinct tuples in views */
fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
fn->agg_star = true;
@@ -1963,6 +2043,8 @@ rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte
return query;
}
+#define IVM_colname(type, col) makeObjectName("__ivm_" type, col, "_")
+
/*
* apply_delta
*
@@ -1976,11 +2058,16 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
{
StringInfoData querybuf;
StringInfoData target_list_buf;
+ StringInfo aggs_list_buf = NULL;
+ StringInfo aggs_set_old = NULL;
+ StringInfo aggs_set_new = NULL;
Relation matviewRel;
char *matviewname;
ListCell *lc;
int i;
List *keys = NIL;
+ List *minmax_list = NIL;
+ List *is_min_list = NIL;
/*
@@ -1998,6 +2085,15 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
initStringInfo(&querybuf);
initStringInfo(&target_list_buf);
+ if (query->hasAggs)
+ {
+ if (old_tuplestores && tuplestore_tuple_count(old_tuplestores) > 0)
+ aggs_set_old = makeStringInfo();
+ if (new_tuplestores && tuplestore_tuple_count(new_tuplestores) > 0)
+ aggs_set_new = makeStringInfo();
+ aggs_list_buf = makeStringInfo();
+ }
+
/* build string of target list */
for (i = 0; i < matviewRel->rd_att->natts; i++)
{
@@ -2014,13 +2110,72 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
{
TargetEntry *tle = (TargetEntry *) lfirst(lc);
Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
i++;
if (tle->resjunk)
continue;
- keys = lappend(keys, attr);
+ /*
+ * For views without aggregates, all attributes are used as keys to identify a
+ * tuple in a view.
+ */
+ if (!query->hasAggs)
+ keys = lappend(keys, attr);
+
+ /* For views with aggregates, we need to build SET clause for updating aggregate
+ * values. */
+ if (query->hasAggs && IsA(tle->expr, Aggref))
+ {
+ Aggref *aggref = (Aggref *) tle->expr;
+ const char *aggname = get_func_name(aggref->aggfnoid);
+
+ /*
+ * We can use function names here because it is already checked if these
+ * can be used in IMMV by its OID at the definition time.
+ */
+
+ /* count */
+ if (!strcmp(aggname, "count"))
+ append_set_clause_for_count(resname, aggs_set_old, aggs_set_new, aggs_list_buf);
+
+ /* sum */
+ else if (!strcmp(aggname, "sum"))
+ append_set_clause_for_sum(resname, aggs_set_old, aggs_set_new, aggs_list_buf);
+
+ /* avg */
+ else if (!strcmp(aggname, "avg"))
+ append_set_clause_for_avg(resname, aggs_set_old, aggs_set_new, aggs_list_buf,
+ format_type_be(aggref->aggtype));
+
+ /* min/max */
+ else if (!strcmp(aggname, "min") || !strcmp(aggname, "max"))
+ {
+ bool is_min = (!strcmp(aggname, "min"));
+
+ append_set_clause_for_minmax(resname, aggs_set_old, aggs_set_new, aggs_list_buf, is_min);
+
+ /* make a resname list of min and max aggregates */
+ minmax_list = lappend(minmax_list, resname);
+ is_min_list = lappend_int(is_min_list, is_min);
+ }
+ else
+ elog(ERROR, "unsupported aggregate function: %s", aggname);
+ }
+ }
+
+ /* If we have GROUP BY clause, we use its entries as keys. */
+ if (query->hasAggs && query->groupClause)
+ {
+ foreach (lc, query->groupClause)
+ {
+ SortGroupClause *sgcl = (SortGroupClause *) lfirst(lc);
+ TargetEntry *tle = get_sortgroupclause_tle(sgcl, query->targetList);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+
+ keys = lappend(keys, attr);
+ }
}
/* Start maintaining the materialized view. */
@@ -2034,6 +2189,8 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
if (old_tuplestores && tuplestore_tuple_count(old_tuplestores) > 0)
{
EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ SPITupleTable *tuptable_recalc = NULL;
+ uint64 num_recalc;
int rc;
/* convert tuplestores to ENR, and register for SPI */
@@ -2051,10 +2208,19 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
if (use_count)
/* apply old delta and get rows to be recalculated */
apply_old_delta_with_count(matviewname, OLD_DELTA_ENRNAME,
- keys, count_colname);
+ keys, aggs_list_buf, aggs_set_old,
+ minmax_list, is_min_list,
+ count_colname, &tuptable_recalc, &num_recalc);
else
apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys);
+ /*
+ * If we have min or max, we might have to recalculate aggregate values from base tables
+ * on some tuples. TIDs and keys such tuples are returned as a result of the above query.
+ */
+ if (minmax_list && tuptable_recalc)
+ recalc_and_set_values(tuptable_recalc, num_recalc, minmax_list, keys, matviewRel);
+
}
/* For tuple insertion */
if (new_tuplestores && tuplestore_tuple_count(new_tuplestores) > 0)
@@ -2077,7 +2243,7 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
/* apply new delta */
if (use_count)
apply_new_delta_with_count(matviewname, NEW_DELTA_ENRNAME,
- keys, &target_list_buf, count_colname);
+ keys, aggs_set_new, &target_list_buf, count_colname);
else
apply_new_delta(matviewname, NEW_DELTA_ENRNAME, &target_list_buf);
}
@@ -2092,49 +2258,410 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
elog(ERROR, "SPI_finish failed");
}
+/*
+ * append_set_clause_for_count
+ *
+ * Append SET clause string for count aggregation to given buffers.
+ * Also, append resnames required for calculating the aggregate value.
+ */
+static void
+append_set_clause_for_count(const char *resname, StringInfo buf_old,
+ StringInfo buf_new,StringInfo aggs_list)
+{
+ /* For tuple deletion */
+ if (buf_old)
+ {
+ /* resname = mv.resname - t.resname */
+ appendStringInfo(buf_old,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, resname),
+ get_operation_string(IVM_SUB, resname, "mv", "t", NULL, NULL));
+ }
+ /* For tuple insertion */
+ if (buf_new)
+ {
+ /* resname = mv.resname + diff.resname */
+ appendStringInfo(buf_new,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, resname),
+ get_operation_string(IVM_ADD, resname, "mv", "diff", NULL, NULL));
+ }
+
+ appendStringInfo(aggs_list, ", %s",
+ quote_qualified_identifier("diff", resname)
+ );
+}
+
+/*
+ * append_set_clause_for_sum
+ *
+ * Append SET clause string for sum aggregation to given buffers.
+ * Also, append resnames required for calculating the aggregate value.
+ */
+static void
+append_set_clause_for_sum(const char *resname, StringInfo buf_old,
+ StringInfo buf_new, StringInfo aggs_list)
+{
+ char *count_col = IVM_colname("count", resname);
+
+ /* For tuple deletion */
+ if (buf_old)
+ {
+ /* sum = mv.sum - t.sum */
+ appendStringInfo(buf_old,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, resname),
+ get_operation_string(IVM_SUB, resname, "mv", "t", count_col, NULL)
+ );
+ /* count = mv.count - t.count */
+ appendStringInfo(buf_old,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, count_col),
+ get_operation_string(IVM_SUB, count_col, "mv", "t", NULL, NULL)
+ );
+ }
+ /* For tuple insertion */
+ if (buf_new)
+ {
+ /* sum = mv.sum + diff.sum */
+ appendStringInfo(buf_new,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, resname),
+ get_operation_string(IVM_ADD, resname, "mv", "diff", count_col, NULL)
+ );
+ /* count = mv.count + diff.count */
+ appendStringInfo(buf_new,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, count_col),
+ get_operation_string(IVM_ADD, count_col, "mv", "diff", NULL, NULL)
+ );
+ }
+
+ appendStringInfo(aggs_list, ", %s, %s",
+ quote_qualified_identifier("diff", resname),
+ quote_qualified_identifier("diff", IVM_colname("count", resname))
+ );
+}
+
+/*
+ * append_set_clause_for_avg
+ *
+ * Append SET clause string for avg aggregation to given buffers.
+ * Also, append resnames required for calculating the aggregate value.
+ */
+static void
+append_set_clause_for_avg(const char *resname, StringInfo buf_old,
+ StringInfo buf_new, StringInfo aggs_list,
+ const char *aggtype)
+{
+ char *sum_col = IVM_colname("sum", resname);
+ char *count_col = IVM_colname("count", resname);
+
+ /* For tuple deletion */
+ if (buf_old)
+ {
+ /* avg = (mv.sum - t.sum)::aggtype / (mv.count - t.count) */
+ appendStringInfo(buf_old,
+ ", %s = %s OPERATOR(pg_catalog./) %s",
+ quote_qualified_identifier(NULL, resname),
+ get_operation_string(IVM_SUB, sum_col, "mv", "t", count_col, aggtype),
+ get_operation_string(IVM_SUB, count_col, "mv", "t", NULL, NULL)
+ );
+ /* sum = mv.sum - t.sum */
+ appendStringInfo(buf_old,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, sum_col),
+ get_operation_string(IVM_SUB, sum_col, "mv", "t", count_col, NULL)
+ );
+ /* count = mv.count - t.count */
+ appendStringInfo(buf_old,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, count_col),
+ get_operation_string(IVM_SUB, count_col, "mv", "t", NULL, NULL)
+ );
+
+ }
+ /* For tuple insertion */
+ if (buf_new)
+ {
+ /* avg = (mv.sum + diff.sum)::aggtype / (mv.count + diff.count) */
+ appendStringInfo(buf_new,
+ ", %s = %s OPERATOR(pg_catalog./) %s",
+ quote_qualified_identifier(NULL, resname),
+ get_operation_string(IVM_ADD, sum_col, "mv", "diff", count_col, aggtype),
+ get_operation_string(IVM_ADD, count_col, "mv", "diff", NULL, NULL)
+ );
+ /* sum = mv.sum + diff.sum */
+ appendStringInfo(buf_new,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, sum_col),
+ get_operation_string(IVM_ADD, sum_col, "mv", "diff", count_col, NULL)
+ );
+ /* count = mv.count + diff.count */
+ appendStringInfo(buf_new,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, count_col),
+ get_operation_string(IVM_ADD, count_col, "mv", "diff", NULL, NULL)
+ );
+ }
+
+ appendStringInfo(aggs_list, ", %s, %s, %s",
+ quote_qualified_identifier("diff", resname),
+ quote_qualified_identifier("diff", IVM_colname("sum", resname)),
+ quote_qualified_identifier("diff", IVM_colname("count", resname))
+ );
+}
+
+/*
+ * append_set_clause_for_minmax
+ *
+ * Append SET clause string for min or max aggregation to given buffers.
+ * Also, append resnames required for calculating the aggregate value.
+ * is_min is true if this is min, false if not.
+ */
+static void
+append_set_clause_for_minmax(const char *resname, StringInfo buf_old,
+ StringInfo buf_new, StringInfo aggs_list,
+ bool is_min)
+{
+ char *count_col = IVM_colname("count", resname);
+
+ /* For tuple deletion */
+ if (buf_old)
+ {
+ /*
+ * If the new value doesn't became NULL then use the value remaining
+ * in the view although this will be recomputated afterwords.
+ */
+ appendStringInfo(buf_old,
+ ", %s = CASE WHEN %s THEN NULL ELSE %s END",
+ quote_qualified_identifier(NULL, resname),
+ get_null_condition_string(IVM_SUB, "mv", "t", count_col),
+ quote_qualified_identifier("mv", resname)
+ );
+ /* count = mv.count - t.count */
+ appendStringInfo(buf_old,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, count_col),
+ get_operation_string(IVM_SUB, count_col, "mv", "t", NULL, NULL)
+ );
+ }
+ /* For tuple insertion */
+ if (buf_new)
+ {
+ /*
+ * min = LEAST(mv.min, diff.min)
+ * max = GREATEST(mv.max, diff.max)
+ */
+ appendStringInfo(buf_new,
+ ", %s = CASE WHEN %s THEN NULL ELSE %s(%s,%s) END",
+ quote_qualified_identifier(NULL, resname),
+ get_null_condition_string(IVM_ADD, "mv", "diff", count_col),
+
+ is_min ? "LEAST" : "GREATEST",
+ quote_qualified_identifier("mv", resname),
+ quote_qualified_identifier("diff", resname)
+ );
+ /* count = mv.count + diff.count */
+ appendStringInfo(buf_new,
+ ", %s = %s",
+ quote_qualified_identifier(NULL, count_col),
+ get_operation_string(IVM_ADD, count_col, "mv", "diff", NULL, NULL)
+ );
+ }
+
+ appendStringInfo(aggs_list, ", %s, %s",
+ quote_qualified_identifier("diff", resname),
+ quote_qualified_identifier("diff", IVM_colname("count", resname))
+ );
+}
+
+/*
+ * get_operation_string
+ *
+ * Build a string to calculate the new aggregate values.
+ */
+static char *
+get_operation_string(IvmOp op, const char *col, const char *arg1, const char *arg2,
+ const char* count_col, const char *castType)
+{
+ StringInfoData buf;
+ StringInfoData castString;
+ char *col1 = quote_qualified_identifier(arg1, col);
+ char *col2 = quote_qualified_identifier(arg2, col);
+ char op_char = (op == IVM_SUB ? '-' : '+');
+
+ initStringInfo(&buf);
+ initStringInfo(&castString);
+
+ if (castType)
+ appendStringInfo(&castString, "::%s", castType);
+
+ if (!count_col)
+ {
+ /*
+ * If the attributes don't have count columns then calc the result
+ * by using the operator simply.
+ */
+ appendStringInfo(&buf, "(%s OPERATOR(pg_catalog.%c) %s)%s",
+ col1, op_char, col2, castString.data);
+ }
+ else
+ {
+ /*
+ * If the attributes have count columns then consider the condition
+ * where the result becomes NULL.
+ */
+ char *null_cond = get_null_condition_string(op, arg1, arg2, count_col);
+
+ appendStringInfo(&buf,
+ "(CASE WHEN %s THEN NULL "
+ "WHEN %s IS NULL THEN %s "
+ "WHEN %s IS NULL THEN %s "
+ "ELSE (%s OPERATOR(pg_catalog.%c) %s)%s END)",
+ null_cond,
+ col1, col2,
+ col2, col1,
+ col1, op_char, col2, castString.data
+ );
+ }
+
+ return buf.data;
+}
+
+/*
+ * get_null_condition_string
+ *
+ * Build a predicate string for CASE clause to check if an aggregate value
+ * will became NULL after the given operation is applied.
+ */
+static char *
+get_null_condition_string(IvmOp op, const char *arg1, const char *arg2,
+ const char* count_col)
+{
+ StringInfoData null_cond;
+ initStringInfo(&null_cond);
+
+ switch (op)
+ {
+ case IVM_ADD:
+ appendStringInfo(&null_cond,
+ "%s OPERATOR(pg_catalog.=) 0 AND %s OPERATOR(pg_catalog.=) 0",
+ quote_qualified_identifier(arg1, count_col),
+ quote_qualified_identifier(arg2, count_col)
+ );
+ break;
+ case IVM_SUB:
+ appendStringInfo(&null_cond,
+ "%s OPERATOR(pg_catalog.=) %s",
+ quote_qualified_identifier(arg1, count_col),
+ quote_qualified_identifier(arg2, count_col)
+ );
+ break;
+ default:
+ elog(ERROR,"unknown operation");
+ }
+
+ return null_cond.data;
+}
+
+
/*
* apply_old_delta_with_count
*
* Execute a query for applying a delta table given by deltname_old
* which contains tuples to be deleted from to a materialized view given by
* matviewname. This is used when counting is required, that is, the view
- * has aggregate or distinct.
+ * has aggregate or distinct. Also, when a table in EXISTS sub queries
+ * is modified.
+ *
+ * If the view desn't have aggregates or has GROUP BY, this requires a keys
+ * list to identify a tuple in the view. If the view has aggregates, this
+ * requires strings representing resnames of aggregates and SET clause for
+ * updating aggregate values.
+ *
+ * If the view has min or max aggregate, this requires a list of resnames of
+ * min/max aggregates and a list of boolean which represents which entries in
+ * minmax_list is min. These are necessary to check if we need to recalculate
+ * min or max aggregate values. In this case, this query returns TID and keys
+ * of tuples which need to be recalculated. This result and the number of rows
+ * are stored in tuptables and num_recalc repectedly.
+ *
*/
static void
apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
- List *keys, const char *count_colname)
+ List *keys, StringInfo aggs_list, StringInfo aggs_set,
+ List *minmax_list, List *is_min_list,
+ const char *count_colname,
+ SPITupleTable **tuptable_recalc, uint64 *num_recalc)
{
StringInfoData querybuf;
char *match_cond;
+ char *updt_returning = "";
+ char *select_for_recalc = "SELECT";
+ bool agg_without_groupby = (list_length(keys) == 0);
+
+ Assert(tuptable_recalc != NULL);
+ Assert(num_recalc != NULL);
/* build WHERE condition for searching tuples to be deleted */
match_cond = get_matching_condition_string(keys);
+ /*
+ * We need a special RETURNING clause and SELECT statement for min/max to
+ * check which tuple needs re-calculation from base tables.
+ */
+ if (minmax_list)
+ {
+ updt_returning = get_returning_string(minmax_list, is_min_list, keys);
+ select_for_recalc = get_select_for_recalc_string(keys);
+ }
+
/* Search for matching tuples from the view and update or delete if found. */
initStringInfo(&querybuf);
appendStringInfo(&querybuf,
"WITH t AS (" /* collecting tid of target tuples in the view */
"SELECT diff.%s, " /* count column */
- "(diff.%s OPERATOR(pg_catalog.=) mv.%s) AS for_dlt, "
+ "(diff.%s OPERATOR(pg_catalog.=) mv.%s AND %s) AS for_dlt, "
"mv.ctid "
+ "%s " /* aggregate columns */
"FROM %s AS mv, %s AS diff "
"WHERE %s" /* tuple matching condition */
"), updt AS (" /* update a tuple if this is not to be deleted */
"UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.-) t.%s "
+ "%s" /* SET clauses for aggregates */
"FROM t WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND NOT for_dlt "
- ")"
- /* delete a tuple if this is to be deleted */
- "DELETE FROM %s AS mv USING t "
- "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt",
+ "%s" /* RETURNING clause for recalc infomation */
+ "), dlt AS (" /* delete a tuple if this is to be deleted */
+ "DELETE FROM %s AS mv USING t "
+ "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt"
+ ") %s", /* SELECT returning which tuples need to be recalculated */
count_colname,
- count_colname, count_colname,
+ count_colname, count_colname, (agg_without_groupby ? "false" : "true"),
+ (aggs_list != NULL ? aggs_list->data : ""),
matviewname, deltaname_old,
match_cond,
matviewname, count_colname, count_colname, count_colname,
- matviewname);
+ (aggs_set != NULL ? aggs_set->data : ""),
+ updt_returning,
+ matviewname,
+ select_for_recalc);
- if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_SELECT)
elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+
+ /* Return tuples to be recalculated. */
+ if (minmax_list)
+ {
+ *tuptable_recalc = SPI_tuptable;
+ *num_recalc = SPI_processed;
+ }
+ else
+ {
+ *tuptable_recalc = NULL;
+ *num_recalc = 0;
+ }
}
/*
@@ -2194,10 +2721,15 @@ apply_old_delta(const char *matviewname, const char *deltaname_old,
* matviewname. This is used when counting is required, that is, the view
* has aggregate or distinct. Also, when a table in EXISTS sub queries
* is modified.
+ *
+ * If the view desn't have aggregates or has GROUP BY, this requires a keys
+ * list to identify a tuple in the view. If the view has aggregates, this
+ * requires strings representing SET clause for updating aggregate values.
*/
static void
apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
- List *keys, StringInfo target_list, const char* count_colname)
+ List *keys, StringInfo aggs_set, StringInfo target_list,
+ const char* count_colname)
{
StringInfoData querybuf;
StringInfoData returning_keys;
@@ -2228,6 +2760,7 @@ apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
appendStringInfo(&querybuf,
"WITH updt AS (" /* update a tuple if this exists in the view */
"UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.+) diff.%s "
+ "%s " /* SET clauses for aggregates */
"FROM %s AS diff "
"WHERE %s " /* tuple matching condition */
"RETURNING %s" /* returning keys of updated tuples */
@@ -2235,6 +2768,7 @@ apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
"SELECT %s FROM %s AS diff "
"WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE %s);",
matviewname, count_colname, count_colname, count_colname,
+ (aggs_set != NULL ? aggs_set->data : ""),
deltaname_new,
match_cond,
returning_keys.data,
@@ -2309,6 +2843,349 @@ get_matching_condition_string(List *keys)
return match_cond.data;
}
+/*
+ * get_returning_string
+ *
+ * Build a string for RETURNING clause of UPDATE used in apply_old_delta_with_count.
+ * This clause returns ctid and a boolean value that indicates if we need to
+ * recalculate min or max value, for each updated row.
+ */
+static char *
+get_returning_string(List *minmax_list, List *is_min_list, List *keys)
+{
+ StringInfoData returning;
+ char *recalc_cond;
+ ListCell *lc;
+
+ Assert(minmax_list != NIL && is_min_list != NIL);
+ recalc_cond = get_minmax_recalc_condition_string(minmax_list, is_min_list);
+
+ initStringInfo(&returning);
+
+ appendStringInfo(&returning, "RETURNING mv.ctid AS tid, (%s) AS recalc", recalc_cond);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&returning, ", %s", quote_qualified_identifier("mv", resname));
+ }
+
+ return returning.data;
+}
+
+/*
+ * get_minmax_recalc_condition_string
+ *
+ * Build a predicate string for checking if any min/max aggregate
+ * value needs to be recalculated.
+ */
+static char *
+get_minmax_recalc_condition_string(List *minmax_list, List *is_min_list)
+{
+ StringInfoData recalc_cond;
+ ListCell *lc1, *lc2;
+
+ initStringInfo(&recalc_cond);
+
+ Assert (list_length(minmax_list) == list_length(is_min_list));
+
+ forboth (lc1, minmax_list, lc2, is_min_list)
+ {
+ char *resname = (char *) lfirst(lc1);
+ bool is_min = (bool) lfirst_int(lc2);
+ char *op_str = (is_min ? ">=" : "<=");
+
+ appendStringInfo(&recalc_cond, "%s OPERATOR(pg_catalog.%s) %s",
+ quote_qualified_identifier("mv", resname),
+ op_str,
+ quote_qualified_identifier("t", resname)
+ );
+
+ if (lnext(minmax_list, lc1))
+ appendStringInfo(&recalc_cond, " OR ");
+ }
+
+ return recalc_cond.data;
+}
+
+/*
+ * get_select_for_recalc_string
+ *
+ * Build a query to return tid and keys of tuples which need
+ * recalculation. This is used as the result of the query
+ * built by apply_old_delta.
+ */
+static char *
+get_select_for_recalc_string(List *keys)
+{
+ StringInfoData qry;
+ ListCell *lc;
+
+ initStringInfo(&qry);
+
+ appendStringInfo(&qry, "SELECT tid");
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ appendStringInfo(&qry, ", %s", NameStr(attr->attname));
+ }
+
+ appendStringInfo(&qry, " FROM updt WHERE recalc");
+
+ return qry.data;
+}
+
+/*
+ * recalc_and_set_values
+ *
+ * Recalculate tuples in a materialized from base tables and update these.
+ * The tuples which needs recalculation are specified by keys, and resnames
+ * of columns to be updated are specified by namelist. TIDs and key values
+ * are given by tuples in tuptable_recalc. Its first attribute must be TID
+ * and key values must be following this.
+ */
+static void
+recalc_and_set_values(SPITupleTable *tuptable_recalc, int64 num_tuples,
+ List *namelist, List *keys, Relation matviewRel)
+{
+ TupleDesc tupdesc_recalc = tuptable_recalc->tupdesc;
+ Oid *keyTypes = NULL, *types = NULL;
+ char *keyNulls = NULL, *nulls = NULL;
+ Datum *keyVals = NULL, *vals = NULL;
+ int num_vals = list_length(namelist);
+ int num_keys = list_length(keys);
+ uint64 i;
+ Oid matviewOid;
+ char *matviewname;
+
+ matviewOid = RelationGetRelid(matviewRel);
+ matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+
+ /* If we have keys, initialize arrays for them. */
+ if (keys)
+ {
+ keyTypes = palloc(sizeof(Oid) * num_keys);
+ keyNulls = palloc(sizeof(char) * num_keys);
+ keyVals = palloc(sizeof(Datum) * num_keys);
+ /* a tuple contains keys to be recalculated and ctid to be updated*/
+ Assert(tupdesc_recalc->natts == num_keys + 1);
+
+ /* Types of key attributes */
+ for (i = 0; i < num_keys; i++)
+ keyTypes[i] = TupleDescAttr(tupdesc_recalc, i + 1)->atttypid;
+ }
+
+ /* allocate memory for all attribute names and tid */
+ types = palloc(sizeof(Oid) * (num_vals + 1));
+ nulls = palloc(sizeof(char) * (num_vals + 1));
+ vals = palloc(sizeof(Datum) * (num_vals + 1));
+
+ /* For each tuple which needs recalculation */
+ for (i = 0; i < num_tuples; i++)
+ {
+ int j;
+ bool isnull;
+ SPIPlanPtr plan;
+ SPITupleTable *tuptable_newvals;
+ TupleDesc tupdesc_newvals;
+
+ /* Set group key values as parameters if needed. */
+ if (keys)
+ {
+ for (j = 0; j < num_keys; j++)
+ {
+ keyVals[j] = SPI_getbinval(tuptable_recalc->vals[i], tupdesc_recalc, j + 2, &isnull);
+ if (isnull)
+ keyNulls[j] = 'n';
+ else
+ keyNulls[j] = ' ';
+ }
+ }
+
+ /*
+ * Get recalculated values from base tables. The result must be
+ * only one tuple thich contains the new values for specified keys.
+ */
+ plan = get_plan_for_recalc(matviewOid, namelist, keys, keyTypes);
+ if (SPI_execute_plan(plan, keyVals, keyNulls, false, 0) != SPI_OK_SELECT)
+ elog(ERROR, "SPI_execute_plan");
+ if (SPI_processed != 1)
+ elog(ERROR, "SPI_execute_plan returned zero or more than one rows");
+
+ tuptable_newvals = SPI_tuptable;
+ tupdesc_newvals = tuptable_newvals->tupdesc;
+
+ Assert(tupdesc_newvals->natts == num_vals);
+
+ /* Set the new values as parameters */
+ for (j = 0; j < tupdesc_newvals->natts; j++)
+ {
+ if (i == 0)
+ types[j] = TupleDescAttr(tupdesc_newvals, j)->atttypid;
+
+ vals[j] = SPI_getbinval(tuptable_newvals->vals[0], tupdesc_newvals, j + 1, &isnull);
+ if (isnull)
+ nulls[j] = 'n';
+ else
+ nulls[j] = ' ';
+ }
+ /* Set TID of the view tuple to be updated as a parameter */
+ types[j] = TIDOID;
+ vals[j] = SPI_getbinval(tuptable_recalc->vals[i], tupdesc_recalc, 1, &isnull);
+ nulls[j] = ' ';
+
+ /* Update the view tuple to the new values */
+ plan = get_plan_for_set_values(matviewOid, matviewname, namelist, types);
+ if (SPI_execute_plan(plan, vals, nulls, false, 0) != SPI_OK_UPDATE)
+ elog(ERROR, "SPI_execute_plan");
+ }
+}
+
+
+/*
+ * get_plan_for_recalc
+ *
+ * Create or fetch a plan for recalculating value in the view's target list
+ * from base tables using the definition query of materialized view specified
+ * by matviewOid. namelist is a list of resnames of values to be recalculated.
+ *
+ * keys is a list of keys to identify tuples to be recalculated if this is not
+ * empty. KeyTypes is an array of types of keys.
+ */
+static SPIPlanPtr
+get_plan_for_recalc(Oid matviewOid, List *namelist, List *keys, Oid *keyTypes)
+{
+ MV_QueryKey hash_key;
+ SPIPlanPtr plan;
+
+ /* Fetch or prepare a saved plan for the recalculation */
+ mv_BuildQueryKey(&hash_key, matviewOid, MV_PLAN_RECALC);
+ if ((plan = mv_FetchPreparedPlan(&hash_key)) == NULL)
+ {
+ ListCell *lc;
+ StringInfoData str;
+ char *viewdef;
+
+ /* get view definition of matview */
+ viewdef = text_to_cstring((text *) DatumGetPointer(
+ DirectFunctionCall1(pg_get_viewdef, ObjectIdGetDatum(matviewOid))));
+ /* get rid of trailing semi-colon */
+ viewdef[strlen(viewdef)-1] = '\0';
+
+ /*
+ * Build a query string for recalculating values. This is like
+ *
+ * SELECT x1, x2, x3, ... FROM ( ... view definition query ...) mv
+ * WHERE (key1, key2, ...) = ($1, $2, ...);
+ */
+
+ initStringInfo(&str);
+ appendStringInfo(&str, "SELECT ");
+ foreach (lc, namelist)
+ {
+ appendStringInfo(&str, "%s", (char *) lfirst(lc));
+ if (lnext(namelist, lc))
+ appendStringInfoString(&str, ", ");
+ }
+ appendStringInfo(&str, " FROM (%s) mv", viewdef);
+
+ if (keys)
+ {
+ int i = 1;
+ char paramname[16];
+
+ appendStringInfo(&str, " WHERE (");
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ Oid typid = attr->atttypid;
+
+ sprintf(paramname, "$%d", i);
+ appendStringInfo(&str, "(");
+ generate_equal(&str, typid, resname, paramname);
+ appendStringInfo(&str, " OR (%s IS NULL AND %s IS NULL))",
+ resname, paramname);
+
+ if (lnext(keys, lc))
+ appendStringInfoString(&str, " AND ");
+ i++;
+ }
+ appendStringInfo(&str, ")");
+ }
+ else
+ keyTypes = NULL;
+
+ plan = SPI_prepare(str.data, list_length(keys), keyTypes);
+ if (plan == NULL)
+ elog(ERROR, "SPI_prepare returned %s for %s", SPI_result_code_string(SPI_result), str.data);
+
+ SPI_keepplan(plan);
+ mv_HashPreparedPlan(&hash_key, plan);
+ }
+
+ return plan;
+}
+
+/*
+ * get_plan_for_set_values
+ *
+ * Create or fetch a plan for applying new values calculated by
+ * get_plan_for_recalc to a materialized view specified by matviewOid.
+ * matviewname is the name of the view. namelist is a list of resnames
+ * of attributes to be updated, and valTypes is an array of types of the
+ * values.
+ */
+static SPIPlanPtr
+get_plan_for_set_values(Oid matviewOid, char *matviewname, List *namelist,
+ Oid *valTypes)
+{
+ MV_QueryKey key;
+ SPIPlanPtr plan;
+
+ /* Fetch or prepare a saved plan for the real check */
+ mv_BuildQueryKey(&key, matviewOid, MV_PLAN_SET_VALUE);
+ if ((plan = mv_FetchPreparedPlan(&key)) == NULL)
+ {
+ ListCell *lc;
+ StringInfoData str;
+ int i;
+
+ /*
+ * Build a query string for applying min/max values. This is like
+ *
+ * UPDATE matviewname AS mv
+ * SET (x1, x2, x3, x4) = ($1, $2, $3, $4)
+ * WHERE ctid = $5;
+ */
+
+ initStringInfo(&str);
+ appendStringInfo(&str, "UPDATE %s AS mv SET (", matviewname);
+ foreach (lc, namelist)
+ {
+ appendStringInfo(&str, "%s", (char *) lfirst(lc));
+ if (lnext(namelist, lc))
+ appendStringInfoString(&str, ", ");
+ }
+ appendStringInfo(&str, ") = ROW(");
+
+ for (i = 1; i <= list_length(namelist); i++)
+ appendStringInfo(&str, "%s$%d", (i==1 ? "" : ", "), i);
+
+ appendStringInfo(&str, ") WHERE ctid OPERATOR(pg_catalog.=) $%d", i);
+
+ plan = SPI_prepare(str.data, list_length(namelist) + 1, valTypes);
+ if (plan == NULL)
+ elog(ERROR, "SPI_prepare returned %s for %s", SPI_result_code_string(SPI_result), str.data);
+
+ SPI_keepplan(plan);
+ mv_HashPreparedPlan(&key, plan);
+ }
+
+ return plan;
+}
+
/*
* generate_equals
*
@@ -2342,6 +3219,13 @@ mv_InitHashTables(void)
{
HASHCTL ctl;
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(MV_QueryKey);
+ ctl.entrysize = sizeof(MV_QueryHashEntry);
+ mv_query_cache = hash_create("MV query cache",
+ MV_INIT_QUERYHASHSIZE,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+
memset(&ctl, 0, sizeof(ctl));
ctl.keysize = sizeof(Oid);
ctl.entrysize = sizeof(MV_TriggerHashEntry);
@@ -2350,6 +3234,99 @@ mv_InitHashTables(void)
&ctl, HASH_ELEM | HASH_BLOBS);
}
+/*
+ * mv_FetchPreparedPlan
+ */
+static SPIPlanPtr
+mv_FetchPreparedPlan(MV_QueryKey *key)
+{
+ MV_QueryHashEntry *entry;
+ SPIPlanPtr plan;
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_query_cache)
+ mv_InitHashTables();
+
+ /*
+ * Lookup for the key
+ */
+ entry = (MV_QueryHashEntry *) hash_search(mv_query_cache,
+ (void *) key,
+ HASH_FIND, NULL);
+ if (entry == NULL)
+ return NULL;
+
+ /*
+ * Check whether the plan is still valid. If it isn't, we don't want to
+ * simply rely on plancache.c to regenerate it; rather we should start
+ * from scratch and rebuild the query text too. This is to cover cases
+ * such as table/column renames. We depend on the plancache machinery to
+ * detect possible invalidations, though.
+ *
+ * CAUTION: this check is only trustworthy if the caller has already
+ * locked both materialized views and base tables.
+ */
+ plan = entry->plan;
+ if (plan && SPI_plan_is_valid(plan))
+ return plan;
+
+ /*
+ * Otherwise we might as well flush the cached plan now, to free a little
+ * memory space before we make a new one.
+ */
+ entry->plan = NULL;
+ if (plan)
+ SPI_freeplan(plan);
+
+ return NULL;
+}
+
+/*
+ * mv_HashPreparedPlan
+ *
+ * Add another plan to our private SPI query plan hashtable.
+ */
+static void
+mv_HashPreparedPlan(MV_QueryKey *key, SPIPlanPtr plan)
+{
+ MV_QueryHashEntry *entry;
+ bool found;
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_query_cache)
+ mv_InitHashTables();
+
+ /*
+ * Add the new plan. We might be overwriting an entry previously found
+ * invalid by mv_FetchPreparedPlan.
+ */
+ entry = (MV_QueryHashEntry *) hash_search(mv_query_cache,
+ (void *) key,
+ HASH_ENTER, &found);
+ Assert(!found || entry->plan == NULL);
+ entry->plan = plan;
+}
+
+/*
+ * mv_BuildQueryKey
+ *
+ * Construct a hashtable key for a prepared SPI plan for IVM.
+ */
+static void
+mv_BuildQueryKey(MV_QueryKey *key, Oid matview_id, int32 query_type)
+{
+ /*
+ * We assume struct MV_QueryKey contains no padding bytes, else we'd need
+ * to use memset to clear them.
+ */
+ key->matview_id = matview_id;
+ key->query_type = query_type;
+}
+
/*
* AtAbort_IVM
*
diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h
index c369b3ba5e..abcc31023c 100644
--- a/src/include/commands/createas.h
+++ b/src/include/commands/createas.h
@@ -30,6 +30,7 @@ extern void CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid, bool is_cr
extern void CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create);
extern Query *rewriteQueryForIMMV(Query *query, List *colNames);
+extern void makeIvmAggColumn(ParseState *pstate, Aggref *aggref, char *resname, AttrNumber *next_resno, List **aggs);
extern int GetIntoRelEFlags(IntoClause *intoClause);
--
2.17.1
[text/x-diff] v27-0008-Add-regression-tests-for-Incremental-View-Mainte.patch (47.5K, ../[email protected]/9-v27-0008-Add-regression-tests-for-Incremental-View-Mainte.patch)
download | inline diff:
From 7b6b26597727a8c97783721aabbc92afc941a946 Mon Sep 17 00:00:00 2001
From: Takuma Hoshiai <[email protected]>
Date: Wed, 10 Mar 2021 11:11:13 +0900
Subject: [PATCH v27 8/9] Add regression tests for Incremental View Maintenance
---
.../regress/expected/incremental_matview.out | 840 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/incremental_matview.sql | 424 +++++++++
3 files changed, 1265 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/incremental_matview.out
create mode 100644 src/test/regress/sql/incremental_matview.sql
diff --git a/src/test/regress/expected/incremental_matview.out b/src/test/regress/expected/incremental_matview.out
new file mode 100644
index 0000000000..201af5f087
--- /dev/null
+++ b/src/test/regress/expected/incremental_matview.out
@@ -0,0 +1,840 @@
+-- create a table to use as a basis for views and materialized views in various combinations
+CREATE TABLE mv_base_a (i int, j int);
+INSERT INTO mv_base_a VALUES
+ (1,10),
+ (2,20),
+ (3,30),
+ (4,40),
+ (5,50);
+CREATE TABLE mv_base_b (i int, k int);
+INSERT INTO mv_base_b VALUES
+ (1,101),
+ (2,102),
+ (3,103),
+ (4,104);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ERROR: materialized view "mv_ivm_1" has not been populated
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+REFRESH MATERIALIZED VIEW mv_ivm_1;
+NOTICE: could not create an index on materialized view "mv_ivm_1" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ i | j | k
+---+----+-----
+ 1 | 10 | 101
+ 2 | 20 | 102
+ 3 | 30 | 103
+ 4 | 40 | 104
+(4 rows)
+
+-- REFRESH WITH NO DATA
+BEGIN;
+CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END
+$$ language plpgsql;
+CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT
+ON mv_base_a FROM mv_ivm_1 FOR EACH ROW
+EXECUTE PROCEDURE dummy_ivm_trigger_func();
+SELECT COUNT(*)
+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid
+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;
+ count
+-------
+ 13
+(1 row)
+
+REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA;
+SELECT COUNT(*)
+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid
+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;
+ count
+-------
+ 1
+(1 row)
+
+ROLLBACK;
+-- immediate maintenance
+BEGIN;
+INSERT INTO mv_base_b VALUES(5,105);
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ i | j | k
+---+----+-----
+ 1 | 10 | 101
+ 2 | 20 | 102
+ 3 | 30 | 103
+ 4 | 40 | 104
+ 5 | 50 | 105
+(5 rows)
+
+UPDATE mv_base_a SET j = 0 WHERE i = 1;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ i | j | k
+---+----+-----
+ 1 | 0 | 101
+ 2 | 20 | 102
+ 3 | 30 | 103
+ 4 | 40 | 104
+ 5 | 50 | 105
+(5 rows)
+
+DELETE FROM mv_base_b WHERE (i,k) = (5,105);
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ i | j | k
+---+----+-----
+ 1 | 0 | 101
+ 2 | 20 | 102
+ 3 | 30 | 103
+ 4 | 40 | 104
+(4 rows)
+
+ROLLBACK;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ i | j | k
+---+----+-----
+ 1 | 10 | 101
+ 2 | 20 | 102
+ 3 | 30 | 103
+ 4 | 40 | 104
+(4 rows)
+
+-- rename of IVM columns
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a;
+NOTICE: created index "mv_ivm_rename_index" on materialized view "mv_ivm_rename"
+ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx;
+ERROR: IVM column can not be renamed
+DROP MATERIALIZED VIEW mv_ivm_rename;
+-- unique index on IVM columns
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a;
+NOTICE: created index "mv_ivm_unique_index" on materialized view "mv_ivm_unique"
+CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__);
+ERROR: unique index creation on IVM columns is not supported
+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__));
+ERROR: unique index creation on IVM columns is not supported
+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1));
+ERROR: unique index creation on IVM columns is not supported
+DROP MATERIALIZED VIEW mv_ivm_unique;
+-- some query syntax
+BEGIN;
+CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql'
+ AS 'SELECT 1' IMMUTABLE;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func();
+NOTICE: could not create an index on materialized view "mv_ivm_func" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1;
+NOTICE: could not create an index on materialized view "mv_ivm_no_tbl" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+ROLLBACK;
+-- result of materialized view have DISTINCT clause or the duplicate result.
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a;
+NOTICE: could not create an index on materialized view "mv_ivm_duplicate" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a;
+NOTICE: created index "mv_ivm_distinct_index" on materialized view "mv_ivm_distinct"
+INSERT INTO mv_base_a VALUES(6,20);
+SELECT * FROM mv_ivm_duplicate ORDER BY 1;
+ j
+----
+ 10
+ 20
+ 20
+ 30
+ 40
+ 50
+(6 rows)
+
+SELECT * FROM mv_ivm_distinct ORDER BY 1;
+ j
+----
+ 10
+ 20
+ 30
+ 40
+ 50
+(5 rows)
+
+DELETE FROM mv_base_a WHERE (i,j) = (2,20);
+SELECT * FROM mv_ivm_duplicate ORDER BY 1;
+ j
+----
+ 10
+ 20
+ 30
+ 40
+ 50
+(5 rows)
+
+SELECT * FROM mv_ivm_distinct ORDER BY 1;
+ j
+----
+ 10
+ 20
+ 30
+ 40
+ 50
+(5 rows)
+
+ROLLBACK;
+-- support SUM(), COUNT() and AVG() aggregate functions
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;
+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+ i | sum | count | avg
+---+-----+-------+---------------------
+ 1 | 10 | 1 | 10.0000000000000000
+ 2 | 20 | 1 | 20.0000000000000000
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+INSERT INTO mv_base_a VALUES(2,100);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+ i | sum | count | avg
+---+-----+-------+---------------------
+ 1 | 10 | 1 | 10.0000000000000000
+ 2 | 120 | 2 | 60.0000000000000000
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+ i | sum | count | avg
+---+-----+-------+----------------------
+ 1 | 10 | 1 | 10.0000000000000000
+ 2 | 220 | 2 | 110.0000000000000000
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+DELETE FROM mv_base_a WHERE (i,j) = (2,200);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+ i | sum | count | avg
+---+-----+-------+---------------------
+ 1 | 10 | 1 | 10.0000000000000000
+ 2 | 20 | 1 | 20.0000000000000000
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+ROLLBACK;
+-- support COUNT(*) aggregate function
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;
+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
+ i | sum | count
+---+-----+-------
+ 1 | 10 | 1
+ 2 | 20 | 1
+ 3 | 30 | 1
+ 4 | 40 | 1
+ 5 | 50 | 1
+(5 rows)
+
+INSERT INTO mv_base_a VALUES(2,100);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
+ i | sum | count
+---+-----+-------
+ 1 | 10 | 1
+ 2 | 120 | 2
+ 3 | 30 | 1
+ 4 | 40 | 1
+ 5 | 50 | 1
+(5 rows)
+
+ROLLBACK;
+-- support aggregate functions without GROUP clause
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;
+NOTICE: could not create an index on materialized view "mv_ivm_group" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv_ivm_group ORDER BY 1;
+ sum | count | avg
+-----+-------+---------------------
+ 150 | 5 | 30.0000000000000000
+(1 row)
+
+INSERT INTO mv_base_a VALUES(6,60);
+SELECT * FROM mv_ivm_group ORDER BY 1;
+ sum | count | avg
+-----+-------+---------------------
+ 210 | 6 | 35.0000000000000000
+(1 row)
+
+DELETE FROM mv_base_a;
+SELECT * FROM mv_ivm_group ORDER BY 1;
+ sum | count | avg
+-----+-------+-----
+ | 0 |
+(1 row)
+
+ROLLBACK;
+-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect.
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i;
+NOTICE: created index "mv_ivm_avg_bug_index" on materialized view "mv_ivm_avg_bug"
+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
+ i | sum | count | avg
+---+-----+-------+---------------------
+ 1 | 10 | 1 | 10.0000000000000000
+ 2 | 20 | 1 | 20.0000000000000000
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+INSERT INTO mv_base_a VALUES
+ (1,0),
+ (1,0),
+ (2,30),
+ (2,30);
+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
+ i | sum | count | avg
+---+-----+-------+---------------------
+ 1 | 10 | 3 | 3.3333333333333333
+ 2 | 80 | 3 | 26.6666666666666667
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+DELETE FROM mv_base_a WHERE (i,j) = (1,0);
+DELETE FROM mv_base_a WHERE (i,j) = (2,30);
+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
+ i | sum | count | avg
+---+-----+-------+---------------------
+ 1 | 10 | 1 | 10.0000000000000000
+ 2 | 20 | 1 | 20.0000000000000000
+ 3 | 30 | 1 | 30.0000000000000000
+ 4 | 40 | 1 | 40.0000000000000000
+ 5 | 50 | 1 | 50.0000000000000000
+(5 rows)
+
+ROLLBACK;
+-- support MIN(), MAX() aggregate functions
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT i, MIN(j), MAX(j) FROM mv_base_a GROUP BY i;
+NOTICE: created index "mv_ivm_min_max_index" on materialized view "mv_ivm_min_max"
+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
+ i | min | max
+---+-----+-----
+ 1 | 10 | 10
+ 2 | 20 | 20
+ 3 | 30 | 30
+ 4 | 40 | 40
+ 5 | 50 | 50
+(5 rows)
+
+INSERT INTO mv_base_a VALUES
+ (1,11), (1,12),
+ (2,21), (2,22),
+ (3,31), (3,32),
+ (4,41), (4,42),
+ (5,51), (5,52);
+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
+ i | min | max
+---+-----+-----
+ 1 | 10 | 12
+ 2 | 20 | 22
+ 3 | 30 | 32
+ 4 | 40 | 42
+ 5 | 50 | 52
+(5 rows)
+
+DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32));
+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
+ i | min | max
+---+-----+-----
+ 1 | 11 | 12
+ 2 | 20 | 22
+ 3 | 30 | 31
+ 4 | 40 | 42
+ 5 | 50 | 52
+(5 rows)
+
+ROLLBACK;
+-- support MIN(), MAX() aggregate functions without GROUP clause
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT MIN(j), MAX(j) FROM mv_base_a;
+NOTICE: could not create an index on materialized view "mv_ivm_min_max" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv_ivm_min_max;
+ min | max
+-----+-----
+ 10 | 50
+(1 row)
+
+INSERT INTO mv_base_a VALUES
+ (0,0), (6,60), (7,70);
+SELECT * FROM mv_ivm_min_max;
+ min | max
+-----+-----
+ 0 | 70
+(1 row)
+
+DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70));
+SELECT * FROM mv_ivm_min_max;
+ min | max
+-----+-----
+ 10 | 60
+(1 row)
+
+DELETE FROM mv_base_a;
+SELECT * FROM mv_ivm_min_max;
+ min | max
+-----+-----
+ |
+(1 row)
+
+ROLLBACK;
+-- support self join view and multiple change on the same table
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS
+ SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i;
+NOTICE: could not create an index on materialized view "mv_self" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv_self ORDER BY v1;
+ v1 | v2
+----+----
+ 10 | 10
+ 20 | 20
+ 30 | 30
+(3 rows)
+
+INSERT INTO base_t VALUES (4,40);
+DELETE FROM base_t WHERE i = 1;
+UPDATE base_t SET v = v*10 WHERE i=2;
+SELECT * FROM mv_self ORDER BY v1;
+ v1 | v2
+-----+-----
+ 30 | 30
+ 40 | 40
+ 200 | 200
+(3 rows)
+
+WITH
+ ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1),
+ ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1),
+ upd_t AS (UPDATE base_t SET v = v + 100 RETURNING 1),
+ dlt_t AS (DELETE FROM base_t WHERE i IN (4,5) RETURNING 1)
+SELECT NULL;
+ ?column?
+----------
+
+(1 row)
+
+SELECT * FROM mv_self ORDER BY v1;
+ v1 | v2
+-----+-----
+ 50 | 50
+ 60 | 60
+ 130 | 130
+ 300 | 300
+(4 rows)
+
+ROLLBACK;
+-- support simultaneous table changes
+BEGIN;
+CREATE TABLE base_r (i int, v int);
+CREATE TABLE base_s (i int, v int);
+INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30);
+INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300);
+CREATE INCREMENTAL MATERIALIZED VIEW mv(v1, v2) AS
+ SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i);
+NOTICE: could not create an index on materialized view "mv" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv ORDER BY v1;
+ v1 | v2
+----+-----
+ 10 | 100
+ 20 | 200
+ 30 | 300
+(3 rows)
+
+WITH
+ ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1),
+ ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1),
+ ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1),
+ upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1),
+ dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1)
+SELECT NULL;
+ ?column?
+----------
+
+(1 row)
+
+SELECT * FROM mv ORDER BY v1;
+ v1 | v2
+------+-----
+ 10 | 100
+ 11 | 100
+ 1020 | 200
+ 1020 | 222
+(4 rows)
+
+ROLLBACK;
+-- support foreign reference constraints
+BEGIN;
+CREATE TABLE ri1 (i int PRIMARY KEY);
+CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int);
+INSERT INTO ri1 VALUES (1),(2),(3);
+INSERT INTO ri2 VALUES (1),(2),(3);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ri(i1, i2) AS
+ SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i);
+NOTICE: created index "mv_ri_index" on materialized view "mv_ri"
+SELECT * FROM mv_ri ORDER BY i1;
+ i1 | i2
+----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+(3 rows)
+
+UPDATE ri1 SET i=10 where i=1;
+DELETE FROM ri1 WHERE i=2;
+SELECT * FROM mv_ri ORDER BY i2;
+ i1 | i2
+----+----
+ 3 | 3
+ 10 | 10
+(2 rows)
+
+ROLLBACK;
+-- views including NULL
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (1,10),(2, NULL);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;
+NOTICE: could not create an index on materialized view "mv" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv ORDER BY i;
+ i | v
+---+----
+ 1 | 10
+ 2 |
+(2 rows)
+
+UPDATE base_t SET v = 20 WHERE i = 2;
+SELECT * FROM mv ORDER BY i;
+ i | v
+---+----
+ 1 | 10
+ 2 | 20
+(2 rows)
+
+ROLLBACK;
+BEGIN;
+CREATE TABLE base_t (i int);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;
+NOTICE: could not create an index on materialized view "mv" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT * FROM mv ORDER BY i;
+ i
+---
+(0 rows)
+
+INSERT INTO base_t VALUES (1),(NULL);
+SELECT * FROM mv ORDER BY i;
+ i
+---
+ 1
+
+(2 rows)
+
+ROLLBACK;
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i;
+NOTICE: created index "mv_index" on materialized view "mv"
+SELECT * FROM mv ORDER BY i;
+ i | sum
+---+-----
+ 1 | 30
+ | 3
+(2 rows)
+
+UPDATE base_t SET v = v * 10;
+SELECT * FROM mv ORDER BY i;
+ i | sum
+---+-----
+ 1 | 300
+ | 30
+(2 rows)
+
+ROLLBACK;
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, min(v), max(v) FROM base_t GROUP BY i;
+NOTICE: created index "mv_index" on materialized view "mv"
+SELECT * FROM mv ORDER BY i;
+ i | min | max
+---+-----+-----
+ | 1 | 5
+(1 row)
+
+DELETE FROM base_t WHERE v = 1;
+SELECT * FROM mv ORDER BY i;
+ i | min | max
+---+-----+-----
+ | 2 | 5
+(1 row)
+
+DELETE FROM base_t WHERE v = 3;
+SELECT * FROM mv ORDER BY i;
+ i | min | max
+---+-----+-----
+ | 2 | 5
+(1 row)
+
+DELETE FROM base_t WHERE v = 5;
+SELECT * FROM mv ORDER BY i;
+ i | min | max
+---+-----+-----
+ | 2 | 4
+(1 row)
+
+ROLLBACK;
+-- IMMV containing user defined type
+BEGIN;
+CREATE TYPE mytype;
+CREATE FUNCTION mytype_in(cstring)
+ RETURNS mytype AS 'int4in'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+NOTICE: return type mytype is only a shell
+CREATE FUNCTION mytype_out(mytype)
+ RETURNS cstring AS 'int4out'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+NOTICE: argument type mytype is only a shell
+CREATE TYPE mytype (
+ LIKE = int4,
+ INPUT = mytype_in,
+ OUTPUT = mytype_out
+);
+CREATE FUNCTION mytype_eq(mytype, mytype)
+ RETURNS bool AS 'int4eq'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE FUNCTION mytype_lt(mytype, mytype)
+ RETURNS bool AS 'int4lt'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE FUNCTION mytype_cmp(mytype, mytype)
+ RETURNS integer AS 'btint4cmp'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE OPERATOR = (
+ leftarg = mytype, rightarg = mytype,
+ procedure = mytype_eq);
+CREATE OPERATOR < (
+ leftarg = mytype, rightarg = mytype,
+ procedure = mytype_lt);
+CREATE OPERATOR CLASS mytype_ops
+ DEFAULT FOR TYPE mytype USING btree AS
+ OPERATOR 1 <,
+ OPERATOR 3 = ,
+ FUNCTION 1 mytype_cmp(mytype,mytype);
+CREATE TABLE t_mytype (x mytype);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS
+ SELECT * FROM t_mytype;
+NOTICE: could not create an index on materialized view "mv_mytype" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+INSERT INTO t_mytype VALUES ('1'::mytype);
+SELECT * FROM mv_mytype;
+ x
+---
+ 1
+(1 row)
+
+ROLLBACK;
+-- outer join is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i;
+ERROR: OUTER JOIN is not supported on incrementally maintainable materialized view
+-- CTE is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS
+ WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i;
+ERROR: CTE is not supported on incrementally maintainable materialized view
+-- contain system column
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a;
+ERROR: system column is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';
+ERROR: system column is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a;
+ERROR: system column is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a;
+ERROR: system column is not supported on incrementally maintainable materialized view
+-- contain subquery
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );
+ERROR: subquery is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i;
+ERROR: subquery is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;
+ERROR: subquery is not supported on incrementally maintainable materialized view
+-- contain ORDER BY
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k;
+ERROR: ORDER BY clause is not supported on incrementally maintainable materialized view
+-- contain HAVING
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5;
+ERROR: HAVING clause is not supported on incrementally maintainable materialized view
+-- contain view or materialized view
+CREATE VIEW b_view AS SELECT i,k FROM mv_base_b;
+CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i;
+ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i;
+ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i;
+ERROR: subquery is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5;
+ERROR: subquery is not supported on incrementally maintainable materialized view
+-- contain mutable functions
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;
+ERROR: mutable function is not supported on incrementally maintainable materialized view
+HINT: functions must be marked IMMUTABLE
+-- LIMIT/OFFSET is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5;
+ERROR: LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view
+-- DISTINCT ON is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;
+ERROR: DISTINCT ON is not supported on incrementally maintainable materialized view
+-- TABLESAMPLE clause is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50);
+ERROR: TABLESAMPLE clause is not supported on incrementally maintainable materialized view
+-- window functions are not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a;
+ERROR: window functions are not supported on incrementally maintainable materialized view
+-- aggregate function with some options is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a;
+ERROR: aggregate function with FILTER clause is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm18 AS SELECT COUNT(DISTINCT i) FROM mv_base_a;
+ERROR: aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a;
+ERROR: aggregate function with ORDER clause is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),());
+ERROR: GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view
+-- inheritance parent is not supported
+BEGIN;
+CREATE TABLE parent (i int, v int);
+CREATE TABLE child_a(options text) INHERITS(parent);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm21 AS SELECT * FROM parent;
+ERROR: inheritance parent is not supported on incrementally maintainable materialized view
+ROLLBACK;
+-- UNION statement is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;;
+ERROR: UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view
+-- empty target list is not allowed with IVM
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm25 AS SELECT FROM mv_base_a;
+ERROR: empty target list is not supported on incrementally maintainable materialized view
+-- FOR UPDATE/SHARE is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE;
+ERROR: FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view
+-- tartget list cannot contain ivm column that start with '__ivm'
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a;
+ERROR: column name __ivm_count__ is not supported on incrementally maintainable materialized view
+-- expressions specified in GROUP BY must appear in the target list.
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;
+ERROR: GROUP BY expression not appearing in select list is not supported on incrementally maintainable materialized view
+-- experssions containing an aggregate is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a;
+ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a;
+ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view
+-- VALUES is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1);
+ERROR: VALUES is not supported on incrementally maintainable materialized view
+-- base table which has row level security
+DROP USER IF EXISTS ivm_admin;
+NOTICE: role "ivm_admin" does not exist, skipping
+DROP USER IF EXISTS ivm_user;
+NOTICE: role "ivm_user" does not exist, skipping
+CREATE USER ivm_admin;
+CREATE USER ivm_user;
+SET SESSION AUTHORIZATION ivm_admin;
+CREATE TABLE rls_tbl(id int, data text, owner name);
+INSERT INTO rls_tbl VALUES
+ (1,'foo','ivm_user'),
+ (2,'bar','postgres');
+CREATE TABLE num_tbl(id int, num text);
+INSERT INTO num_tbl VALUES
+ (1,'one'),
+ (2,'two'),
+ (3,'three'),
+ (4,'four');
+CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user);
+CREATE POLICY rls_tbl_policy2 ON rls_tbl FOR INSERT TO PUBLIC WITH CHECK(current_user LIKE 'ivm_%');
+ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
+GRANT ALL on rls_tbl TO PUBLIC;
+GRANT ALL on num_tbl TO PUBLIC;
+SET SESSION AUTHORIZATION ivm_user;
+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls AS SELECT * FROM rls_tbl;
+NOTICE: could not create an index on materialized view "ivm_rls" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
+ id | data | owner
+----+------+----------
+ 1 | foo | ivm_user
+(1 row)
+
+INSERT INTO rls_tbl VALUES
+ (3,'baz','ivm_user'),
+ (4,'qux','postgres');
+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
+ id | data | owner
+----+------+----------
+ 1 | foo | ivm_user
+ 3 | baz | ivm_user
+(2 rows)
+
+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls2 AS SELECT * FROM rls_tbl JOIN num_tbl USING(id);
+NOTICE: could not create an index on materialized view "ivm_rls2" automatically
+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
+HINT: Create an index on the materialized view for efficient incremental maintenance.
+RESET SESSION AUTHORIZATION;
+WITH
+ x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)),
+ y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4))
+SELECT;
+--
+(1 row)
+
+SELECT * FROM ivm_rls2 ORDER BY 1,2,3;
+ id | data | owner | num
+----+-------+----------+---------
+ 1 | foo | ivm_user | one
+ 3 | baz_2 | ivm_user | three_2
+(2 rows)
+
+DROP TABLE rls_tbl CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to materialized view ivm_rls
+drop cascades to materialized view ivm_rls2
+DROP TABLE num_tbl CASCADE;
+DROP USER ivm_user;
+DROP USER ivm_admin;
+DROP TABLE mv_base_b CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to materialized view mv_ivm_1
+drop cascades to view b_view
+drop cascades to materialized view b_mview
+DROP TABLE mv_base_a CASCADE;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 103e11483d..3027f4e78c 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -86,7 +86,7 @@ test: brin_bloom brin_multi
# psql depends on create_am
# amutils depends on geometry, create_index_spgist, hash_index, brin
# ----------
-test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role
+test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role incremental_matview
# collate.*.utf8 tests cannot be run in parallel with each other
test: rules psql psql_crosstab amutils stats_ext collate.linux.utf8
diff --git a/src/test/regress/sql/incremental_matview.sql b/src/test/regress/sql/incremental_matview.sql
new file mode 100644
index 0000000000..a4ce2c741e
--- /dev/null
+++ b/src/test/regress/sql/incremental_matview.sql
@@ -0,0 +1,424 @@
+-- create a table to use as a basis for views and materialized views in various combinations
+CREATE TABLE mv_base_a (i int, j int);
+INSERT INTO mv_base_a VALUES
+ (1,10),
+ (2,20),
+ (3,30),
+ (4,40),
+ (5,50);
+CREATE TABLE mv_base_b (i int, k int);
+INSERT INTO mv_base_b VALUES
+ (1,101),
+ (2,102),
+ (3,103),
+ (4,104);
+
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+REFRESH MATERIALIZED VIEW mv_ivm_1;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+
+-- REFRESH WITH NO DATA
+BEGIN;
+CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END
+$$ language plpgsql;
+
+CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT
+ON mv_base_a FROM mv_ivm_1 FOR EACH ROW
+EXECUTE PROCEDURE dummy_ivm_trigger_func();
+
+SELECT COUNT(*)
+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid
+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;
+
+REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA;
+
+SELECT COUNT(*)
+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid
+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;
+ROLLBACK;
+
+-- immediate maintenance
+BEGIN;
+INSERT INTO mv_base_b VALUES(5,105);
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+UPDATE mv_base_a SET j = 0 WHERE i = 1;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+DELETE FROM mv_base_b WHERE (i,k) = (5,105);
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+ROLLBACK;
+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;
+
+-- rename of IVM columns
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a;
+ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx;
+DROP MATERIALIZED VIEW mv_ivm_rename;
+
+-- unique index on IVM columns
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a;
+CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__);
+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__));
+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1));
+DROP MATERIALIZED VIEW mv_ivm_unique;
+
+-- some query syntax
+BEGIN;
+CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql'
+ AS 'SELECT 1' IMMUTABLE;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func();
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1;
+ROLLBACK;
+
+-- result of materialized view have DISTINCT clause or the duplicate result.
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a;
+INSERT INTO mv_base_a VALUES(6,20);
+SELECT * FROM mv_ivm_duplicate ORDER BY 1;
+SELECT * FROM mv_ivm_distinct ORDER BY 1;
+DELETE FROM mv_base_a WHERE (i,j) = (2,20);
+SELECT * FROM mv_ivm_duplicate ORDER BY 1;
+SELECT * FROM mv_ivm_distinct ORDER BY 1;
+ROLLBACK;
+
+-- support SUM(), COUNT() and AVG() aggregate functions
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+INSERT INTO mv_base_a VALUES(2,100);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+DELETE FROM mv_base_a WHERE (i,j) = (2,200);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;
+ROLLBACK;
+
+-- support COUNT(*) aggregate function
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
+INSERT INTO mv_base_a VALUES(2,100);
+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;
+ROLLBACK;
+
+-- support aggregate functions without GROUP clause
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;
+SELECT * FROM mv_ivm_group ORDER BY 1;
+INSERT INTO mv_base_a VALUES(6,60);
+SELECT * FROM mv_ivm_group ORDER BY 1;
+DELETE FROM mv_base_a;
+SELECT * FROM mv_ivm_group ORDER BY 1;
+ROLLBACK;
+
+-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect.
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i;
+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
+INSERT INTO mv_base_a VALUES
+ (1,0),
+ (1,0),
+ (2,30),
+ (2,30);
+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
+DELETE FROM mv_base_a WHERE (i,j) = (1,0);
+DELETE FROM mv_base_a WHERE (i,j) = (2,30);
+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;
+ROLLBACK;
+
+-- support MIN(), MAX() aggregate functions
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT i, MIN(j), MAX(j) FROM mv_base_a GROUP BY i;
+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
+INSERT INTO mv_base_a VALUES
+ (1,11), (1,12),
+ (2,21), (2,22),
+ (3,31), (3,32),
+ (4,41), (4,42),
+ (5,51), (5,52);
+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
+DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32));
+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;
+ROLLBACK;
+
+-- support MIN(), MAX() aggregate functions without GROUP clause
+BEGIN;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT MIN(j), MAX(j) FROM mv_base_a;
+SELECT * FROM mv_ivm_min_max;
+INSERT INTO mv_base_a VALUES
+ (0,0), (6,60), (7,70);
+SELECT * FROM mv_ivm_min_max;
+DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70));
+SELECT * FROM mv_ivm_min_max;
+DELETE FROM mv_base_a;
+SELECT * FROM mv_ivm_min_max;
+ROLLBACK;
+
+-- support self join view and multiple change on the same table
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS
+ SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i;
+SELECT * FROM mv_self ORDER BY v1;
+INSERT INTO base_t VALUES (4,40);
+DELETE FROM base_t WHERE i = 1;
+UPDATE base_t SET v = v*10 WHERE i=2;
+SELECT * FROM mv_self ORDER BY v1;
+WITH
+ ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1),
+ ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1),
+ upd_t AS (UPDATE base_t SET v = v + 100 RETURNING 1),
+ dlt_t AS (DELETE FROM base_t WHERE i IN (4,5) RETURNING 1)
+SELECT NULL;
+SELECT * FROM mv_self ORDER BY v1;
+ROLLBACK;
+
+-- support simultaneous table changes
+BEGIN;
+CREATE TABLE base_r (i int, v int);
+CREATE TABLE base_s (i int, v int);
+INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30);
+INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300);
+CREATE INCREMENTAL MATERIALIZED VIEW mv(v1, v2) AS
+ SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i);
+SELECT * FROM mv ORDER BY v1;
+WITH
+ ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1),
+ ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1),
+ ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1),
+ upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1),
+ dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1)
+SELECT NULL;
+SELECT * FROM mv ORDER BY v1;
+ROLLBACK;
+
+-- support foreign reference constraints
+BEGIN;
+CREATE TABLE ri1 (i int PRIMARY KEY);
+CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int);
+INSERT INTO ri1 VALUES (1),(2),(3);
+INSERT INTO ri2 VALUES (1),(2),(3);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ri(i1, i2) AS
+ SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i);
+SELECT * FROM mv_ri ORDER BY i1;
+UPDATE ri1 SET i=10 where i=1;
+DELETE FROM ri1 WHERE i=2;
+SELECT * FROM mv_ri ORDER BY i2;
+ROLLBACK;
+
+-- views including NULL
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (1,10),(2, NULL);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;
+SELECT * FROM mv ORDER BY i;
+UPDATE base_t SET v = 20 WHERE i = 2;
+SELECT * FROM mv ORDER BY i;
+ROLLBACK;
+
+BEGIN;
+CREATE TABLE base_t (i int);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;
+SELECT * FROM mv ORDER BY i;
+INSERT INTO base_t VALUES (1),(NULL);
+SELECT * FROM mv ORDER BY i;
+ROLLBACK;
+
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i;
+SELECT * FROM mv ORDER BY i;
+UPDATE base_t SET v = v * 10;
+SELECT * FROM mv ORDER BY i;
+ROLLBACK;
+
+BEGIN;
+CREATE TABLE base_t (i int, v int);
+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5);
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, min(v), max(v) FROM base_t GROUP BY i;
+SELECT * FROM mv ORDER BY i;
+DELETE FROM base_t WHERE v = 1;
+SELECT * FROM mv ORDER BY i;
+DELETE FROM base_t WHERE v = 3;
+SELECT * FROM mv ORDER BY i;
+DELETE FROM base_t WHERE v = 5;
+SELECT * FROM mv ORDER BY i;
+ROLLBACK;
+
+-- IMMV containing user defined type
+BEGIN;
+
+CREATE TYPE mytype;
+CREATE FUNCTION mytype_in(cstring)
+ RETURNS mytype AS 'int4in'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE FUNCTION mytype_out(mytype)
+ RETURNS cstring AS 'int4out'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE TYPE mytype (
+ LIKE = int4,
+ INPUT = mytype_in,
+ OUTPUT = mytype_out
+);
+
+CREATE FUNCTION mytype_eq(mytype, mytype)
+ RETURNS bool AS 'int4eq'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE FUNCTION mytype_lt(mytype, mytype)
+ RETURNS bool AS 'int4lt'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+CREATE FUNCTION mytype_cmp(mytype, mytype)
+ RETURNS integer AS 'btint4cmp'
+ LANGUAGE INTERNAL STRICT IMMUTABLE;
+
+CREATE OPERATOR = (
+ leftarg = mytype, rightarg = mytype,
+ procedure = mytype_eq);
+CREATE OPERATOR < (
+ leftarg = mytype, rightarg = mytype,
+ procedure = mytype_lt);
+
+CREATE OPERATOR CLASS mytype_ops
+ DEFAULT FOR TYPE mytype USING btree AS
+ OPERATOR 1 <,
+ OPERATOR 3 = ,
+ FUNCTION 1 mytype_cmp(mytype,mytype);
+
+CREATE TABLE t_mytype (x mytype);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS
+ SELECT * FROM t_mytype;
+INSERT INTO t_mytype VALUES ('1'::mytype);
+SELECT * FROM mv_mytype;
+
+ROLLBACK;
+
+-- outer join is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i;
+-- CTE is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS
+ WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i;
+-- contain system column
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a;
+-- contain subquery
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;
+-- contain ORDER BY
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k;
+-- contain HAVING
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5;
+
+-- contain view or materialized view
+CREATE VIEW b_view AS SELECT i,k FROM mv_base_b;
+CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5;
+
+-- contain mutable functions
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;
+
+-- LIMIT/OFFSET is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5;
+
+-- DISTINCT ON is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;
+
+-- TABLESAMPLE clause is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50);
+
+-- window functions are not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a;
+
+-- aggregate function with some options is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm18 AS SELECT COUNT(DISTINCT i) FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),());
+
+-- inheritance parent is not supported
+BEGIN;
+CREATE TABLE parent (i int, v int);
+CREATE TABLE child_a(options text) INHERITS(parent);
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm21 AS SELECT * FROM parent;
+ROLLBACK;
+
+-- UNION statement is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;;
+
+-- empty target list is not allowed with IVM
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm25 AS SELECT FROM mv_base_a;
+
+-- FOR UPDATE/SHARE is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE;
+
+-- tartget list cannot contain ivm column that start with '__ivm'
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a;
+
+-- expressions specified in GROUP BY must appear in the target list.
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;
+
+-- experssions containing an aggregate is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a;
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a;
+
+-- VALUES is not supported
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1);
+
+-- base table which has row level security
+DROP USER IF EXISTS ivm_admin;
+DROP USER IF EXISTS ivm_user;
+CREATE USER ivm_admin;
+CREATE USER ivm_user;
+SET SESSION AUTHORIZATION ivm_admin;
+
+CREATE TABLE rls_tbl(id int, data text, owner name);
+INSERT INTO rls_tbl VALUES
+ (1,'foo','ivm_user'),
+ (2,'bar','postgres');
+CREATE TABLE num_tbl(id int, num text);
+INSERT INTO num_tbl VALUES
+ (1,'one'),
+ (2,'two'),
+ (3,'three'),
+ (4,'four');
+CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user);
+CREATE POLICY rls_tbl_policy2 ON rls_tbl FOR INSERT TO PUBLIC WITH CHECK(current_user LIKE 'ivm_%');
+ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
+GRANT ALL on rls_tbl TO PUBLIC;
+GRANT ALL on num_tbl TO PUBLIC;
+
+SET SESSION AUTHORIZATION ivm_user;
+
+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls AS SELECT * FROM rls_tbl;
+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
+INSERT INTO rls_tbl VALUES
+ (3,'baz','ivm_user'),
+ (4,'qux','postgres');
+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;
+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls2 AS SELECT * FROM rls_tbl JOIN num_tbl USING(id);
+
+RESET SESSION AUTHORIZATION;
+
+WITH
+ x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)),
+ y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4))
+SELECT;
+SELECT * FROM ivm_rls2 ORDER BY 1,2,3;
+
+DROP TABLE rls_tbl CASCADE;
+DROP TABLE num_tbl CASCADE;
+DROP USER ivm_user;
+DROP USER ivm_admin;
+
+DROP TABLE mv_base_b CASCADE;
+DROP TABLE mv_base_a CASCADE;
--
2.17.1
[text/x-diff] v27-0009-Add-documentations-about-Incremental-View-Mainte.patch (25.2K, ../[email protected]/10-v27-0009-Add-documentations-about-Incremental-View-Mainte.patch)
download | inline diff:
From 25dcb137648c51e98471beada93788e95a869323 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Fri, 20 Dec 2019 10:25:34 +0900
Subject: [PATCH v27 9/9] Add documentations about Incremental View Maintenance
---
doc/src/sgml/catalogs.sgml | 24 +-
.../sgml/ref/create_materialized_view.sgml | 131 +++++-
.../sgml/ref/refresh_materialized_view.sgml | 8 +-
doc/src/sgml/rules.sgml | 443 ++++++++++++++++++
4 files changed, 601 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a533a2153e..2561faa163 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2193,6 +2193,15 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relisivm</structfield> <type>bool</type>
+ </para>
+ <para>
+ True if materialized view enables incremental view maintenance
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>relrewrite</structfield> <type>oid</type>
@@ -3508,6 +3517,18 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><symbol>DEPENDENCY_IMMV</symbol> (<literal>m</literal>)</term>
+ <listitem>
+ <para>
+ The dependent object was created as part of creation of the Materialized
+ View with Incremental View Maintenance reference, and is really just a
+ part of its internal implementation. The dependent object must not be
+ dropped unless the materialized view is also dropped.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
Other dependency flavors might be needed in future.
@@ -3891,7 +3912,8 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<structfield>extrelocatable</structfield> <type>bool</type>
</para>
<para>
- True if extension can be relocated to another schema
+ True for materialized views which are enabled for incremental
+ view maintenance (IVM).
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index 0d2fea2b97..52a4d206b1 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>] [, ... ] ) ]
@@ -60,6 +60,132 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>INCREMENTAL</literal></term>
+ <listitem>
+ <para>
+ If specified, some triggers are automatically created so that the rows
+ of the materialized view are immediately updated when base tables of the
+ materialized view are updated. In general, this allows faster update of
+ the materialized view at a price of slower update of the base tables
+ because the triggers will be invoked. We call this form of materialized
+ view as "Incrementally Maintainable Materialized View" (IMMV).
+ </para>
+ <para>
+ When <acronym>IMMV</acronym> is defined without using <command>WITH NO DATA</command>,
+ a unique index is created on the view automatically if possible. If the view
+ definition query has a GROUP BY clause, a unique index is created on the columns
+ of GROUP BY expressions. Also, if the view has DISTINCT clause, a unique index
+ is created on all columns in the target list. Otherwise, if the view contains all
+ primary key attritubes of its base tables in the target list, a unique index is
+ created on these attritubes. In other cases, no index is created.
+ </para>
+ <para>
+ There are restrictions of query definitions allowed to use this
+ option. The following are supported in query definitions for IMMV:
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Inner joins (including self-joins).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Some built-in aggregate functions (count, sum, avg, min, max) without a HAVING
+ clause.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ Unsupported queries with this option include the following:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Outer joins.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Sub-queries.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Aggregate functions other than built-in count, sum, avg, min and max.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Aggregate functions with a HAVING clause.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ DISTINCT ON, WINDOW, VALUES, LIMIT and OFFSET clause.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ Other restrictions include:
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ IMMVs must be based on simple base tables. It's not supported to
+ create them on top of views or materialized views.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When the TRUNCATE command is executed on a base table,
+ no changes are made to the materialized view.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ It is not supported to include system columns in an IMMV.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';
+ERROR: system column is not supported with IVM
+ </programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Non-immutable functions are not supported.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;
+ERROR: functions in IMMV must be marked IMMUTABLE
+ </programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ IMMVs do not support expressions that contains aggregates
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Logical replication does not support IMMVs.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
@@ -155,7 +281,8 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
This clause specifies whether or not the materialized view should be
populated at creation time. If not, the materialized view will be
flagged as unscannable and cannot be queried until <command>REFRESH
- MATERIALIZED VIEW</command> is used.
+ MATERIALIZED VIEW</command> is used. Also, if the view is IMMV,
+ triggers for maintaining the view are not created.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml
index 675d6090f3..c29cfc19b6 100644
--- a/doc/src/sgml/ref/refresh_materialized_view.sgml
+++ b/doc/src/sgml/ref/refresh_materialized_view.sgml
@@ -35,9 +35,13 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
owner of the materialized view. The old contents are discarded. If
<literal>WITH DATA</literal> is specified (or defaults) the backing query
is executed to provide the new data, and the materialized view is left in a
- scannable state. If <literal>WITH NO DATA</literal> is specified no new
+ scannable state. If the view is an incrementally maintainable materialized
+ view (IMMV) and was unpopulated, triggers for maintaining the view are
+ created. Also, a unique index is created for IMMV if it is possible and the
+ view doesn't have that yet.
+ If <literal>WITH NO DATA</literal> is specified no new
data is generated and the materialized view is left in an unscannable
- state.
+ state. If the view is IMMV, the triggers are dropped.
</para>
<para>
<literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 4b2ba5a4e6..71e10ef5a1 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1090,6 +1090,449 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;
</sect1>
+<sect1 id="rules-ivm">
+<title>Incremental View Maintenance</title>
+
+<indexterm zone="rules-ivm">
+ <primary>incremental view maintenance</primary>
+</indexterm>
+
+<indexterm zone="rules-ivm">
+ <primary>materialized view</primary>
+ <secondary>incremental view maintenance</secondary>
+</indexterm>
+
+<indexterm zone="rules-ivm">
+ <primary>view</primary>
+ <secondary>incremental view maintenance</secondary>
+</indexterm>
+
+<sect2 id="rules-ivm-overview">
+<title>Overview</title>
+
+<para>
+ Incremental View Maintenance (<acronym>IVM</acronym>) is a way to make
+ materialized views up-to-date in which only incremental changes are computed
+ and applied on views rather than recomputing the contents from scratch as
+ <command>REFRESH MATERIALIZED VIEW</command> does. <acronym>IVM</acronym>
+ can update materialized views more efficiently than recomputation when only
+ small parts of the view are changed.
+</para>
+
+<para>
+ There are two approaches with regard to timing of view maintenance:
+ immediate and deferred. In immediate maintenance, views are updated in the
+ same transaction that its base table is modified. In deferred maintenance,
+ views are updated after the transaction is committed, for example, when the
+ view is accessed, as a response to user command like <command>REFRESH
+ MATERIALIZED VIEW</command>, or periodically in background, and so on.
+ <productname>PostgreSQL</productname> currently implements only a kind of
+ immediate maintenance, in which materialized views are updated immediately
+ in AFTER triggers when a base table is modified.
+</para>
+
+<para>
+ To create materialized views supporting <acronym>IVM</acronym>, use the
+ <command>CREATE INCREMENTAL MATERIALIZED VIEW</command>, for example:
+<programlisting>
+CREATE <emphasis>INCREMENTAL</emphasis> MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;
+</programlisting>
+ When a materialized view is created with the <literal>INCREMENTAL</literal>
+ keyword, some triggers are automatically created so that the view's contents are
+ immediately updated when its base tables are modified. We call this form
+ of materialized view an Incrementally Maintainable Materialized View
+ (<acronym>IMMV</acronym>).
+<programlisting>
+postgres=# CREATE INCREMENTAL MATERIALIZED VIEW m AS SELECT * FROM t0;
+NOTICE: could not create an index on materialized view "m" automatically
+HINT: Create an index on the materialized view for effcient incremental maintenance.
+SELECT 3
+postgres=# SELECT * FROM m;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+postgres=# INSERT INTO t0 VALUES (4);
+INSERT 0 1
+postgres=# SELECT * FROM m; -- automatically updated
+ i
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+</programlisting>
+</para>
+
+<para>
+ Some <acronym>IMMV</acronym>s have hidden columns which are added
+ automatically when a materialized view is created. Their name starts
+ with <literal>__ivm_</literal> and they contain information required
+ for maintaining the <acronym>IMMV</acronym>. Such columns are not visible
+ when the <acronym>IMMV</acronym> is accessed by <literal>SELECT *</literal>
+ but are visible if the column name is explicitly specified in the target
+ list. We can also see the hidden columns in <literal>\d</literal>
+ meta-commands of <command>psql</command> commands.
+</para>
+
+<para>
+ In general, <acronym>IMMV</acronym>s allow faster updates of materialized
+ views at the price of slower updates to their base tables. Updates of
+ <acronym>IMMV</acronym> is slower because triggers will be invoked and the
+ view is updated in triggers per modification statement.
+</para>
+
+<para>
+ For example, suppose a normal materialized view defined as below:
+
+<programlisting>
+test=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm AS
+ SELECT a.aid, b.bid, a.abalance, b.bbalance
+ FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
+SELECT 10000000
+
+</programlisting>
+
+ Updating a tuple in a base table of this materialized view is rapid but the
+ <command>REFRESH MATERIALIZED VIEW</command> command on this view takes a long time:
+
+<programlisting>
+test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
+UPDATE 1
+Time: 0.990 ms
+
+test=# REFRESH MATERIALIZED VIEW mv_normal ;
+REFRESH MATERIALIZED VIEW
+Time: 33533.952 ms (00:33.534)
+</programlisting>
+</para>
+
+<para>
+ On the other hand, after creating <acronym>IMMV</acronym> with the same view
+ definition as below:
+
+<programlisting>
+test=# CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm AS
+ SELECT a.aid, b.bid, a.abalance, b.bbalance
+ FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
+test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
+NOTICE: created index "mv_ivm_index" on materialized view "mv_ivm"
+</programlisting>
+
+ updating a tuple in a base table takes more than the normal view,
+ but its content is updated automatically and this is faster than the
+ <command>REFRESH MATERIALIZED VIEW</command> command.
+
+<programlisting>
+test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
+UPDATE 1
+Time: 13.068 ms
+</programlisting>
+
+</para>
+
+<para>
+ Appropriate indexes on <acronym>IMMV</acronym>s are necessary for
+ efficient <acronym>IVM</acronym> because it looks for tuples to be
+ updated in <acronym>IMMV</acronym>. If there are no indexes, it
+ will take a long time.
+</para>
+
+<para>
+ Therefore, when <acronym>IMMV</acronym> is defined, a unique index is created on the view
+ automatically if possible. If the view definition query has a GROUP BY clause, a unique
+ index is created on the columns of GROUP BY expressions. Also, if the view has DISTINCT
+ clause, a unique index is created on all columns in the target list. Otherwise, if the
+ view contains all primary key attritubes of its base tables in the target list, a unique
+ index is created on these attritubes. In other cases, no index is created.
+</para>
+
+<para>
+ In the previous example, a unique index "mv_ivm_index" is created on aid and bid
+ columns of materialized view "mv_ivm", and this enables the rapid update of the view.
+ Dropping this index make updating the view take a loger time.
+<programlisting>
+test=# DROP INDEX mv_ivm_index;
+DROP INDEX
+Time: 67.081 ms
+
+test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
+UPDATE 1
+Time: 16386.245 ms (00:16.386)
+</programlisting>
+
+</para>
+
+<para>
+ <acronym>IVM</acronym> is effective when we want to keep a materialized
+ view up-to-date and small fraction of a base table is modified
+ infrequently. Due to the overhead of immediate maintenance, <acronym>IVM</acronym>
+ is not effective when a base table is modified frequently. Also, when a
+ large part of a base table is modified or large data is inserted into a
+ base table, <acronym>IVM</acronym> is not effective and the cost of
+ maintenance can be larger than the <command>REFRESH MATERIALIZED VIEW</command>
+ command. In such situation, we can use <command>REFRESH MATERIALIZED VIEW</command>
+ and specify <literal>WITH NO DATA</literal> to disable immediate
+ maintenance before modifying a base table. After a base table modification,
+ execute the <command>REFRESH MATERIALIZED VIEW</command> (with <literal>WITH DATA</literal>)
+ command to refresh the view data and enable immediate maintenance.
+</para>
+
+</sect2>
+
+<sect2>
+<title>Supported View Definitions and Restrictions</title>
+
+<para>
+ Currently, we can create <acronym>IMMV</acronym>s using inner joins, and some
+ aggregates. However, several restrictions apply to the definition of IMMV.
+</para>
+
+<sect3>
+<title>Joins</title>
+<para>
+ Inner joins including self-join are supported. Outer joins are not supported.
+</para>
+</sect3>
+
+<sect3>
+<title>Aggregates</title>
+<para>
+ Supported aggregate functions are <function>count</function>, <function>sum</function>,
+ <function>avg</function>, <function>min</function>, and <function>max</function>.
+ Currently, only built-in aggregate functions are supported and user defined
+ aggregates cannot be used. When a base table is modified, the new aggregated
+ values are incrementally calculated using the old aggregated values and values
+ of related hidden columns stored in <acronym>IMMV</acronym>.
+</para>
+
+<para>
+ Note that for <function>min</function> or <function>max</function>, the new values
+ could be re-calculated from base tables with regard to the affected groups when a
+ tuple containing the current minimal or maximal values are deleted from a base table.
+ Therefore, it can takes a long time to update an <acronym>IMMV</acronym> containing
+ these functions.
+</para>
+
+<para>
+ Also note that using <function>sum</function> or <function>avg</function> on
+ <type>real</type> (<type>float4</type>) type or <type>double precision</type>
+ (<type>float8</type>) type in <acronym>IMMV</acronym> is unsafe. This is
+ because aggregated values in <acronym>IMMV</acronym> can become different from
+ results calculated from base tables due to the limited precision of these types.
+ To avoid this problem, use the <type>numeric</type> type instead.
+</para>
+
+ <sect4>
+ <title>Restrictions on Aggregates</title>
+ <para>
+ There are the following restrictions:
+ <itemizedlist>
+ <listitem>
+ <para>
+ If we have a <literal>GROUP BY</literal> clause, expressions specified in
+ <literal>GROUP BY</literal> must appear in the target list. This is
+ how tuples to be updated in the <acronym>IMMV</acronym> are identified.
+ These attributes are used as scan keys for searching tuples in the
+ <acronym>IMMV</acronym>, so indexes on them are required for efficient
+ <acronym>IVM</acronym>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>HAVING</literal> clause cannot be used.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect4>
+</sect3>
+
+<sect3>
+<title>Other General Restrictions</title>
+<para>
+ There are other restrictions which generally apply to <acronym>IMMV</acronym>:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Sub-queries cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ CTEs cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Window functions cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <acronym>IMMV</acronym>s must be based on simple base tables. It's not
+ supported to create them on top of views, materialized views, foreign tables, inhe.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ LIMIT and OFFSET clauses cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <acronym>IMMV</acronym>s cannot contain system columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <acronym>IMMV</acronym>s cannot contain non-immutable functions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ UNION/INTERSECT/EXCEPT clauses cannnot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ DISTINCT ON clauses cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ TABLESAMPLE parameter cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ inheritance parent tables cannnot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ VALUES clause cannnot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>GROUPING SETS</literal> and <literal>FILTER</literal> clauses cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ FOR UPDATE/SHARE cannot be used.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ targetlist cannot contain columns whose name start with <literal>__ivm_</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ targetlist cannot contain expressions which contain an aggregate in it.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Logical replication is not supported, that is, even when a base table
+ at a publisher node is modified, <acronym>IMMV</acronym>s at subscriber
+ nodes are not updated.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When the <literal>TRUNCATE</literal> command is executed on a base table,
+ nothing is changed on the <acronym>IMMV</acronym>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+</para>
+</sect3>
+
+</sect2>
+
+<sect2>
+<title><literal>DISTINCT</literal></title>
+
+<para>
+ <productname>PostgreSQL</productname> supports <acronym>IMMV</acronym> with
+ <literal>DISTINCT</literal>. For example, suppose a <acronym>IMMV</acronym>
+ defined with <literal>DISTINCT</literal> on a base table containing duplicate
+ tuples. When tuples are deleted from the base table, a tuple in the view is
+ deleted if and only if the multiplicity of the tuple becomes zero. Moreover,
+ when tuples are inserted into the base table, a tuple is inserted into the
+ view only if the same tuple doesn't already exist in it.
+</para>
+
+<para>
+ Physically, an <acronym>IMMV</acronym> defined with <literal>DISTINCT</literal>
+ contains tuples after eliminating duplicates, and the multiplicity of each tuple
+ is stored in a hidden column named <literal>__ivm_count__</literal>.
+</para>
+</sect2>
+
+<sect2>
+<title>Concurrent Transactions</title>
+<para>
+ Suppose an <acronym>IMMV</acronym> is defined on two base tables and each
+ table was modified in different a concurrent transaction simultaneously.
+ In the transaction which was committed first, <acronym>IMMV</acronym> can
+ be updated considering only the change which happened in this transaction.
+ On the other hand, in order to update the view correctly in the transaction
+ which was committed later, we need to know the changes occurred in
+ both transactions. For this reason, <literal>ExclusiveLock</literal>
+ is held on an <acronym>IMMV</acronym> immediately after a base table is
+ modified in <literal>READ COMMITTED</literal> mode to make sure that
+ the <acronym>IMMV</acronym> is updated in the latter transaction after
+ the former transaction is committed. In <literal>REPEATABLE READ</literal>
+ or <literal>SERIALIZABLE</literal> mode, an error is raised immediately
+ if lock acquisition fails because any changes which occurred in
+ other transactions are not be visible in these modes and
+ <acronym>IMMV</acronym> cannot be updated correctly in such situations.
+ However, as an exception if the view has only one base table,
+ the lock held on thew view is <literal>RowExclusiveLock</literal>.
+</para>
+</sect2>
+
+<sect2>
+<title>Row Level Security</title>
+<para>
+ If some base tables have row level security policy, rows that are not visible
+ to the materialized view's owner are excluded from the result. In addition, such
+ rows are excluded as well when views are incrementally maintained. However, if a
+ new policy is defined or policies are changed after the materialized view was created,
+ the new policy will not be applied to the view contents. To apply the new policy,
+ you need to refresh materialized views.
+</para>
+</sect2>
+
+</sect1>
+
<sect1 id="rules-update">
<title>Rules on <command>INSERT</command>, <command>UPDATE</command>, and <command>DELETE</command></title>
--
2.17.1
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Implementing Incremental View Maintenance
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox