001_implement_mat_view_where.patch
text/x-patch
Filename: 001_implement_mat_view_where.patch
Type: text/x-patch
Part: 1
From 599d6a8f3d48aed56f960b1e4beba37aeeba0a71 Mon Sep 17 00:00:00 2001
From: Adam Brusselback <adambrusselback@gmail.com>
Date: Mon, 8 Dec 2025 15:00:15 -0500
Subject: [PATCH] Add support for partial matview refresh using `REFRESH
MATERIALIZED VIEW ... WHERE ...`
---
.../sgml/ref/refresh_materialized_view.sgml | 44 +-
src/backend/commands/createas.c | 4 +-
src/backend/commands/matview.c | 821 ++++++++++++++++--
src/backend/executor/execMain.c | 3 +-
src/backend/parser/gram.y | 14 +-
src/backend/tcop/utility.c | 2 +-
src/include/commands/matview.h | 6 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/matview_where.out | 346 ++++++++
src/test/regress/parallel_schedule | 5 +
src/test/regress/sql/matview_where.sql | 305 +++++++
11 files changed, 1477 insertions(+), 74 deletions(-)
create mode 100644 src/test/regress/expected/matview_where.out
create mode 100644 src/test/regress/sql/matview_where.sql
diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml
index 8ed43ade803..ca812ddcdf8 100644
--- a/doc/src/sgml/ref/refresh_materialized_view.sgml
+++ b/doc/src/sgml/ref/refresh_materialized_view.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
- [ WITH [ NO ] DATA ]
+ [ WITH [ NO ] DATA ] [ WHERE <replaceable class="parameter">condition</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -44,6 +44,15 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
<literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
be specified together.
</para>
+ <para>
+ If a <literal>WHERE</literal> clause is specified, only the rows matching
+ the <replaceable class="parameter">condition</replaceable> are updated.
+ Rows in the materialized view that match the condition but are no longer
+ present in the underlying base tables (or no longer match the query definition)
+ are deleted. New rows from the base tables that match the condition are inserted.
+ Rows in the materialized view that do not match the condition are left unchanged.
+ The <literal>WHERE</literal> clause cannot be used with <literal>WITH NO DATA</literal>.
+ </para>
</refsect1>
<refsect1>
@@ -87,6 +96,29 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>WHERE</literal> <replaceable class="parameter">condition</replaceable></term>
+ <listitem>
+ <para>
+ A <literal>WHERE</literal> clause specifying a condition that determines
+ which rows to refresh. The condition applies to both the existing data
+ in the materialized view and the new data generated by the view's defining query.
+ </para>
+ <para>
+ When a <literal>WHERE</literal> clause is used without
+ <literal>CONCURRENTLY</literal>, the operation requires a
+ <literal>ROW EXCLUSIVE</literal> lock, which allows concurrent reads on the
+ materialized view but blocks other modification commands. This is a lower
+ lock level than the <literal>ACCESS EXCLUSIVE</literal> lock required by a
+ full refresh.
+ </para>
+ <para>
+ The <literal>WHERE</literal> clause cannot contain volatile functions or
+ aggregates. The materialized view must be already populated to use this option.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</refsect1>
@@ -125,7 +157,17 @@ REFRESH MATERIALIZED VIEW order_summary;
state:
<programlisting>
REFRESH MATERIALIZED VIEW annual_statistics_basis WITH NO DATA;
+</programlisting>
+ </para>
+
+ <para>
+ This command will update only the rows in the materialized view
+ <literal>order_summary</literal> where the <literal>order_date</literal>
+ is in the year 2024:
+<programlisting>
+REFRESH MATERIALIZED VIEW order_summary WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
</programlisting></para>
+
</refsect1>
<refsect1>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 1ccc2e55c64..ce40b163d6f 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -293,8 +293,8 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
* reduces the chance that a subsequent refresh will fail.
*/
if (do_refresh)
- RefreshMatViewByOid(address.objectId, true, false, false,
- pstate->p_sourcetext, qc);
+ RefreshMatViewByOid(address.objectId, true, false, false, NULL,
+ pstate->p_sourcetext, params, qc);
}
else
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index a5c579ce112..821269706e5 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -32,12 +32,20 @@
#include "executor/spi.h"
#include "miscadmin.h"
#include "pgstat.h"
+#include "optimizer/optimizer.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_relation.h"
#include "rewrite/rewriteHandler.h"
#include "storage/lmgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/hsearch.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
@@ -53,6 +61,28 @@ typedef struct
BulkInsertState bistate; /* bulk insert state */
} DR_transientrel;
+/*
+ * Session-level cache for Partial Refresh plans.
+ * We cache the prepared SPI plans for the DELETE and INSERT/UPSERT steps
+ * to avoid expensive decompilation (pg_get_viewdef) and parsing on every execution.
+ */
+typedef struct MatViewPartialRefreshCache
+{
+ Oid matviewOid; /* Hash Key */
+
+ /* Validation fields */
+ Oid uniqueIndexOid; /* The unique index used for conflict
+ * resolution */
+ char *whereClauseStr; /* The WHERE clause string used to build the
+ * plans */
+
+ /* The cached plans */
+ SPIPlanPtr deletePlan;
+ SPIPlanPtr upsertPlan;
+} MatViewPartialRefreshCache;
+
+static HTAB *MatViewRefreshCache = NULL;
+
static int matview_maintenance_depth = 0;
static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
@@ -62,11 +92,19 @@ static void transientrel_destroy(DestReceiver *self);
static uint64 refresh_matview_datafill(DestReceiver *dest, Query *query,
const char *queryString, bool is_create);
static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
- int save_sec_context);
+ int save_sec_context, char *whereClauseStr,
+ ParamListInfo params);
+static void refresh_by_direct_modification(Oid matviewOid, Oid relowner,
+ int save_sec_context, char *whereClauseStr,
+ ParamListInfo params);
static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence);
static bool is_usable_unique_index(Relation indexRel);
static void OpenMatViewIncrementalMaintenance(void);
static void CloseMatViewIncrementalMaintenance(void);
+static int matview_execute_spi(const char *command, ParamListInfo params, bool read_only);
+static int matview_execute_spi_plan(SPIPlanPtr plan, ParamListInfo params, bool read_only);
+static char *get_matview_view_query(Oid matviewOid);
+static void InitMatViewCache(void);
/*
* SetMatViewPopulatedState
@@ -79,9 +117,17 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
{
Relation pgrel;
HeapTuple tuple;
+ Form_pg_class classForm;
Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
+ /*
+ * If the state matches, do nothing. This prevents cache invalidation
+ * storms when doing frequent partial refreshes via triggers.
+ */
+ if (relation->rd_rel->relispopulated == newstate)
+ return;
+
/*
* Update relation's pg_class entry. Crucial side-effect: other backends
* (and this one too!) are sent SI message to make them rebuild relcache
@@ -94,9 +140,13 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
elog(ERROR, "cache lookup failed for relation %u",
RelationGetRelid(relation));
- ((Form_pg_class) GETSTRUCT(tuple))->relispopulated = newstate;
+ classForm = (Form_pg_class) GETSTRUCT(tuple);
- CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
+ if (classForm->relispopulated != newstate)
+ {
+ classForm->relispopulated = newstate;
+ CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
+ }
heap_freetuple(tuple);
table_close(pgrel, RowExclusiveLock);
@@ -108,51 +158,226 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
CommandCounterIncrement();
}
+/*
+ * Hook to allow parameters (e.g. $1) in the WHERE clause.
+ */
+static Node *
+refresh_paramref_hook(ParseState *pstate, ParamRef *pref)
+{
+ ParamListInfo params = (ParamListInfo) pstate->p_ref_hook_state;
+ Param *param;
+
+ param = makeNode(Param);
+ param->paramkind = PARAM_EXTERN;
+ param->paramid = pref->number;
+ param->paramtype = UNKNOWNOID;
+ param->paramtypmod = -1;
+ param->paramcollid = InvalidOid;
+ param->location = pref->location;
+
+ if (params && pref->number > 0 && pref->number <= params->numParams)
+ {
+ Oid ptype = params->params[pref->number - 1].ptype;
+
+ if (OidIsValid(ptype))
+ param->paramtype = ptype;
+ }
+
+ return (Node *) param;
+}
+
+/*
+ * Transform the WHERE clause for REFRESH MATERIALIZED VIEW.
+ */
+static Node *
+transformRefreshWhereClause(Oid relid, Node *whereClause, ParamListInfo params)
+{
+ ParseState *pstate = make_parsestate(NULL);
+ Relation rel = table_open(relid, NoLock);
+ ParseNamespaceItem *nsitem;
+ Node *result;
+
+ pstate->p_paramref_hook = refresh_paramref_hook;
+ pstate->p_ref_hook_state = (void *) params;
+
+ nsitem = addRangeTableEntryForRelation(pstate, rel, AccessShareLock, NULL, false, true);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+
+ result = transformExpr(pstate, whereClause, EXPR_KIND_WHERE);
+ result = coerce_to_boolean(pstate, result, "WHERE");
+
+ if (contain_volatile_functions(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WHERE clause in REFRESH MATERIALIZED VIEW cannot contain volatile functions")));
+
+ if (pstate->p_hasAggs)
+ ereport(ERROR,
+ (errcode(ERRCODE_GROUPING_ERROR),
+ errmsg("WHERE clause in REFRESH MATERIALIZED VIEW cannot contain aggregates")));
+
+ table_close(rel, NoLock);
+ free_parsestate(pstate);
+
+ return result;
+}
+
+static char *
+deparseRefreshWhereClause(Oid relid, Node *whereClause)
+{
+ return TextDatumGetCString(DirectFunctionCall2(pg_get_expr,
+ CStringGetTextDatum(nodeToString(whereClause)),
+ ObjectIdGetDatum(relid)));
+}
+
+/*
+ * Helper to execute SPI commands with optional parameters.
+ */
+static int
+matview_execute_spi(const char *command, ParamListInfo params, bool read_only)
+{
+ if (params && params->numParams > 0)
+ {
+ Oid *argtypes;
+ Datum *argvalues;
+ char *nulls;
+ int i;
+ int res;
+
+ argtypes = (Oid *) palloc(params->numParams * sizeof(Oid));
+ argvalues = (Datum *) palloc(params->numParams * sizeof(Datum));
+ nulls = (char *) palloc(params->numParams * sizeof(char));
+
+ for (i = 0; i < params->numParams; i++)
+ {
+ ParamExternData *prm = ¶ms->params[i];
+
+ argtypes[i] = prm->ptype;
+ argvalues[i] = prm->value;
+ nulls[i] = prm->isnull ? 'n' : ' ';
+ }
+
+ res = SPI_execute_with_args(command, params->numParams, argtypes,
+ argvalues, nulls, read_only, 0);
+
+ pfree(argtypes);
+ pfree(argvalues);
+ pfree(nulls);
+
+ return res;
+ }
+ else
+ {
+ return SPI_exec(command, 0);
+ }
+}
+
+/*
+ * Helper to execute Prepared SPI Plans with optional parameters.
+ */
+static int
+matview_execute_spi_plan(SPIPlanPtr plan, ParamListInfo params, bool read_only)
+{
+ if (params && params->numParams > 0)
+ {
+ Datum *argvalues;
+ char *nulls;
+ int i;
+ int res;
+
+ argvalues = (Datum *) palloc(params->numParams * sizeof(Datum));
+ nulls = (char *) palloc(params->numParams * sizeof(char));
+
+ for (i = 0; i < params->numParams; i++)
+ {
+ ParamExternData *prm = ¶ms->params[i];
+
+ argvalues[i] = prm->value;
+ nulls[i] = prm->isnull ? 'n' : ' ';
+ }
+
+ res = SPI_execute_plan(plan, argvalues, nulls, read_only, 0);
+
+ pfree(argvalues);
+ pfree(nulls);
+
+ return res;
+ }
+ else
+ {
+ return SPI_execute_plan(plan, NULL, NULL, read_only, 0);
+ }
+}
+
/*
* ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
*
- * If WITH NO DATA was specified, this is effectively like a TRUNCATE;
- * otherwise it is like a TRUNCATE followed by an INSERT using the SELECT
- * statement associated with the materialized view. The statement node's
- * skipData field shows whether the clause was used.
+ * This is the entry point for REFRESH MATERIALIZED VIEW. It handles:
+ *
+ * - WITH NO DATA: effectively like a TRUNCATE.
+ * - CONCURRENTLY: diff-based refresh allowing concurrent reads.
+ * - WHERE clause: partial refresh of a subset of rows.
+ * - Default: full rebuild via heap swap.
+ *
+ * The statement node's skipData field shows whether WITH NO DATA was used.
*/
ObjectAddress
ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
- QueryCompletion *qc)
+ ParamListInfo params, QueryCompletion *qc)
{
Oid matviewOid;
LOCKMODE lockmode;
- /* Determine strength of lock needed. */
- lockmode = stmt->concurrent ? ExclusiveLock : AccessExclusiveLock;
-
/*
- * Get a lock until end of transaction.
+ * Determine strength of lock needed.
+ *
+ * Concurrent Refresh: ExclusiveLock. Partial Non-Concurrent (WHERE ...):
+ * RowExclusiveLock. Full Non-Concurrent: AccessExclusiveLock.
*/
+ if (stmt->concurrent)
+ lockmode = ExclusiveLock;
+ else if (stmt->whereClause)
+ lockmode = RowExclusiveLock;
+ else
+ lockmode = AccessExclusiveLock;
+
matviewOid = RangeVarGetRelidExtended(stmt->relation,
lockmode, 0,
RangeVarCallbackMaintainsTable,
NULL);
return RefreshMatViewByOid(matviewOid, false, stmt->skipData,
- stmt->concurrent, queryString, qc);
+ stmt->concurrent, stmt->whereClause,
+ queryString, params, qc);
}
/*
* RefreshMatViewByOid -- refresh materialized view by OID
*
- * This refreshes the materialized view by creating a new table and swapping
- * the relfilenumbers of the new table and the old materialized view, so the OID
- * of the original materialized view is preserved. Thus we do not lose GRANT
- * nor references to this materialized view.
+ * This refreshes a materialized view using one of three strategies:
+ *
+ * 1. Partial non-concurrent (WHERE clause, no CONCURRENTLY):
+ * Directly modifies the matview in-place using DELETE/INSERT or upsert.
+ * Uses RowExclusiveLock, allowing concurrent reads but blocking writes.
+ *
+ * 2. Concurrent (CONCURRENTLY, with or without WHERE clause):
+ * Creates a temporary table with new data, computes a diff against
+ * the existing matview, and applies changes. Uses ExclusiveLock,
+ * allowing concurrent reads throughout the operation.
+ *
+ * 3. Full rebuild (default, no WHERE, no CONCURRENTLY):
+ * Creates a new heap, populates it, and swaps relfilenumbers.
+ * Uses AccessExclusiveLock, blocking all concurrent access.
+ * The OID of the original materialized view is preserved, so we
+ * do not lose GRANT nor references to this materialized view.
*
* If skipData is true, this is effectively like a TRUNCATE; otherwise it is
* like a TRUNCATE followed by an INSERT using the SELECT statement associated
* with the materialized view.
*
- * Indexes are rebuilt too, via REINDEX. Since we are effectively bulk-loading
- * the new heap, it's better to create the indexes afterwards than to fill them
- * incrementally while we load.
+ * For full rebuild, indexes are rebuilt too, via REINDEX. Since we are
+ * effectively bulk-loading the new heap, it's better to create the indexes
+ * afterwards than to fill them incrementally while we load.
*
* The matview's "populated" state is changed based on whether the contents
* reflect the result set of the materialized view's query.
@@ -162,22 +387,22 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
*/
ObjectAddress
RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
- bool concurrent, const char *queryString,
+ bool concurrent, Node *whereClause,
+ const char *queryString, ParamListInfo params,
QueryCompletion *qc)
{
Relation matviewRel;
RewriteRule *rule;
List *actions;
Query *dataQuery;
- Oid tableSpace;
Oid relowner;
- Oid OIDNewHeap;
uint64 processed = 0;
- char relpersistence;
Oid save_userid;
int save_sec_context;
int save_nestlevel;
ObjectAddress address;
+ Node *qual = NULL;
+ char *qual_str = NULL;
matviewRel = table_open(matviewOid, NoLock);
relowner = matviewRel->rd_rel->relowner;
@@ -206,7 +431,11 @@ RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("CONCURRENTLY cannot be used when the materialized view is not populated")));
- /* Check that conflicting options have not been specified. */
+ if (whereClause && !RelationIsPopulated(matviewRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WHERE clause cannot be used when the materialized view is not populated")));
+
if (concurrent && skipData)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -240,6 +469,12 @@ RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
"the rule for materialized view \"%s\" is not a single action",
RelationGetRelationName(matviewRel));
+ if (whereClause)
+ {
+ qual = transformRefreshWhereClause(matviewOid, whereClause, params);
+ qual_str = deparseRefreshWhereClause(matviewOid, qual);
+ }
+
/*
* Check that there is a unique index with no WHERE clause on one or more
* columns of the materialized view if CONCURRENTLY is specified.
@@ -298,47 +533,92 @@ RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
*/
SetMatViewPopulatedState(matviewRel, !skipData);
- /* Concurrent refresh builds new data in temp tablespace, and does diff. */
- if (concurrent)
- {
- tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
- relpersistence = RELPERSISTENCE_TEMP;
- }
- else
- {
- tableSpace = matviewRel->rd_rel->reltablespace;
- relpersistence = matviewRel->rd_rel->relpersistence;
- }
-
/*
- * Create the transient table that will receive the regenerated data. Lock
- * it against access by any other process until commit (by which time it
- * will be gone).
+ * STRATEGY 1: PARTIAL NON-CONCURRENT
*/
- OIDNewHeap = make_new_heap(matviewOid, tableSpace,
- matviewRel->rd_rel->relam,
- relpersistence, ExclusiveLock);
- Assert(CheckRelationOidLockedByMe(OIDNewHeap, AccessExclusiveLock, false));
-
- /* Generate the data, if wanted. */
- if (!skipData)
+ if (qual && !concurrent && !skipData)
{
- DestReceiver *dest;
+ PG_TRY();
+ {
+ refresh_by_direct_modification(matviewOid, relowner,
+ save_sec_context, qual_str, params);
+ }
+ PG_CATCH();
+ {
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
- dest = CreateTransientRelDestReceiver(OIDNewHeap);
- processed = refresh_matview_datafill(dest, dataQuery, queryString,
- is_create);
+ processed = SPI_processed;
}
- /* Make the matview match the newly generated data. */
- if (concurrent)
+ /*
+ * STRATEGY 2: CONCURRENT (PARTIAL or FULL)
+ */
+ else if (concurrent)
{
+ Oid tableSpace;
+ char relpersistence;
+ Oid OIDNewHeap;
int old_depth = matview_maintenance_depth;
+ tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
+ relpersistence = RELPERSISTENCE_TEMP;
+
+ /*
+ * Create the transient table that will receive the regenerated data.
+ * Lock it against access by any other process until commit (by which
+ * time it will be gone).
+ */
+ OIDNewHeap = make_new_heap(matviewOid, tableSpace,
+ matviewRel->rd_rel->relam,
+ relpersistence, ExclusiveLock);
+ Assert(CheckRelationOidLockedByMe(OIDNewHeap, AccessExclusiveLock, false));
+
+ /* Generate the data, if wanted. */
+ if (!skipData)
+ {
+ if (qual_str)
+ {
+ StringInfoData buf;
+ char *view_sql = get_matview_view_query(matviewOid);
+ char *transient_name;
+ Relation transientRel = table_open(OIDNewHeap, NoLock);
+
+ transient_name = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(transientRel)),
+ RelationGetRelationName(transientRel));
+ table_close(transientRel, NoLock);
+
+ /*
+ * Init buffer before SPI connection to avoid double free
+ * issues on context destroy
+ */
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "INSERT INTO %s SELECT * FROM (%s) _mv_q WHERE %s",
+ transient_name, view_sql, qual_str);
+
+ SPI_connect();
+ if (matview_execute_spi(buf.data, params, false) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", buf.data);
+ processed = SPI_processed;
+ SPI_finish();
+ pfree(view_sql);
+ pfree(transient_name);
+ pfree(buf.data);
+ }
+ else
+ {
+ DestReceiver *dest;
+
+ dest = CreateTransientRelDestReceiver(OIDNewHeap);
+ processed = refresh_matview_datafill(dest, dataQuery, queryString, is_create);
+ }
+ }
+
PG_TRY();
{
refresh_by_match_merge(matviewOid, OIDNewHeap, relowner,
- save_sec_context);
+ save_sec_context, qual_str, params);
}
PG_CATCH();
{
@@ -346,10 +626,34 @@ RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
PG_RE_THROW();
}
PG_END_TRY();
+
Assert(matview_maintenance_depth == old_depth);
}
+
+ /*
+ * STRATEGY 3: FULL REBUILD
+ */
else
{
+ Oid tableSpace;
+ char relpersistence;
+ Oid OIDNewHeap;
+
+ tableSpace = matviewRel->rd_rel->reltablespace;
+ relpersistence = matviewRel->rd_rel->relpersistence;
+
+ OIDNewHeap = make_new_heap(matviewOid, tableSpace,
+ matviewRel->rd_rel->relam,
+ relpersistence, AccessExclusiveLock);
+
+ if (!skipData)
+ {
+ DestReceiver *dest;
+
+ dest = CreateTransientRelDestReceiver(OIDNewHeap);
+ processed = refresh_matview_datafill(dest, dataQuery, queryString, is_create);
+ }
+
refresh_by_heap_swap(matviewOid, OIDNewHeap, relpersistence);
/*
@@ -555,11 +859,345 @@ transientrel_destroy(DestReceiver *self)
pfree(self);
}
+/*
+ * get_matview_view_query
+ *
+ * Retrieve the SQL definition of a materialized view's underlying query.
+ * Returns the query text with trailing semicolons and whitespace removed.
+ */
+static char *
+get_matview_view_query(Oid matviewOid)
+{
+ char *view_sql;
+
+ view_sql = TextDatumGetCString(DirectFunctionCall2(pg_get_viewdef,
+ ObjectIdGetDatum(matviewOid),
+ BoolGetDatum(false)));
+ if (view_sql)
+ {
+ int len = strlen(view_sql);
+
+ while (len > 0 && (view_sql[len - 1] == ';' || isspace((unsigned char) view_sql[len - 1])))
+ view_sql[--len] = '\0';
+ }
+ return view_sql;
+}
+
+static void
+InitMatViewCache(void)
+{
+ HASHCTL ctl;
+
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(MatViewPartialRefreshCache);
+ ctl.hcxt = CacheMemoryContext;
+
+ MatViewRefreshCache = hash_create("MatView Partial Refresh Cache",
+ 16,
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+}
+
+/*
+ * refresh_by_direct_modification
+ *
+ * Strategy for non-concurrent partial refresh (REFRESH ... WHERE ...).
+ * Directly modifies the materialized view in-place without creating a
+ * temporary heap or swapping relfilenumbers.
+ *
+ * STRATEGY: DELETE -> UPSERT
+ *
+ * This implementation uses a session-level cache to store the prepared SPI
+ * plans for the DELETE and UPSERT operations. This avoids the overhead of
+ * calling pg_get_viewdef(), reconstructing SQL strings, and re-parsing
+ * them on every execution.
+ */
+static void
+refresh_by_direct_modification(Oid matviewOid, Oid relowner,
+ int save_sec_context, char *whereClauseStr,
+ ParamListInfo params)
+{
+ Relation matviewRel;
+ Oid uniqueIndexOid = InvalidOid;
+ List *indexoidlist;
+ ListCell *lc;
+ MatViewPartialRefreshCache *cacheEntry = NULL;
+ bool found = false;
+ bool use_cache = false;
+
+ /*
+ * Setup and Analysis (must happen every time to locate correct index)
+ * Note: We could cache the index OID too, but looking it up is cheap
+ * compared to view decompilation.
+ */
+
+ matviewRel = table_open(matviewOid, NoLock);
+
+ /* Search for a usable unique index (PK preferred) */
+ indexoidlist = RelationGetIndexList(matviewRel);
+ foreach(lc, indexoidlist)
+ {
+ Oid indexoid = lfirst_oid(lc);
+ Relation indexRel;
+ bool usable;
+ bool is_pk;
+
+ indexRel = index_open(indexoid, AccessShareLock);
+ usable = is_usable_unique_index(indexRel);
+ is_pk = indexRel->rd_index->indisprimary;
+ index_close(indexRel, AccessShareLock);
+
+ if (usable)
+ {
+ if (is_pk)
+ {
+ uniqueIndexOid = indexoid;
+ break;
+ }
+ if (!OidIsValid(uniqueIndexOid))
+ uniqueIndexOid = indexoid;
+ }
+ }
+ list_free(indexoidlist);
+
+ if (!OidIsValid(uniqueIndexOid))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot perform partial refresh on materialized view \"%s\"",
+ RelationGetRelationName(matviewRel)),
+ errdetail("Partial refresh requires a usable unique index to perform an UPSERT operation.")));
+
+ /*
+ * Cache Lookup
+ */
+ if (!MatViewRefreshCache)
+ InitMatViewCache();
+
+ cacheEntry = (MatViewPartialRefreshCache *) hash_search(MatViewRefreshCache,
+ &matviewOid,
+ HASH_ENTER,
+ &found);
+
+ if (found)
+ {
+ /*
+ * Validate the cache entry. We must ensure the WHERE clause string is
+ * identical (in case the user calls different partial refresh queries
+ * in the same session) and that the unique index has not changed
+ * (e.g. drop/create index).
+ */
+ if (cacheEntry->uniqueIndexOid == uniqueIndexOid &&
+ strcmp(cacheEntry->whereClauseStr, whereClauseStr) == 0)
+ {
+ use_cache = true;
+ }
+ else
+ {
+ /* Invalid entry. Clean up old plans and strings. */
+ if (cacheEntry->deletePlan)
+ SPI_freeplan(cacheEntry->deletePlan);
+ if (cacheEntry->upsertPlan)
+ SPI_freeplan(cacheEntry->upsertPlan);
+ if (cacheEntry->whereClauseStr)
+ pfree(cacheEntry->whereClauseStr);
+
+ cacheEntry->deletePlan = NULL;
+ cacheEntry->upsertPlan = NULL;
+ cacheEntry->whereClauseStr = NULL;
+ found = false; /* Force rebuild */
+ }
+ }
+
+ OpenMatViewIncrementalMaintenance();
+ SPI_connect();
+
+ if (use_cache)
+ {
+ /*
+ * FAST PATH: Execute cached plans. SPI_execute_plan will handle
+ * re-planning if underlying schema changes.
+ */
+ if (matview_execute_spi_plan(cacheEntry->deletePlan, params, false) < 0)
+ elog(ERROR, "SPI_execute_plan failed during delete");
+
+ if (matview_execute_spi_plan(cacheEntry->upsertPlan, params, false) < 0)
+ elog(ERROR, "SPI_execute_plan failed during upsert");
+ }
+ else
+ {
+ /*
+ * SLOW PATH: Build SQL, Prepare, Cache.
+ */
+ StringInfoData buf;
+ char *view_sql;
+ char *matview_name;
+ const char *matview_alias;
+ Oid *argtypes = NULL;
+ int nargs = 0;
+
+ matview_name = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+ matview_alias = quote_identifier(RelationGetRelationName(matviewRel));
+
+ initStringInfo(&buf);
+ view_sql = get_matview_view_query(matviewOid);
+
+ /* Prepare argument types for SPI_prepare */
+ if (params && params->numParams > 0)
+ {
+ nargs = params->numParams;
+ argtypes = (Oid *) palloc(nargs * sizeof(Oid));
+ for (int i = 0; i < nargs; i++)
+ argtypes[i] = params->params[i].ptype;
+ }
+
+ /*
+ * STEP 1: DELETE (Prune)
+ */
+ resetStringInfo(&buf);
+ appendStringInfo(&buf,
+ "DELETE FROM %s mv "
+ "WHERE (%s)",
+ matview_name,
+ whereClauseStr);
+
+ cacheEntry->deletePlan = SPI_prepare(buf.data, nargs, argtypes);
+ if (cacheEntry->deletePlan == NULL)
+ elog(ERROR, "SPI_prepare failed for delete: %s", buf.data);
+ SPI_keepplan(cacheEntry->deletePlan);
+
+ if (matview_execute_spi_plan(cacheEntry->deletePlan, params, false) < 0)
+ elog(ERROR, "SPI_execute_plan failed during delete");
+
+ /*
+ * STEP 2: UPSERT
+ */
+ {
+ Relation indexRel;
+ Form_pg_index indexStruct;
+ TupleDesc tupdesc = matviewRel->rd_att;
+ StringInfoData conflict_cols;
+ StringInfoData set_clause;
+ bool first;
+ bool has_non_key_cols = false;
+ int i;
+
+ indexRel = index_open(uniqueIndexOid, AccessShareLock);
+ indexStruct = indexRel->rd_index;
+
+ initStringInfo(&conflict_cols);
+ initStringInfo(&set_clause);
+
+ /* Build ON CONFLICT keys */
+ first = true;
+ for (i = 0; i < indexStruct->indnatts; i++)
+ {
+ int attnum = indexStruct->indkey.values[i];
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+ char *attname = NameStr(attr->attname);
+
+ if (!first)
+ appendStringInfoString(&conflict_cols, ", ");
+ first = false;
+ appendStringInfo(&conflict_cols, "%s", quote_identifier(attname));
+ }
+
+ /* Build UPDATE SET clause */
+ first = true;
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+ char *attname = NameStr(attr->attname);
+ bool is_key = false;
+ int j;
+
+ if (attr->attisdropped)
+ continue;
+
+ for (j = 0; j < indexStruct->indnatts; j++)
+ {
+ if (indexStruct->indkey.values[j] == (i + 1))
+ {
+ is_key = true;
+ break;
+ }
+ }
+
+ if (is_key)
+ continue;
+
+ if (!first)
+ appendStringInfoString(&set_clause, ", ");
+ first = false;
+ has_non_key_cols = true;
+
+ appendStringInfo(&set_clause, "%s = EXCLUDED.%s",
+ quote_identifier(attname), quote_identifier(attname));
+ }
+
+ index_close(indexRel, AccessShareLock);
+
+ resetStringInfo(&buf);
+ if (has_non_key_cols)
+ {
+ appendStringInfo(&buf,
+ "INSERT INTO %s "
+ "SELECT * FROM (%s) %s WHERE (%s) "
+ "ON CONFLICT (%s) DO UPDATE SET %s",
+ matview_name,
+ view_sql, matview_alias, whereClauseStr,
+ conflict_cols.data,
+ set_clause.data);
+ }
+ else
+ {
+ appendStringInfo(&buf,
+ "INSERT INTO %s "
+ "SELECT * FROM (%s) %s WHERE (%s) "
+ "ON CONFLICT (%s) DO NOTHING",
+ matview_name,
+ view_sql, matview_alias, whereClauseStr,
+ conflict_cols.data);
+ }
+
+ pfree(conflict_cols.data);
+ pfree(set_clause.data);
+ }
+
+ cacheEntry->upsertPlan = SPI_prepare(buf.data, nargs, argtypes);
+ if (cacheEntry->upsertPlan == NULL)
+ elog(ERROR, "SPI_prepare failed for upsert: %s", buf.data);
+ SPI_keepplan(cacheEntry->upsertPlan);
+
+ if (matview_execute_spi_plan(cacheEntry->upsertPlan, params, false) < 0)
+ elog(ERROR, "SPI_execute_plan failed during upsert");
+
+ /* Update Cache Metadata */
+ {
+ MemoryContext oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+
+ cacheEntry->uniqueIndexOid = uniqueIndexOid;
+ cacheEntry->whereClauseStr = pstrdup(whereClauseStr);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ pfree(view_sql);
+ pfree(buf.data);
+ if (argtypes)
+ pfree(argtypes);
+ }
+
+ SPI_finish();
+ CloseMatViewIncrementalMaintenance();
+ table_close(matviewRel, NoLock);
+}
+
/*
* refresh_by_match_merge
*
* Refresh a materialized view with transactional semantics, while allowing
- * concurrent reads.
+ * concurrent reads. Used for REFRESH MATERIALIZED VIEW CONCURRENTLY.
*
* This is called after a new version of the data has been created in a
* temporary table. It performs a full outer join against the old version of
@@ -572,6 +1210,10 @@ transientrel_destroy(DestReceiver *self)
* are consistent with default behavior. If there is at least one UNIQUE
* index on the materialized view, we have exactly the guarantee we need.
*
+ * If whereClauseStr is provided, only rows matching the WHERE condition
+ * in the existing matview are considered for the diff operation, enabling
+ * partial concurrent refresh.
+ *
* The temporary table used to hold the diff results contains just the TID of
* the old record (if matched) and the ROW from the new table as a single
* column of complex record type (if matched).
@@ -589,7 +1231,8 @@ transientrel_destroy(DestReceiver *self)
*/
static void
refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
- int save_sec_context)
+ int save_sec_context, char *whereClauseStr,
+ ParamListInfo params)
{
StringInfoData querybuf;
Relation matviewRel;
@@ -703,8 +1346,15 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
appendStringInfo(&querybuf,
"INSERT INTO %s "
"SELECT mv.ctid AS tid, newdata.*::%s AS newdata "
- "FROM %s mv FULL JOIN %s newdata ON (",
- diffname, tempname, matviewname, tempname);
+ "FROM ",
+ diffname, tempname);
+
+ if (whereClauseStr)
+ appendStringInfo(&querybuf, "(SELECT ctid, * FROM %s WHERE %s) mv", matviewname, whereClauseStr);
+ else
+ appendStringInfo(&querybuf, "%s mv", matviewname);
+
+ appendStringInfo(&querybuf, " FULL JOIN %s newdata ON (", tempname);
/*
* Get the list of index OIDs for the table from the relcache, and look up
@@ -826,13 +1476,42 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
errmsg("could not find suitable unique index on materialized view \"%s\"",
RelationGetRelationName(matviewRel)));
- appendStringInfoString(&querybuf,
- " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) "
- "WHERE newdata.* IS NULL OR mv.* IS NULL "
- "ORDER BY tid");
+ if (whereClauseStr)
+ {
+ StringInfoData cols;
+ int i;
+ bool first = true;
+
+ initStringInfo(&cols);
+ for (i = 0; i < relnatts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attisdropped)
+ continue;
+ if (!first)
+ appendStringInfoString(&cols, ", ");
+ first = false;
+ appendStringInfo(&cols, "mv.%s", quote_qualified_identifier(NULL, NameStr(attr->attname)));
+ }
+
+ appendStringInfo(&querybuf,
+ " AND newdata.* OPERATOR(pg_catalog.*=) ROW(%s)) "
+ "WHERE newdata.* IS NULL OR mv.ctid IS NULL "
+ "ORDER BY tid",
+ cols.data);
+ pfree(cols.data);
+ }
+ else
+ {
+ appendStringInfoString(&querybuf,
+ " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) "
+ "WHERE newdata.* IS NULL OR mv.* IS NULL "
+ "ORDER BY tid");
+ }
/* Populate the temporary "diff" table. */
- if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ if (matview_execute_spi(querybuf.data, params, false) != SPI_OK_INSERT)
elog(ERROR, "SPI_exec failed: %s", querybuf.data);
/*
@@ -897,7 +1576,15 @@ refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence)
}
/*
- * Check whether specified index is usable for match merge.
+ * is_usable_unique_index
+ *
+ * Check whether the specified index is usable for concurrent refresh
+ * (refresh_by_match_merge) or for the upsert strategy in non-concurrent
+ * partial refresh (refresh_by_direct_modification).
+ *
+ * A usable index must be unique, valid, immediate (not deferrable),
+ * non-partial (no WHERE clause), and defined over plain user columns
+ * (not expressions or system columns).
*/
static bool
is_usable_unique_index(Relation indexRel)
@@ -945,8 +1632,10 @@ is_usable_unique_index(Relation indexRel)
*
* While the function names reflect the fact that their main intended use is
* incremental maintenance of materialized views (in response to changes to
- * the data in referenced relations), they are initially used to allow REFRESH
- * without blocking concurrent reads.
+ * the data in referenced relations), they are currently used to allow:
+ *
+ * - REFRESH CONCURRENTLY without blocking concurrent reads.
+ * - Partial REFRESH (with WHERE clause) which modifies the matview in-place.
*/
bool
MatViewIncrementalMaintenanceIsEnabled(void)
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 27c9eec697b..b685e14951f 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1208,7 +1208,8 @@ CheckValidRowMarkRel(Relation rel, RowMarkType markType)
break;
case RELKIND_MATVIEW:
/* Allow referencing a matview, but not actual locking clauses */
- if (markType != ROW_MARK_REFERENCE)
+ if (markType != ROW_MARK_REFERENCE &&
+ !MatViewIncrementalMaintenanceIsEnabled())
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot lock rows in materialized view \"%s\"",
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c3a0a354a9c..52a28e01b4a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -323,6 +323,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> opt_single_name
%type <list> opt_qualified_name
%type <boolean> opt_concurrently
+%type <node> opt_refresh_where_clause
%type <dbehavior> opt_drop_behavior
%type <list> opt_utility_option_list
%type <list> opt_wait_with_clause
@@ -4988,17 +4989,28 @@ OptNoLog: UNLOGGED { $$ = RELPERSISTENCE_UNLOGGED; }
*****************************************************************************/
RefreshMatViewStmt:
- REFRESH MATERIALIZED VIEW opt_concurrently qualified_name opt_with_data
+ REFRESH MATERIALIZED VIEW opt_concurrently qualified_name opt_with_data opt_refresh_where_clause
{
RefreshMatViewStmt *n = makeNode(RefreshMatViewStmt);
n->concurrent = $4;
n->relation = $5;
n->skipData = !($6);
+ n->whereClause = $7;
+
+ if (n->skipData && n->whereClause)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot specify WHERE clause with WITH NO DATA")));
$$ = (Node *) n;
}
;
+opt_refresh_where_clause:
+ WHERE a_expr { $$ = $2; }
+ | /* empty */ { $$ = NULL; }
+ ;
+
/*****************************************************************************
*
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index d18a3a60a46..f7a9eba5503 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1690,7 +1690,7 @@ ProcessUtilitySlow(ParseState *pstate,
PG_TRY(2);
{
address = ExecRefreshMatView((RefreshMatViewStmt *) parsetree,
- queryString, qc);
+ queryString, params, qc);
}
PG_FINALLY(2);
{
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index 750bb10ddca..f4a3bf52cfb 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -24,9 +24,11 @@
extern void SetMatViewPopulatedState(Relation relation, bool newstate);
extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
- QueryCompletion *qc);
+ ParamListInfo params, QueryCompletion *qc);
+
extern ObjectAddress RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
- bool concurrent, const char *queryString,
+ bool concurrent, Node *whereClause,
+ const char *queryString, ParamListInfo params,
QueryCompletion *qc);
extern DestReceiver *CreateTransientRelDestReceiver(Oid transientoid);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d14294a4ece..cb8b8a8c4ee 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4041,6 +4041,7 @@ typedef struct RefreshMatViewStmt
bool concurrent; /* allow concurrent access? */
bool skipData; /* true for WITH NO DATA */
RangeVar *relation; /* relation to insert into */
+ Node *whereClause; /* qualification for partial refresh */
} RefreshMatViewStmt;
/* ----------------------
diff --git a/src/test/regress/expected/matview_where.out b/src/test/regress/expected/matview_where.out
new file mode 100644
index 00000000000..464c19e79be
--- /dev/null
+++ b/src/test/regress/expected/matview_where.out
@@ -0,0 +1,346 @@
+--
+-- REFRESH MATERIALIZED VIEW ... WHERE ...
+--
+-- Setup
+CREATE TABLE mv_base_a (id int primary key, val text);
+INSERT INTO mv_base_a VALUES (1, 'One'), (2, 'Two'), (3, 'Three');
+CREATE MATERIALIZED VIEW mv_test_a AS SELECT * FROM mv_base_a;
+CREATE UNIQUE INDEX ON mv_test_a(id);
+--
+-- Test 1: Syntax and Error handling
+--
+-- 1.1 WITH NO DATA + WHERE -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WITH NO DATA WHERE id = 1;
+ERROR: cannot specify WHERE clause with WITH NO DATA
+-- 1.2 Unpopulated + WHERE -> Error
+CREATE MATERIALIZED VIEW mv_unpop AS SELECT * FROM mv_base_a WITH NO DATA;
+REFRESH MATERIALIZED VIEW mv_unpop WHERE id = 1;
+ERROR: WHERE clause cannot be used when the materialized view is not populated
+DROP MATERIALIZED VIEW mv_unpop;
+-- 1.3 Volatile functions -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WHERE random() > 0.5;
+ERROR: WHERE clause in REFRESH MATERIALIZED VIEW cannot contain volatile functions
+-- 1.4 Subqueries -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WHERE id IN (SELECT id FROM public.mv_base_a);
+-- 1.5 Aggregates -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WHERE count(*) > 0;
+ERROR: aggregate functions are not allowed in WHERE
+--
+-- Test 2: Non-concurrent Partial Refresh
+--
+-- Modify base data
+UPDATE mv_base_a SET val = 'One Updated' WHERE id = 1;
+UPDATE mv_base_a SET val = 'Two Updated' WHERE id = 2;
+-- Refresh only id=1
+REFRESH MATERIALIZED VIEW mv_test_a WHERE id = 1;
+-- Verify: id=1 should be updated, id=2 should remain stale
+SELECT * FROM mv_test_a ORDER BY id;
+ id | val
+----+-------------
+ 1 | One Updated
+ 2 | Two
+ 3 | Three
+(3 rows)
+
+-- Refresh id=2
+REFRESH MATERIALIZED VIEW mv_test_a WHERE id = 2;
+SELECT * FROM mv_test_a ORDER BY id;
+ id | val
+----+-------------
+ 1 | One Updated
+ 2 | Two Updated
+ 3 | Three
+(3 rows)
+
+--
+-- Test 3: Concurrent Partial Refresh
+--
+-- Modify base data
+UPDATE mv_base_a SET val = 'One Concurrent' WHERE id = 1;
+UPDATE mv_base_a SET val = 'Two Concurrent' WHERE id = 2;
+-- Refresh only id=1
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_a WHERE id = 1;
+-- Verify: id=1 updated, id=2 stale
+SELECT * FROM mv_test_a ORDER BY id;
+ id | val
+----+----------------
+ 1 | One Concurrent
+ 2 | Two Updated
+ 3 | Three
+(3 rows)
+
+-- Refresh id=2
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_a WHERE id = 2;
+SELECT * FROM mv_test_a ORDER BY id;
+ id | val
+----+----------------
+ 1 | One Concurrent
+ 2 | Two Concurrent
+ 3 | Three
+(3 rows)
+
+-- Cleanup Test 2/3
+DROP MATERIALIZED VIEW mv_test_a;
+DROP TABLE mv_base_a;
+--
+-- Test 4: Join View (Invoice style)
+--
+CREATE TABLE invoices (id int primary key, total numeric);
+CREATE TABLE invoice_items (inv_id int references invoices(id), amount numeric);
+INSERT INTO invoices VALUES (1, 0), (2, 0);
+INSERT INTO invoice_items VALUES (1, 100), (1, 50), (2, 200);
+CREATE MATERIALIZED VIEW mv_invoices AS
+ SELECT i.id, sum(ii.amount) as computed_total
+ FROM invoices i
+ JOIN invoice_items ii ON i.id = ii.inv_id
+ GROUP BY i.id;
+CREATE UNIQUE INDEX ON mv_invoices(id);
+SELECT * FROM mv_invoices ORDER BY id;
+ id | computed_total
+----+----------------
+ 1 | 150
+ 2 | 200
+(2 rows)
+
+-- Modify items for invoice 1
+INSERT INTO invoice_items VALUES (1, 25);
+-- Modify items for invoice 2
+INSERT INTO invoice_items VALUES (2, 50);
+-- Refresh only invoice 1
+REFRESH MATERIALIZED VIEW mv_invoices WHERE id = 1;
+-- Verify: Invoice 1 updated (175), Invoice 2 stale (200)
+SELECT * FROM mv_invoices ORDER BY id;
+ id | computed_total
+----+----------------
+ 1 | 175
+ 2 | 200
+(2 rows)
+
+-- Refresh invoice 2 concurrently
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_invoices WHERE id = 2;
+-- Verify: Invoice 2 updated (250)
+SELECT * FROM mv_invoices ORDER BY id;
+ id | computed_total
+----+----------------
+ 1 | 175
+ 2 | 250
+(2 rows)
+
+DROP MATERIALIZED VIEW mv_invoices;
+DROP TABLE invoice_items;
+DROP TABLE invoices;
+--
+-- Test 5: Rows entering/leaving view scope
+--
+CREATE TABLE items (id int, status text, val int);
+INSERT INTO items VALUES (1, 'active', 10), (2, 'inactive', 20);
+CREATE MATERIALIZED VIEW mv_active_items AS
+ SELECT * FROM items WHERE status = 'active';
+CREATE UNIQUE INDEX ON mv_active_items(id);
+SELECT * FROM mv_active_items ORDER BY id;
+ id | status | val
+----+--------+-----
+ 1 | active | 10
+(1 row)
+
+-- Case A: Row changes status active -> inactive (should be removed)
+UPDATE items SET status = 'inactive' WHERE id = 1;
+-- Also update row 2 to active (should be added)
+UPDATE items SET status = 'active' WHERE id = 2;
+-- Refresh partial WHERE id=1
+-- Should remove id=1 because it no longer matches view definition
+REFRESH MATERIALIZED VIEW mv_active_items WHERE id = 1;
+SELECT * FROM mv_active_items ORDER BY id;
+ id | status | val
+----+--------+-----
+(0 rows)
+
+-- Case B: Refresh to add row 2 (which is now active)
+REFRESH MATERIALIZED VIEW mv_active_items WHERE id = 2;
+SELECT * FROM mv_active_items ORDER BY id;
+ id | status | val
+----+--------+-----
+ 2 | active | 20
+(1 row)
+
+-- Cleanup
+DROP MATERIALIZED VIEW mv_active_items;
+DROP TABLE items;
+--
+-- Test 6: Order of Operations (Value Swap)
+-- Addressed specific worry: "The order of tuple processing matters"
+--
+CREATE TABLE mv_swap_base (id int primary key, code text);
+INSERT INTO mv_swap_base VALUES (1, 'A'), (2, 'B');
+CREATE MATERIALIZED VIEW mv_swap AS SELECT * FROM mv_swap_base;
+CREATE UNIQUE INDEX ON mv_swap(code); -- Unique Index is on code, not ID
+SELECT * FROM mv_swap ORDER BY id;
+ id | code
+----+------
+ 1 | A
+ 2 | B
+(2 rows)
+
+-- Perform a swap in the base table
+-- 1 becomes B, 2 becomes A
+BEGIN;
+UPDATE mv_swap_base SET code = 'TEMP' WHERE id = 1;
+UPDATE mv_swap_base SET code = 'A' WHERE id = 2;
+UPDATE mv_swap_base SET code = 'B' WHERE id = 1;
+COMMIT;
+-- Refresh both rows concurrently.
+-- If the implementation inserts (1, 'B') before deleting (2, 'B'), this will fail.
+-- It relies on the implementation correctly handling the Delete/Lock set before the Insert/Upsert.
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_swap WHERE id IN (1, 2);
+SELECT * FROM mv_swap ORDER BY id;
+ id | code
+----+------
+ 1 | B
+ 2 | A
+(2 rows)
+
+DROP MATERIALIZED VIEW mv_swap;
+DROP TABLE mv_swap_base;
+--
+-- Test 7: Scope Drift / Constraint Violation
+-- Addressed specific worry: "If WHERE predicate would be different... UK violation couldn't be solved"
+--
+CREATE TABLE mv_drift_base (id int primary key, category_id int);
+INSERT INTO mv_drift_base VALUES (1, 100), (2, 200);
+CREATE MATERIALIZED VIEW mv_drift AS SELECT * FROM mv_drift_base;
+-- KEY FIX: Index on ID, not Category.
+-- We want to test that the Refresh logic detects ID conflicts when rows drift into scope,
+-- not that Postgres enforces unique indexes on non-unique data.
+CREATE UNIQUE INDEX ON mv_drift(id);
+-- Update Row 1 to collide with Row 2's category
+UPDATE mv_drift_base SET category_id = 200 WHERE id = 1;
+-- Attempt to refresh using the NEW category value as the filter.
+-- The View still contains (1, 100).
+-- The Filter "category_id = 200" sees the NEW row (1, 200) in the base table.
+-- The Filter "category_id = 200" does NOT see the OLD row (1, 100) in the View.
+-- Result: The system thinks (1, 200) is a brand new row and tries to INSERT it.
+-- This MUST fail with a Unique Constraint Violation on 'id' because (1, 100) was never deleted.
+\set VERBOSITY terse
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_drift WHERE category_id = 200;
+ERROR: duplicate key value violates unique constraint "mv_drift_id_idx"
+\set VERBOSITY default
+-- Correct usage: Scope must include BOTH the Old location (100) and New location (200)
+-- so the system sees the update as an update (or delete+insert).
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_drift WHERE category_id IN (100, 200);
+SELECT * FROM mv_drift ORDER BY id;
+ id | category_id
+----+-------------
+ 1 | 200
+ 2 | 200
+(2 rows)
+
+DROP MATERIALIZED VIEW mv_drift;
+DROP TABLE mv_drift_base;
+--
+-- Test 8: Multiple Unique Keys
+-- Addressed specific worry: "what if we have multiple UKs?"
+--
+CREATE TABLE mv_multi_uk (id int primary key, email text, username text);
+INSERT INTO mv_multi_uk VALUES (1, 'a@example.com', 'user_a');
+CREATE MATERIALIZED VIEW mv_multi AS SELECT * FROM mv_multi_uk;
+CREATE UNIQUE INDEX ON mv_multi(email);
+CREATE UNIQUE INDEX ON mv_multi(username);
+-- Update all columns
+UPDATE mv_multi_uk SET email = 'b@example.com', username = 'user_b' WHERE id = 1;
+-- Refresh should succeed updating all unique indexes
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_multi WHERE id = 1;
+SELECT * FROM mv_multi;
+ id | email | username
+----+---------------+----------
+ 1 | b@example.com | user_b
+(1 row)
+
+DROP MATERIALIZED VIEW mv_multi;
+DROP TABLE mv_multi_uk;
+--
+-- Test 9: Trigger-based Automatic Maintenance
+-- Use Case: Automating the partial refresh via triggers using Arrays.
+--
+CREATE TABLE mv_trigger_base (id int primary key, val text);
+CREATE MATERIALIZED VIEW mv_trigger_view AS SELECT * FROM mv_trigger_base;
+CREATE UNIQUE INDEX ON mv_trigger_view(id);
+-- Create a maintainer function
+CREATE OR REPLACE FUNCTION maintain_mv_trigger_view() RETURNS TRIGGER AS $$
+BEGIN
+ IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
+ EXECUTE 'REFRESH MATERIALIZED VIEW mv_trigger_view WHERE id = ANY($1);'
+ USING (SELECT array_agg(id) FROM new_table);
+ END IF;
+
+ IF (TG_OP IN ('DELETE')) THEN
+ EXECUTE 'REFRESH MATERIALIZED VIEW mv_trigger_view WHERE id = ANY($1);'
+ USING (SELECT array_agg(id) FROM old_table);
+ END IF;
+
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql VOLATILE;
+-- Trigger for Insert
+CREATE TRIGGER t_refresh_mv_ins
+ AFTER INSERT ON mv_trigger_base
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH STATEMENT
+EXECUTE FUNCTION maintain_mv_trigger_view();
+-- Trigger for Update
+CREATE TRIGGER t_refresh_mv_upd
+ AFTER UPDATE ON mv_trigger_base
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH STATEMENT
+EXECUTE FUNCTION maintain_mv_trigger_view();
+-- Trigger for Delete
+CREATE TRIGGER t_refresh_mv_del
+ AFTER DELETE ON mv_trigger_base
+ REFERENCING OLD TABLE AS old_table
+ FOR EACH STATEMENT
+EXECUTE FUNCTION maintain_mv_trigger_view();
+-- 1. Test Insert
+INSERT INTO mv_trigger_base VALUES (1, 'Auto-Insert'), (2, 'Auto-Insert');
+SELECT * FROM mv_trigger_view ORDER BY id;
+ id | val
+----+-------------
+ 1 | Auto-Insert
+ 2 | Auto-Insert
+(2 rows)
+
+-- 2. Test Update
+UPDATE mv_trigger_base SET val = 'Auto-Update' WHERE id = 1;
+SELECT * FROM mv_trigger_view ORDER BY id;
+ id | val
+----+-------------
+ 1 | Auto-Update
+ 2 | Auto-Insert
+(2 rows)
+
+-- 3. Test Delete
+DELETE FROM mv_trigger_base WHERE id = 2;
+SELECT * FROM mv_trigger_view ORDER BY id;
+ id | val
+----+-------------
+ 1 | Auto-Update
+(1 row)
+
+-- 4. Verify Transaction Isolation
+-- Ensure that if the main transaction rolls back, the Refresh also rolls back
+BEGIN;
+INSERT INTO mv_trigger_base VALUES (99, 'Rollback');
+SELECT * FROM mv_trigger_view WHERE id = 99; -- Should see it
+ id | val
+----+----------
+ 99 | Rollback
+(1 row)
+
+ROLLBACK;
+SELECT * FROM mv_trigger_view WHERE id = 99; -- Should NOT see it
+ id | val
+----+-----
+(0 rows)
+
+-- Cleanup
+DROP MATERIALIZED VIEW mv_trigger_view;
+DROP TABLE mv_trigger_base;
+DROP FUNCTION maintain_mv_trigger_view();
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index cc6d799bcea..14aa5921c94 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -68,6 +68,11 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated_stored join_hash
+# ----------
+# Additional Mat View tests
+# ----------
+test: matview_where
+
# ----------
# Additional BRIN tests
# ----------
diff --git a/src/test/regress/sql/matview_where.sql b/src/test/regress/sql/matview_where.sql
new file mode 100644
index 00000000000..6be5ef8bf77
--- /dev/null
+++ b/src/test/regress/sql/matview_where.sql
@@ -0,0 +1,305 @@
+--
+-- REFRESH MATERIALIZED VIEW ... WHERE ...
+--
+
+-- Setup
+CREATE TABLE mv_base_a (id int primary key, val text);
+INSERT INTO mv_base_a VALUES (1, 'One'), (2, 'Two'), (3, 'Three');
+
+CREATE MATERIALIZED VIEW mv_test_a AS SELECT * FROM mv_base_a;
+CREATE UNIQUE INDEX ON mv_test_a(id);
+
+--
+-- Test 1: Syntax and Error handling
+--
+
+-- 1.1 WITH NO DATA + WHERE -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WITH NO DATA WHERE id = 1;
+
+-- 1.2 Unpopulated + WHERE -> Error
+CREATE MATERIALIZED VIEW mv_unpop AS SELECT * FROM mv_base_a WITH NO DATA;
+REFRESH MATERIALIZED VIEW mv_unpop WHERE id = 1;
+DROP MATERIALIZED VIEW mv_unpop;
+
+-- 1.3 Volatile functions -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WHERE random() > 0.5;
+
+-- 1.4 Subqueries -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WHERE id IN (SELECT id FROM public.mv_base_a);
+
+-- 1.5 Aggregates -> Error
+REFRESH MATERIALIZED VIEW mv_test_a WHERE count(*) > 0;
+
+--
+-- Test 2: Non-concurrent Partial Refresh
+--
+
+-- Modify base data
+UPDATE mv_base_a SET val = 'One Updated' WHERE id = 1;
+UPDATE mv_base_a SET val = 'Two Updated' WHERE id = 2;
+
+-- Refresh only id=1
+REFRESH MATERIALIZED VIEW mv_test_a WHERE id = 1;
+
+-- Verify: id=1 should be updated, id=2 should remain stale
+SELECT * FROM mv_test_a ORDER BY id;
+
+-- Refresh id=2
+REFRESH MATERIALIZED VIEW mv_test_a WHERE id = 2;
+SELECT * FROM mv_test_a ORDER BY id;
+
+--
+-- Test 3: Concurrent Partial Refresh
+--
+
+-- Modify base data
+UPDATE mv_base_a SET val = 'One Concurrent' WHERE id = 1;
+UPDATE mv_base_a SET val = 'Two Concurrent' WHERE id = 2;
+
+-- Refresh only id=1
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_a WHERE id = 1;
+
+-- Verify: id=1 updated, id=2 stale
+SELECT * FROM mv_test_a ORDER BY id;
+
+-- Refresh id=2
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_a WHERE id = 2;
+SELECT * FROM mv_test_a ORDER BY id;
+
+-- Cleanup Test 2/3
+DROP MATERIALIZED VIEW mv_test_a;
+DROP TABLE mv_base_a;
+
+--
+-- Test 4: Join View (Invoice style)
+--
+
+CREATE TABLE invoices (id int primary key, total numeric);
+CREATE TABLE invoice_items (inv_id int references invoices(id), amount numeric);
+
+INSERT INTO invoices VALUES (1, 0), (2, 0);
+INSERT INTO invoice_items VALUES (1, 100), (1, 50), (2, 200);
+
+CREATE MATERIALIZED VIEW mv_invoices AS
+ SELECT i.id, sum(ii.amount) as computed_total
+ FROM invoices i
+ JOIN invoice_items ii ON i.id = ii.inv_id
+ GROUP BY i.id;
+
+CREATE UNIQUE INDEX ON mv_invoices(id);
+
+SELECT * FROM mv_invoices ORDER BY id;
+
+-- Modify items for invoice 1
+INSERT INTO invoice_items VALUES (1, 25);
+-- Modify items for invoice 2
+INSERT INTO invoice_items VALUES (2, 50);
+
+-- Refresh only invoice 1
+REFRESH MATERIALIZED VIEW mv_invoices WHERE id = 1;
+
+-- Verify: Invoice 1 updated (175), Invoice 2 stale (200)
+SELECT * FROM mv_invoices ORDER BY id;
+
+-- Refresh invoice 2 concurrently
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_invoices WHERE id = 2;
+-- Verify: Invoice 2 updated (250)
+SELECT * FROM mv_invoices ORDER BY id;
+
+DROP MATERIALIZED VIEW mv_invoices;
+DROP TABLE invoice_items;
+DROP TABLE invoices;
+
+--
+-- Test 5: Rows entering/leaving view scope
+--
+
+CREATE TABLE items (id int, status text, val int);
+INSERT INTO items VALUES (1, 'active', 10), (2, 'inactive', 20);
+
+CREATE MATERIALIZED VIEW mv_active_items AS
+ SELECT * FROM items WHERE status = 'active';
+
+CREATE UNIQUE INDEX ON mv_active_items(id);
+
+SELECT * FROM mv_active_items ORDER BY id;
+
+-- Case A: Row changes status active -> inactive (should be removed)
+UPDATE items SET status = 'inactive' WHERE id = 1;
+-- Also update row 2 to active (should be added)
+UPDATE items SET status = 'active' WHERE id = 2;
+
+-- Refresh partial WHERE id=1
+-- Should remove id=1 because it no longer matches view definition
+REFRESH MATERIALIZED VIEW mv_active_items WHERE id = 1;
+SELECT * FROM mv_active_items ORDER BY id;
+
+-- Case B: Refresh to add row 2 (which is now active)
+REFRESH MATERIALIZED VIEW mv_active_items WHERE id = 2;
+SELECT * FROM mv_active_items ORDER BY id;
+
+-- Cleanup
+DROP MATERIALIZED VIEW mv_active_items;
+DROP TABLE items;
+
+--
+-- Test 6: Order of Operations (Value Swap)
+-- Addressed specific worry: "The order of tuple processing matters"
+--
+
+CREATE TABLE mv_swap_base (id int primary key, code text);
+INSERT INTO mv_swap_base VALUES (1, 'A'), (2, 'B');
+
+CREATE MATERIALIZED VIEW mv_swap AS SELECT * FROM mv_swap_base;
+CREATE UNIQUE INDEX ON mv_swap(code); -- Unique Index is on code, not ID
+
+SELECT * FROM mv_swap ORDER BY id;
+
+-- Perform a swap in the base table
+-- 1 becomes B, 2 becomes A
+BEGIN;
+UPDATE mv_swap_base SET code = 'TEMP' WHERE id = 1;
+UPDATE mv_swap_base SET code = 'A' WHERE id = 2;
+UPDATE mv_swap_base SET code = 'B' WHERE id = 1;
+COMMIT;
+
+-- Refresh both rows concurrently.
+-- If the implementation inserts (1, 'B') before deleting (2, 'B'), this will fail.
+-- It relies on the implementation correctly handling the Delete/Lock set before the Insert/Upsert.
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_swap WHERE id IN (1, 2);
+
+SELECT * FROM mv_swap ORDER BY id;
+
+DROP MATERIALIZED VIEW mv_swap;
+DROP TABLE mv_swap_base;
+
+--
+-- Test 7: Scope Drift / Constraint Violation
+-- Addressed specific worry: "If WHERE predicate would be different... UK violation couldn't be solved"
+--
+
+CREATE TABLE mv_drift_base (id int primary key, category_id int);
+INSERT INTO mv_drift_base VALUES (1, 100), (2, 200);
+
+CREATE MATERIALIZED VIEW mv_drift AS SELECT * FROM mv_drift_base;
+-- KEY FIX: Index on ID, not Category.
+-- We want to test that the Refresh logic detects ID conflicts when rows drift into scope,
+-- not that Postgres enforces unique indexes on non-unique data.
+CREATE UNIQUE INDEX ON mv_drift(id);
+
+-- Update Row 1 to collide with Row 2's category
+UPDATE mv_drift_base SET category_id = 200 WHERE id = 1;
+
+-- Attempt to refresh using the NEW category value as the filter.
+-- The View still contains (1, 100).
+-- The Filter "category_id = 200" sees the NEW row (1, 200) in the base table.
+-- The Filter "category_id = 200" does NOT see the OLD row (1, 100) in the View.
+-- Result: The system thinks (1, 200) is a brand new row and tries to INSERT it.
+-- This MUST fail with a Unique Constraint Violation on 'id' because (1, 100) was never deleted.
+\set VERBOSITY terse
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_drift WHERE category_id = 200;
+\set VERBOSITY default
+
+-- Correct usage: Scope must include BOTH the Old location (100) and New location (200)
+-- so the system sees the update as an update (or delete+insert).
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_drift WHERE category_id IN (100, 200);
+
+SELECT * FROM mv_drift ORDER BY id;
+
+DROP MATERIALIZED VIEW mv_drift;
+DROP TABLE mv_drift_base;
+
+--
+-- Test 8: Multiple Unique Keys
+-- Addressed specific worry: "what if we have multiple UKs?"
+--
+
+CREATE TABLE mv_multi_uk (id int primary key, email text, username text);
+INSERT INTO mv_multi_uk VALUES (1, 'a@example.com', 'user_a');
+
+CREATE MATERIALIZED VIEW mv_multi AS SELECT * FROM mv_multi_uk;
+CREATE UNIQUE INDEX ON mv_multi(email);
+CREATE UNIQUE INDEX ON mv_multi(username);
+
+-- Update all columns
+UPDATE mv_multi_uk SET email = 'b@example.com', username = 'user_b' WHERE id = 1;
+
+-- Refresh should succeed updating all unique indexes
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_multi WHERE id = 1;
+
+SELECT * FROM mv_multi;
+
+DROP MATERIALIZED VIEW mv_multi;
+DROP TABLE mv_multi_uk;
+
+--
+-- Test 9: Trigger-based Automatic Maintenance
+-- Use Case: Automating the partial refresh via triggers using Arrays.
+--
+
+CREATE TABLE mv_trigger_base (id int primary key, val text);
+CREATE MATERIALIZED VIEW mv_trigger_view AS SELECT * FROM mv_trigger_base;
+CREATE UNIQUE INDEX ON mv_trigger_view(id);
+
+-- Create a maintainer function
+CREATE OR REPLACE FUNCTION maintain_mv_trigger_view() RETURNS TRIGGER AS $$
+BEGIN
+ IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
+ EXECUTE 'REFRESH MATERIALIZED VIEW mv_trigger_view WHERE id = ANY($1);'
+ USING (SELECT array_agg(id) FROM new_table);
+ END IF;
+
+ IF (TG_OP IN ('DELETE')) THEN
+ EXECUTE 'REFRESH MATERIALIZED VIEW mv_trigger_view WHERE id = ANY($1);'
+ USING (SELECT array_agg(id) FROM old_table);
+ END IF;
+
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql VOLATILE;
+
+-- Trigger for Insert
+CREATE TRIGGER t_refresh_mv_ins
+ AFTER INSERT ON mv_trigger_base
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH STATEMENT
+EXECUTE FUNCTION maintain_mv_trigger_view();
+
+-- Trigger for Update
+CREATE TRIGGER t_refresh_mv_upd
+ AFTER UPDATE ON mv_trigger_base
+ REFERENCING NEW TABLE AS new_table
+ FOR EACH STATEMENT
+EXECUTE FUNCTION maintain_mv_trigger_view();
+
+-- Trigger for Delete
+CREATE TRIGGER t_refresh_mv_del
+ AFTER DELETE ON mv_trigger_base
+ REFERENCING OLD TABLE AS old_table
+ FOR EACH STATEMENT
+EXECUTE FUNCTION maintain_mv_trigger_view();
+
+-- 1. Test Insert
+INSERT INTO mv_trigger_base VALUES (1, 'Auto-Insert'), (2, 'Auto-Insert');
+SELECT * FROM mv_trigger_view ORDER BY id;
+
+-- 2. Test Update
+UPDATE mv_trigger_base SET val = 'Auto-Update' WHERE id = 1;
+SELECT * FROM mv_trigger_view ORDER BY id;
+
+-- 3. Test Delete
+DELETE FROM mv_trigger_base WHERE id = 2;
+SELECT * FROM mv_trigger_view ORDER BY id;
+
+-- 4. Verify Transaction Isolation
+-- Ensure that if the main transaction rolls back, the Refresh also rolls back
+BEGIN;
+INSERT INTO mv_trigger_base VALUES (99, 'Rollback');
+SELECT * FROM mv_trigger_view WHERE id = 99; -- Should see it
+ROLLBACK;
+SELECT * FROM mv_trigger_view WHERE id = 99; -- Should NOT see it
+
+-- Cleanup
+DROP MATERIALIZED VIEW mv_trigger_view;
+DROP TABLE mv_trigger_base;
+DROP FUNCTION maintain_mv_trigger_view();
--
2.34.1