This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new f34ae72416 Maintain Data Status of Materialized Views for Partitioned
Tables.
f34ae72416 is described below
commit f34ae7241633e4672c7a0bfb6d5e9f5be72f8619
Author: Zhang Mingli <[email protected]>
AuthorDate: Fri Aug 23 13:09:40 2024 +0800
Maintain Data Status of Materialized Views for Partitioned Tables.
This commit improves the handling of data status changes for
materialized views associated with partitioned tables.
Writable operations (INSERT, UPDATE, DELETE, COPY, TRUNCATE, CLUSTER) on
a base table lead to changes in the data status of linked materialized
views.
For partitioned tables, changes in child partitions can impact parent and
ancestor tables, necessitating updates to the relevant materialized views.
Example:
P0
/ \
P1 P2
/ \
P2_1 P2_2
For a root partitioned table P0 with child partitions P1 and P2
(each having sub-partitions), inserting rows into P1_1 updates the
data status of mv0 (based on P0) and mv1 (based on P1). Conversely,
inserting into P2_1 only affects mv2_1 (based on P2_1) and mv0, leaving
mv1 unchanged.
Propagation Direction: Data status changes propagate both UP and DOWN the
partition tree, except for specific DDL operations:
Create Table XXX Partition Of:
Indicates an insert operation on the parent table.
Drop Table:
Indicates a delete operation on the parent table.
Alter Table ATTACH/DETACH :
Attaching implies an insert, while detaching implies a delete.
Handling TRUNCATE and CLUSTER: A TRUNCATE operation on a parent table is
treated as a DELETE on its child tables, affecting both the parent and
its descendants. Similarly, a CLUSTER operation will transform the
data status in both UP and DOWN directions. However, CLUSTER has a
recognized status to indicate that the changes have been applied to
the pages of the tables.
The COPY command indicates that data has been inserted into the table
and its ancestor tables.
The VACUUM command indicates that data has been recognized on the table
and its ancestor tables.
Authored-by: Zhang Mingli [email protected]
---
src/backend/catalog/gp_matview_aux.c | 64 ++++++---
src/backend/catalog/heap.c | 12 +-
src/backend/commands/cluster.c | 8 +-
src/backend/commands/copy.c | 4 +-
src/backend/commands/tablecmds.c | 58 +++++++-
src/backend/commands/vacuum.c | 4 +-
src/backend/executor/execMain.c | 22 +--
src/include/catalog/gp_matview_aux.h | 6 +-
src/test/regress/expected/matview_data.out | 221 +++++++++++++++++++++++++++++
src/test/regress/sql/matview_data.sql | 92 ++++++++++++
10 files changed, 460 insertions(+), 31 deletions(-)
diff --git a/src/backend/catalog/gp_matview_aux.c
b/src/backend/catalog/gp_matview_aux.c
index 7fd036f9af..d700e09cb2 100644
--- a/src/backend/catalog/gp_matview_aux.c
+++ b/src/backend/catalog/gp_matview_aux.c
@@ -17,9 +17,11 @@
#include "access/htup_details.h"
#include "access/table.h"
#include "access/genam.h"
+#include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/gp_matview_aux.h"
#include "catalog/gp_matview_tables.h"
+#include "catalog/partition.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_type.h"
#include "catalog/indexing.h"
@@ -103,6 +105,7 @@ GetViewBaseRelids(const Query *viewQuery, bool *has_foreign)
* outside CBDB, let them decide with aqumv_allow_foreign_table.
*/
if (relkind != RELKIND_RELATION &&
+ relkind != RELKIND_PARTITIONED_TABLE &&
relkind != RELKIND_FOREIGN_TABLE)
return NIL;
@@ -111,10 +114,8 @@ GetViewBaseRelids(const Query *viewQuery, bool
*has_foreign)
/*
* inherit tables are not supported.
- * FIXME: left a door for partition table which will be supported soon.
*/
- bool can_be_partition = (get_rel_relkind(rte->relid) ==
RELKIND_PARTITIONED_TABLE) ||
-
get_rel_relispartition(rte->relid);
+ bool can_be_partition = (relkind == RELKIND_PARTITIONED_TABLE) ||
get_rel_relispartition(rte->relid);
if (!can_be_partition &&
(has_superclass(rte->relid) || has_subclass(rte->relid)))
@@ -186,6 +187,8 @@ InsertMatviewAuxEntry(Oid mvoid, const Query *viewQuery,
bool skipdata)
table_close(mvauxRel, RowExclusiveLock);
+ CommandCounterIncrement();
+
return;
}
@@ -249,6 +252,8 @@ RemoveMatviewAuxEntry(Oid mvoid)
table_close(mvauxRel, RowExclusiveLock);
+ CommandCounterIncrement();
+
return;
}
@@ -285,33 +290,57 @@ RemoveMatviewTablesEntries(Oid mvoid)
* the underlying table's data is changed.
*/
void
-SetRelativeMatviewAuxStatus(Oid relid, char status)
+SetRelativeMatviewAuxStatus(Oid relid, char status, char direction)
{
Relation mvauxRel;
Relation mtRel;
HeapTuple tup;
ScanKeyData key;
SysScanDesc desc;
+ List *base_oids;
+ ListCell *cell;
mvauxRel = table_open(GpMatviewAuxId, RowExclusiveLock);
/* Find all mvoids have relid */
mtRel = table_open(GpMatviewTablesId, AccessShareLock);
- ScanKeyInit(&key,
- Anum_gp_matview_tables_relid,
- BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(relid));
- desc = systable_beginscan(mtRel,
-
GpMatviewTablesRelIndexId,
- true,
- NULL, 1,
&key);
- while (HeapTupleIsValid(tup = systable_getnext(desc)))
+
+ /*
+ * For partitioned table, transfer status to children.
+ * For patition, transfer status to all ancestors.
+ */
+ if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE &&
+ (MV_DATA_STATUS_TRANSFER_DIRECTION_ALL == direction ||
+ MV_DATA_STATUS_TRANSFER_DIRECTION_DOWN == direction))
+ base_oids = find_all_inheritors(relid, NoLock, NULL);
+ else
+ base_oids = list_make1_oid(relid);
+
+ if (get_rel_relispartition(relid) &&
+ (MV_DATA_STATUS_TRANSFER_DIRECTION_ALL == direction ||
+ MV_DATA_STATUS_TRANSFER_DIRECTION_UP == direction))
+ base_oids = list_concat(base_oids,
get_partition_ancestors(relid));
+
+ foreach(cell, base_oids)
{
- Form_gp_matview_tables mt = (Form_gp_matview_tables)
GETSTRUCT(tup);
- /* Update mv aux status. */
- SetMatviewAuxStatus_guts(mt->mvoid, status);
+ Oid base_oid = lfirst_oid(cell);
+ ScanKeyInit(&key,
+ Anum_gp_matview_tables_relid,
+ BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(base_oid));
+ desc = systable_beginscan(mtRel,
+
GpMatviewTablesRelIndexId,
+ true,
+ NULL,
1, &key);
+ while (HeapTupleIsValid(tup = systable_getnext(desc)))
+ {
+ Form_gp_matview_tables mt = (Form_gp_matview_tables)
GETSTRUCT(tup);
+ /* Update mv aux status. */
+ SetMatviewAuxStatus_guts(mt->mvoid, status);
+ }
+
+ systable_endscan(desc);
}
- systable_endscan(desc);
table_close(mtRel, AccessShareLock);
table_close(mvauxRel, RowExclusiveLock);
}
@@ -435,6 +464,9 @@ SetMatviewAuxStatus_guts(Oid mvoid, char status)
CatalogTupleUpdate(mvauxRel, &newtuple->t_self, newtuple);
heap_freetuple(newtuple);
table_close(mvauxRel, NoLock);
+
+ /* For partitioned table, we may insert into same rel multiple times. */
+ CommandCounterIncrement();
}
/*
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index d877752ca2..9c7a9fb923 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2423,6 +2423,14 @@ heap_drop_with_catalog(Oid relid)
parentOid = get_partition_parent(relid, true);
LockRelationOid(parentOid, AccessExclusiveLock);
+ /*
+ * Now we could update view status of parent.
+ * Drop a partiton means delete data from parent.
+ */
+ SetRelativeMatviewAuxStatus(parentOid,
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_UP);
+
/*
* If this is not the default partition, dropping it will
change the
* default partition's partition constraint, so we must lock it.
@@ -3992,7 +4000,9 @@ heap_truncate_one_rel(Relation rel)
/* update view info */
if (IS_QD_OR_SINGLENODE())
- SetRelativeMatviewAuxStatus(RelationGetRelid(rel),
MV_DATA_STATUS_EXPIRED);
+ SetRelativeMatviewAuxStatus(RelationGetRelid(rel),
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
/* If there is a toast table, truncate that too */
toastrelid = rel->rd_rel->reltoastrelid;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 320d4ae57d..0517912ffd 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -232,7 +232,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool
isTopLevel)
* to distint the pages instead, since latest relative
materialized
* view REFRESH.
*/
- SetRelativeMatviewAuxStatus(tableOid,
MV_DATA_STATUS_UP_REORGANIZED);
+ SetRelativeMatviewAuxStatus(tableOid,
+
MV_DATA_STATUS_UP_REORGANIZED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
}
}
@@ -302,7 +304,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool
isTopLevel)
}
/* See comments above. */
if (IS_QD_OR_SINGLENODE())
- SetRelativeMatviewAuxStatus(rvtc->tableOid,
MV_DATA_STATUS_UP_REORGANIZED);
+ SetRelativeMatviewAuxStatus(rvtc->tableOid,
+
MV_DATA_STATUS_UP_REORGANIZED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
PopActiveSnapshot();
CommitTransactionCommand();
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 61131f8dfc..c0da428e53 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -453,7 +453,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
*/
if (IS_QD_OR_SINGLENODE() && *processed > 0)
{
- SetRelativeMatviewAuxStatus(relid,
MV_DATA_STATUS_EXPIRED_INSERT_ONLY);
+ SetRelativeMatviewAuxStatus(relid,
+
MV_DATA_STATUS_EXPIRED_INSERT_ONLY,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
}
}
PG_CATCH();
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 850c08cb08..3af452d491 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1437,6 +1437,24 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid
ownerId,
/* Store inheritance information for new rel. */
StoreCatalogInheritance(relationId, inheritOids, stmt->partbound !=
NULL);
+ if (IS_QD_OR_SINGLENODE() && stmt->partbound != NULL)
+ {
+ /*
+ * Update parent relation's view status.
+ * We just want to tranasfer status up to the top.
+ */
+ Assert(list_length(inheritOids) == 1);
+ Oid parent = linitial_oid(inheritOids);
+ /*
+ * FIXME: A new partition means data insertion for parent.
+ * But DeltaScan can't handle it yet, so mark it as expired
+ * to disable Append Agg for now.
+ */
+ SetRelativeMatviewAuxStatus(parent,
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_UP);
+ }
+
/*
* Process the partitioning specification (if any) and store the
partition
* key information into the catalog.
@@ -2572,7 +2590,9 @@ ExecuteTruncateGuts(List *explicit_rels,
/* update view info */
if (IS_QD_OR_SINGLENODE())
-
SetRelativeMatviewAuxStatus(RelationGetRelid(rel), MV_DATA_STATUS_EXPIRED);
+
SetRelativeMatviewAuxStatus(RelationGetRelid(rel),
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
heap_relid = RelationGetRelid(rel);
@@ -21404,6 +21424,20 @@ ATExecAttachPartition(List **wqueue, Relation rel,
PartitionCmd *cmd,
/* OK to create inheritance. Rest of the checks performed there */
CreateInheritance(attachrel, rel);
+ /*
+ * Update status of parent relative views.
+ * FIXME: Attach a partition means insert data into parent.
+ * But DeltaScan can't handle it yet, so mark it as expired
+ * to disable Append Agg for now.
+ */
+ if (IS_QD_OR_SINGLENODE())
+ {
+ /* to see its parent */
+ CommandCounterIncrement();
+ SetRelativeMatviewAuxStatus(RelationGetRelid(rel),
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_UP);
+ }
/* Update the pg_class entry. */
StorePartitionBound(attachrel, rel, cmd->bound);
@@ -21932,7 +21966,19 @@ ATExecDetachPartition(List **wqueue, AlteredTableInfo
*tab, Relation rel,
* non-concurrent mode) or just set the inhdetachpending flag.
*/
if (!concurrent)
+ {
+ /*
+ * Update status of parent relative views.
+ * We must do this before RemoveInheritance.
+ * Detach a partition means delete data from parent.
+ */
+ if (IS_QD_OR_SINGLENODE())
+ SetRelativeMatviewAuxStatus(RelationGetRelid(rel),
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_UP);
+
RemoveInheritance(partRel, rel, false);
+ }
else
MarkInheritDetached(partRel, rel);
@@ -22068,6 +22114,16 @@ DetachPartitionFinalize(Relation rel, Relation
partRel, bool concurrent,
if (concurrent)
{
+ /*
+ * Update status of parent relative views.
+ * We must do this before RemoveInheritance.
+ * Detach a partition means delete data from parent.
+ */
+ if (IS_QD_OR_SINGLENODE())
+ SetRelativeMatviewAuxStatus(RelationGetRelid(rel),
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_UP);
+
/*
* We can remove the pg_inherits row now. (In the
non-concurrent case,
* this was already done).
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 78a7ac60c3..79337453e1 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -2688,7 +2688,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams
*params,
* FIXME: for auto vacuum process on segments, it's in utility
mode,
* we can't handle it yet. But it's not a problem for
SERVERLESS.
*/
- SetRelativeMatviewAuxStatus(relid,
MV_DATA_STATUS_UP_REORGANIZED);
+ SetRelativeMatviewAuxStatus(relid,
+
MV_DATA_STATUS_UP_REORGANIZED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
}
/* Roll back any GUC changes executed by index functions */
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 430ed0ab27..40bb773761 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1065,9 +1065,13 @@ standard_ExecutorRun(QueryDesc *queryDesc,
List *rtable =
queryDesc->plannedstmt->rtable;
int length = list_length(rtable);
ListCell *lc;
- foreach(lc, queryDesc->plannedstmt->resultRelations)
+ List *unique_result_relations =
list_concat_unique_int(NIL, queryDesc->plannedstmt->resultRelations);
+
+ foreach(lc, unique_result_relations)
{
+
int varno = lfirst_int(lc);
+ RangeTblEntry *rte = rt_fetch(varno, rtable);
/* Avoid crash in case we don't find a rte. */
if (varno > length + 1)
@@ -1075,24 +1079,26 @@ standard_ExecutorRun(QueryDesc *queryDesc,
ereport(WARNING, (errmsg("could not
find rte of varno: %u ", varno)));
continue;
}
-
- RangeTblEntry *rte = rt_fetch(varno, rtable);
+
switch (operation)
{
case CMD_INSERT:
-
SetRelativeMatviewAuxStatus(rte->relid, MV_DATA_STATUS_EXPIRED_INSERT_ONLY);
+
SetRelativeMatviewAuxStatus(rte->relid,
+
MV_DATA_STATUS_EXPIRED_INSERT_ONLY,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
break;
case CMD_UPDATE:
case CMD_DELETE:
-
SetRelativeMatviewAuxStatus(rte->relid, MV_DATA_STATUS_EXPIRED);
+
SetRelativeMatviewAuxStatus(rte->relid,
+
MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
break;
default:
- {
/* If there were writable CTE,
just mark it as expired. */
if
(queryDesc->plannedstmt->hasModifyingCTE)
-
SetRelativeMatviewAuxStatus(rte->relid, MV_DATA_STATUS_EXPIRED);
+
SetRelativeMatviewAuxStatus(rte->relid, MV_DATA_STATUS_EXPIRED,
+
MV_DATA_STATUS_TRANSFER_DIRECTION_ALL);
break;
- }
}
}
}
diff --git a/src/include/catalog/gp_matview_aux.h
b/src/include/catalog/gp_matview_aux.h
index 3dca7a526c..0d1c03003d 100644
--- a/src/include/catalog/gp_matview_aux.h
+++ b/src/include/catalog/gp_matview_aux.h
@@ -54,13 +54,17 @@ DECLARE_INDEX(gp_matview_aux_datastatus_index, 7149, on
gp_matview_aux using btr
#define MV_DATA_STATUS_EXPIRED
'e' /* data is expired */
#define MV_DATA_STATUS_EXPIRED_INSERT_ONLY 'i'
/* expired but has only INSERT operation since latest REFRESH */
+#define MV_DATA_STATUS_TRANSFER_DIRECTION_UP 'u' /* set
status recursivly up to the top. */
+#define MV_DATA_STATUS_TRANSFER_DIRECTION_DOWN 'd' /* set
status recursivly down to the leaf. */
+#define MV_DATA_STATUS_TRANSFER_DIRECTION_ALL 'a' /* set
status recursivly up and down */
+
extern void InsertMatviewAuxEntry(Oid mvoid, const Query *viewQuery, bool
skipdata);
extern void RemoveMatviewAuxEntry(Oid mvoid);
extern List* GetViewBaseRelids(const Query *viewQuery, bool *has_foreign);
-extern void SetRelativeMatviewAuxStatus(Oid relid, char status);
+extern void SetRelativeMatviewAuxStatus(Oid relid, char status, char
direction);
extern void SetMatviewAuxStatus(Oid mvoid, char status);
diff --git a/src/test/regress/expected/matview_data.out
b/src/test/regress/expected/matview_data.out
index fe425e9595..f851e8c15d 100644
--- a/src/test/regress/expected/matview_data.out
+++ b/src/test/regress/expected/matview_data.out
@@ -432,10 +432,231 @@ select mvname, datastatus from gp_matview_aux where
mvname = 'mv_t_cte_issue_582
(1 row)
abort;
+-- test partitioned tables
+create table par(a int, b int, c int) partition by range(b)
+ subpartition by range(c) subpartition template (start (1) end (3) every
(1))
+ (start(1) end(3) every(1));
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into par values(1, 1, 1), (1, 1, 2), (2, 2, 1), (2, 2, 2);
+create materialized view mv_par as select * from par;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create materialized view mv_par1 as select * from par_1_prt_1;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create materialized view mv_par1_1 as select * from par_1_prt_1_2_prt_1;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create materialized view mv_par1_2 as select * from par_1_prt_1_2_prt_2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create materialized view mv_par2 as select * from par_1_prt_2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create materialized view mv_par2_2 as select * from par_1_prt_2_2_prt_1;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par | u
+ mv_par1 | u
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2 | u
+ mv_par2_2 | u
+(6 rows)
+
+insert into par_1_prt_1 values (1, 1, 1);
+-- mv_par1* shoud be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par2 | u
+ mv_par2_2 | u
+ mv_par1 | i
+ mv_par1_1 | i
+ mv_par1_2 | i
+ mv_par | i
+(6 rows)
+
+insert into par values (1, 2, 2);
+-- mv_par* should be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1 | i
+ mv_par1_1 | i
+ mv_par1_2 | i
+ mv_par | i
+ mv_par2 | i
+ mv_par2_2 | i
+(6 rows)
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+begin;
+insert into par_1_prt_2_2_prt_1 values (1, 2, 1);
+-- mv_par1* should not be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1 | u
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2_2 | i
+ mv_par2 | i
+ mv_par | i
+(6 rows)
+
+abort;
+begin;
+truncate par_1_prt_2;
+-- mv_par1* should not be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1 | u
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2_2 | e
+ mv_par2 | e
+ mv_par | e
+(6 rows)
+
+abort;
+truncate par_1_prt_2;
+-- mv_par1* should not be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1 | u
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2_2 | e
+ mv_par2 | e
+ mv_par | e
+(6 rows)
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+vacuum full par_1_prt_1_2_prt_1;
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1_2 | u
+ mv_par2 | u
+ mv_par2_2 | u
+ mv_par1_1 | r
+ mv_par1 | r
+ mv_par | r
+(6 rows)
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+vacuum full par;
+-- all should be updated.
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par2 | r
+ mv_par | r
+ mv_par2_2 | r
+ mv_par1_2 | r
+ mv_par1 | r
+ mv_par1_1 | r
+(6 rows)
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+begin;
+create table par_1_prt_1_2_prt_3 partition of par_1_prt_1 for values from
(3) to (4);
+NOTICE: table has parent, setting distribution columns to match parent table
+-- update status when partition of
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2 | u
+ mv_par2_2 | u
+ mv_par1 | e
+ mv_par | e
+(6 rows)
+
+abort;
+begin;
+drop table par_1_prt_1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to materialized view mv_par1_1
+drop cascades to materialized view mv_par1_2
+drop cascades to materialized view mv_par1
+-- update status when drop table
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par2 | u
+ mv_par2_2 | u
+ mv_par | e
+(3 rows)
+
+abort;
+begin;
+alter table par_1_prt_1 detach partition par_1_prt_1_2_prt_1;
+-- update status when detach
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2 | u
+ mv_par2_2 | u
+ mv_par1 | e
+ mv_par | e
+(6 rows)
+
+abort;
+begin;
+create table new_par(a int, b int, c int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+-- update status when attach
+alter table par_1_prt_1 attach partition new_par for values from (4) to (5);
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+ mvname | datastatus
+-----------+------------
+ mv_par1_1 | u
+ mv_par1_2 | u
+ mv_par2 | u
+ mv_par2_2 | u
+ mv_par1 | e
+ mv_par | e
+(6 rows)
+
+abort;
+--start_ignore
drop schema matview_data_schema cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table t2
drop cascades to table t3
drop cascades to materialized view mv3
+--end_ignore
reset enable_answer_query_using_materialized_views;
reset optimizer;
diff --git a/src/test/regress/sql/matview_data.sql
b/src/test/regress/sql/matview_data.sql
index 2c3552eea8..d76f96583f 100644
--- a/src/test/regress/sql/matview_data.sql
+++ b/src/test/regress/sql/matview_data.sql
@@ -158,6 +158,98 @@ with mod1 as (insert into t_cte_issue_582 values(1, 1)
returning *) select * fro
select mvname, datastatus from gp_matview_aux where mvname =
'mv_t_cte_issue_582';
abort;
+-- test partitioned tables
+create table par(a int, b int, c int) partition by range(b)
+ subpartition by range(c) subpartition template (start (1) end (3) every
(1))
+ (start(1) end(3) every(1));
+insert into par values(1, 1, 1), (1, 1, 2), (2, 2, 1), (2, 2, 2);
+create materialized view mv_par as select * from par;
+create materialized view mv_par1 as select * from par_1_prt_1;
+create materialized view mv_par1_1 as select * from par_1_prt_1_2_prt_1;
+create materialized view mv_par1_2 as select * from par_1_prt_1_2_prt_2;
+create materialized view mv_par2 as select * from par_1_prt_2;
+create materialized view mv_par2_2 as select * from par_1_prt_2_2_prt_1;
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+insert into par_1_prt_1 values (1, 1, 1);
+-- mv_par1* shoud be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+insert into par values (1, 2, 2);
+-- mv_par* should be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+begin;
+insert into par_1_prt_2_2_prt_1 values (1, 2, 1);
+-- mv_par1* should not be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+abort;
+
+begin;
+truncate par_1_prt_2;
+-- mv_par1* should not be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+abort;
+truncate par_1_prt_2;
+-- mv_par1* should not be updated
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+vacuum full par_1_prt_1_2_prt_1;
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+vacuum full par;
+-- all should be updated.
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+
+refresh materialized view mv_par;
+refresh materialized view mv_par1;
+refresh materialized view mv_par1_1;
+refresh materialized view mv_par1_2;
+refresh materialized view mv_par2;
+refresh materialized view mv_par2_2;
+begin;
+create table par_1_prt_1_2_prt_3 partition of par_1_prt_1 for values from
(3) to (4);
+-- update status when partition of
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+abort;
+
+begin;
+drop table par_1_prt_1 cascade;
+-- update status when drop table
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+abort;
+
+begin;
+alter table par_1_prt_1 detach partition par_1_prt_1_2_prt_1;
+-- update status when detach
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+abort;
+
+begin;
+create table new_par(a int, b int, c int);
+-- update status when attach
+alter table par_1_prt_1 attach partition new_par for values from (4) to (5);
+select mvname, datastatus from gp_matview_aux where mvname like 'mv_par%';
+abort;
+
+--start_ignore
drop schema matview_data_schema cascade;
+--end_ignore
reset enable_answer_query_using_materialized_views;
reset optimizer;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
