Subject: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

From: Adam Brusselback <[email-masked]>
Date: Mon, 8 Dec 2025 15:58:27 -0500

Attached is a patch implementing support for a WHERE clause in REFRESH
MATERIALIZED VIEW.

The syntax allows for targeted refreshes:
    REFRESH MATERIALIZED VIEW mv WHERE invoice_id = ANY('{1,2,3}');
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv WHERE customer_id = 42;
    REFRESH MATERIALIZED VIEW mv WHERE order_date >= '2023-01-01';

I was inspired to implement this feature after watching the Hacking
Postgres discussion on the topic:
https://www.youtube.com/watch?v=6cZvHjDrmlQ

This allows the user to restrict the refresh operation to a subset of the
view. The qualification is applied to the view's output columns. The
optimizer can then push this condition down to the underlying base tables,
avoiding a full scan when only a known subset of data has changed.

Implementation notes:

1. The grammar accepts an optional WHERE clause. We forbid volatile
functions in the clause to ensure correctness.

2. Non-Concurrent Partial Refresh: When `CONCURRENTLY` is not specified,
the operation performs an in-place modification using a `ROW EXCLUSIVE`
lock.
    *   This mode requires a unique index to ensure constraint violations
are handled correctly (e.g., when a row's values change such that it
"drifts" into or out of the `WHERE` clause scope).
    *   It executes a Prune + Upsert strategy:
        * `DELETE` all rows in the materialized view that match the `WHERE`
clause.
        * `INSERT` the new data from the source query.
    *   It uses `ON CONFLICT DO UPDATE` during the insert phase to handle
concurrency edge cases, ensuring the refresh is robust against constraint
violations.

3. Concurrent Partial Refresh: When `CONCURRENTLY` is specified, it uses
the existing diff/merge infrastructure (`refresh_by_match_merge`), limiting
the scope of the diff (and the temporary table population) to the rows
matching the predicate. This requires an `EXCLUSIVE` lock and a unique
index, consistent with existing concurrent refresh behavior. It is much
slower than `Non-Concurrent Partial Refresh`

4. The execution logic uses SPI to inject the predicate into the source
queries during execution.

I have attached a benchmark suite to validate performance and correctness:

*   `setup.sql`: Creates a schema `mv_benchmark` modeling an invoicing
system (`invoices` and `invoice_lines`). It includes an aggregated
materialized view (`invoice_summary`) and a control table
(`invoice_summary_table`).
*   `workload_*.sql`: pgbench scripts simulating a high-churn environment
(45% inserts, 10% updates, 45% deletes) to maintain roughly stable dataset
sizes while generating significant refresh work.
*   `run_benchmark_comprehensive.sh`: Orchestrates the benchmark across
multiple scale factors and concurrency levels.

The benchmark compares strategies for keeping a summary up to date (vs
baseline):
*   Partial Refresh: Triggers on the base table collect modified IDs and
execute `REFRESH MATERIALIZED VIEW ... WHERE ...`.
*   Materialized Table (Control): A standard table maintained via complex
PL/pgSQL triggers (the traditional manual workaround).
*   Full Refresh (Legacy): Manually refresh the view after changes.

Results are below:
Concurrency: 1 client(s)
----------------------------------------------------------------------------------
Scale       Batch | Baseline TPS | Full (Rel)   Partial (Rel) Table (Rel)
---------- ------ | ------------ | ------------ ------------ ------------
20000           1 | 5309.05      | 0.002x        0.437x        0.470x
20000          50 | 1209.32      | 0.010x        0.600x        0.598x
20000        1000 | 56.05        | 0.164x        0.594x        0.576x
400000          1 | 5136.91      | 0    x        0.450x        0.487x
400000         50 | 1709.17      | 0    x        0.497x        0.482x
400000       1000 | 110.35       | 0.006x        0.507x        0.460x

Concurrency: 4 client(s)
----------------------------------------------------------------------------------
Scale       Batch | Baseline TPS | Full (Rel)   Partial (Rel) Table (Rel)
---------- ------ | ------------ | ------------ ------------ ------------
20000           1 | 19197.50     | 0x            0.412x        0.435x
20000          50 | 1016.14      | 0.007x        0.966x        1.036x
20000        1000 | 9.94         | 0.708x        1.401x        1.169x
400000          1 | 19637.36     | 0x            0.436x        0.483x
400000         50 | 4669.32      | 0x            0.574x        0.566x
400000       1000 | 23.26        | 0.029x        1.147x        0.715x

Concurrency: 8 client(s)
----------------------------------------------------------------------------------
Scale       Batch | Baseline TPS | Full (Rel)   Partial (Rel) Table (Rel)
---------- ------ | ------------ | ------------ ------------ ------------
20000           1 | 30358.32     | 0x            0.440x        0.457x
20000          50 | 262.75       | 0.026x        2.943x        2.740x
20000        1000 | 11.28        | 0.575x        0.840x        0.578x
400000          1 | 36007.15     | 0x            0.430x        0.464x
400000         50 | 6664.58      | 0x            0.563x        0.494x
400000       1000 | 11.61        | 0.058x        1.000x        1.277x

In these tests, the partial refresh behaves as O(delta) rather than
O(total), performing comparably to the manual PL/pgSQL approach but with
significantly lower code complexity for the user.

I recognize that adding a WHERE clause to REFRESH is an extension to the
SQL standard. I believe the syntax is intuitive, but I am open to
discussion regarding alternative implementation strategies or syntax if the
community feels a different approach is warranted.

New regression tests are included in the patch.

This is my first time submitting a patch to PostgreSQL, so please bear with
me if I've missed anything or made any procedural mistakes. I'm happy to
address any feedback.

Thanks,
Adam Brusselback
From 599d6a8f3d48aed56f960b1e4beba37aeeba0a71 Mon Sep 17 00:00:00 2001
From: Adam Brusselback <[email-masked]>
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 &lt; '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 = &params->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 = &params->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, [email-masked]', '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 = [email-masked]', 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 | [email-masked] | 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, [email-masked]', '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 = [email-masked]', 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

---

From: Kirk Wolak <[email-masked]>
Date: Tue, 9 Dec 2025 00:08:58 -0500

On Mon, Dec 8, 2025 at 3:58 PM Adam Brusselback <[email-masked]>
wrote:

> +1 (But I was in that hacking session).

  Our situation was a wonderful MV with all the columns we needed (some
hard to calculate) to augment search data done millions of times/day.  It
was a thing of beauty.  Until we realized we needed to update 1 record
(vendor inventory UPDATE date/time) every time we processed a file
(something we do 24x7, a hundred times each hour!

  For that ONE field, we ended up doing REFRESH MV concurrently;  OVER
2,000 times per day.

  Our understanding is that many people run into this exact issue.  The
cache needs small frequent updates.
(After reading the code that handles MVs, we just created our own TABLE,
and maintain it with a scheduler to rebuild HOURLY,
and when we process the file, a Simple UPDATE is issued for the one column).

  While this "Works", the CONCEPT of this patch (untested by me, as of
yet), would have fixed this with far less effort,
and would be easier to maintain.

  After I review the code, I will add additional comments.

  I am curious what others think?  (And FWIW, I believe that the larger the
MV, the MORE this feature is needed,
vs refreshing the ENTIRE view).

Regards...

---

From: Nico Williams <[email-masked]>
Date: Mon, 8 Dec 2025 23:35:42 -0600

> vs refreshing the ENTIRE view).

What I do is I have my own materialized view infrastructure, written
entirely in PlPgSQL, and I completely avoid PG's MV support.  This
alternative MV scheme creates an actual table for each MV, which means:

 - one can update the MV directly (and I do, via triggers)

 - one can have triggers on the MV (e.g., to record history)

This is has been very handy for me.

I also have a state table in which to keep track of whether an MV needs
a refresh, and I have a function i can use to mark an MV as needing a
refresh.  Marking an MV as needing a refresh sends a NOTIFY, and then I
have a daemon that will refresh views as needed (with some
debouncing/coalescing of notifications).

This way I can have MVs with very complex underlying queries for which
some kinds of updates I can easily write fast triggers for and others
where I can't (or where they would slow down transactions too much) I
simply mark the MV as needing a refresh.

Typical MV queries I have that this works very well for include
transitive reachability closure computations (e.g., all the groups a
thing is a member of, directly and indirectly, or vice versa --
recursive CTEs basically).  Though I do now have triggers that can do a
reasonably good job of synchronously and quickly updating MVs with such
queries, it's just I didn't always.

Refreshes are always 'concurrent'.

This is my 80/20 solution to the "Incremental View Maintenance" (IVM)
problem.

A not very current version is here:

https://github.com/twosigma/postgresql-contrib/blob/master/mat_views.sql

If you like it I might be able to get a newer version out.  The version
above has a few minor issues:

 - it uses DELETE FROM instead of TRUNCATE for its' sort-of temp tables

 - using TRUNCATE ends up requiring some care to avoid occasional
   deadlocks with VACUUM that are due to using tables as types of the
   columns of the deltas tables

 - logging -- lots of logging in the newest version

Another issue is that I rely on NATURAL FULL OUTER JOIN to avoid having
to generate ON conditions, but that means that all columns of the
underlying VIEW must not have NULLs.  As I've not needed to support
nullable columns in these MVs, I don't mind.

Nico
--

---

From: Adam Brusselback <[email-masked]>
Date: Tue, 9 Dec 2025 11:27:58 -0500

>   For that ONE field, we ended up doing REFRESH MV concurrently;  OVER

Thanks for the feedback and the use case, Kirk.

Regarding that specific scenario where a single column ("last updated" or
similar) churns significantly faster than the heavy-computation columns:
Even with this patch, you might find it beneficial to separate that
high-velocity column into its own small materialized view (or regular view)
and join it to the main MV at query time. That will reduce the bloat you
get on the main MV by quite a lot, especially if you have very wide rows
(which it seems like you do).

I initially tried to implement logic that would allow for direct UPDATEs
(which would enable HOT updates). However, to handle rows that matched the
predicate but were no longer present in the new source data, I had to run
an anti-join to identify them for deletion. That approach caused
performance issues, so I settled on the "Prune + Upsert" strategy (DELETE
matching rows, then INSERT from source).

Because this patch performs a delete/insert cycle, updating that one
timestamp column will still result in rewriting the whole tuple in the MV.

>   For that ONE field, we ended up doing REFRESH MV concurrently;  OVER

That said, 2,000 refreshes per day is nothing for this implementation,
provided your updates are selective enough and your queries allow for
predicate push-down to the base tables.

I look forward to your thoughts after reviewing the code.

Thanks,
Adam Brusselback

---

From: Adam Brusselback <[email-masked]>
Date: Mon, 23 Mar 2026 10:23:43 -0400

Hi Dharin, thanks for the review.

> 1. indnatts vs indnkeyatts

Good catch. Will fix.

> 2. Subqueries -> Error

That comment is wrong, I never added a check for that because it turned out
to be unnecessary. Will remove.

> 3. Concurrency gap / safety model

To answer your questions directly:

1. The goal is to be safe against concurrent partial refreshes on
overlapping rows, not just concurrent DML on base tables.
2. The intent is maintenance-like and safe by default.

Because we lose the physical lock on the row after the DELETE, I plan to
enforce that safety default via transaction-level advisory locks acquired
before the DELETE with somethin like:

    SELECT pg_advisory_xact_lock(matviewOid, hashtext(ROW(key_cols)::text))
    FROM matview
    WHERE (condition);

Concurrent refreshes on the same logical rows will serialize while
non-overlapping rows still run in parallel.

This also made me think about whether the CONCURRENTLY keyword is doing the
right thing here. Here's how the guarantees break down across all the
refresh modes:

Refresh Command / State                 | Base Table Lock  | Concurrent
Reads? | Concurrent Writes? | Same-Row Concurrent Refreshes
----------------------------------------+------------------+-------------------+--------------------+------------------------------
Standard Full Refresh                   | ACCESS EXCLUSIVE | Blocked
    | Blocked            | Blocked (Table Level)
CONCURRENTLY (Full)                     | EXCLUSIVE        | Allowed
    | Blocked            | Blocked (Table Level)
Partial (WHERE) - Current Patch         | ROW EXCLUSIVE    | Allowed
    | Allowed            | Race condition (Fails)
Partial (WHERE) - With Advisory Locks   | ROW EXCLUSIVE    | Allowed
    | Allowed            | Serialized (Waits)
Partial (CONCURRENTLY WHERE)            | EXCLUSIVE        | Allowed
    | Blocked            | Serialized (Waits)

Because of this, the `CONCURRENTLY` distinction gets inverted with a
`WHERE` clause. With a full refresh, `CONCURRENTLY` is the more permissive
option (allowing readers). But here, the bare `WHERE` path allows both
reads and writes, while `CONCURRENTLY WHERE` blocks writers. Non-concurrent
ends up being the more permissive option, which goes against what the
keyword generally implies.

One option is to swap the two implementations to restore that intuition.
`CONCURRENTLY WHERE` becomes the advisory locks approach (maximum
throughput), and bare `WHERE` becomes the diff approach (conservative,
blocks writers). On the other hand, `CONCURRENTLY` has historically meant
the diff-based algorithm specifically, not just a lower lock level.

I don't have a strong opinion here and would rather let the community
decide. The updated patch will leave the algorithms as-is for now. Happy to
swap them if that's the preferred direction.

Will post an updated patch soon.

Thanks,
Adam Brusselback

---

From: Adam Brusselback <[email-masked]>
Date: Thu, 9 Apr 2026 13:32:59 -0400

Hi Dharin,

Attached is an updated patch addressing your review comments and fixing the
concurrency model for the non-concurrent path.

Regarding the items you brought up:
1. Fixed the indnatts vs indnkeyatts issue for generating the ON CONFLICT
target.
2. Removed the incorrect regression test comment regarding subqueries.

Regarding the concurrency gap and safety model:
In my last email, I mentioned planning to use transaction-level advisory
locks to fix the consistency gap. After prototyping it, I had to abandon
that approach. Testing revealed that it falls over at scale, quickly
hitting `max_locks_per_transaction` limits and causing issues with bulk
operations. I worked on this for a while before deciding it wasn't workable.

Instead, I went a different direction. The non-concurrent partial refresh
now uses a different two-step strategy:

1. It first executes a `SELECT FROM mv WHERE ... FOR UPDATE` to lock
existing rows matching the predicate. This serializes concurrent partial
refreshes on overlapping rows while allowing non-overlapping refreshes to
proceed in parallel.
2. It then executes a single CTE that evaluates the underlying query,
upserts the results into the matview, and deletes rows that no longer match
the predicate via an anti-join.

In my testing, this approach had similar performance to the original
implementation, but hasn't exhibited the same correctness issues.

Thanks,
Adam Brusselback

---

From: vellaipandiyan sm <[email-masked]>
Date: Thu, 21 May 2026 10:47:55 +0530

Hello hackers,

I reviewed the REFRESH MATERIALIZED VIEW ... WHERE patch and had a few
questions around concurrency semantics.

- The original DELETE -> INSERT approach exposing a consistency gap makes
sense, especially once tuple locks disappear after DELETE. The newer FOR
UPDATE + single-CTE approach seems safer, though I wonder whether
overlapping refreshes could still encounter deadlock scenarios around
UPSERT conflicts.

- The CONCURRENTLY behavior also feels somewhat unintuitive here. With
WHERE refreshes, the non-CONCURRENT path appears more permissive for
writers than CONCURRENTLY WHERE, which seems opposite to the expectation
established by normal REFRESH MATERIALIZED VIEW semantics.

- It may also help to document the intended guarantees around overlapping
partial refreshes and concurrent DML on base tables.

Overall, the use case seems quite valuable for selective high-churn refresh
workloads.

Thanks for working on this patch.

Regards,
Vellaipandiyan

On Thu, May 21, 2026 at 10:44 AM Adam Brusselback <[email-masked]>
wrote:

>         * `DELETE` all rows in the materialized view that match the
> `WHERE` clause.
> This is my first time submitting a patch to PostgreSQL, so please bear
> with me if I've missed anything or made any procedural mistakes. I'm happy
> to address any feedback.
>
> Thanks,
> Adam Brusselback
>

---

From: Adam Brusselback <[email-masked]>
Date: Tue, 26 May 2026 14:53:28 -0400

Hi Vellaipandiyan, thanks for the review.

> I wonder whether overlapping refreshes could still encounter deadlock
> scenarios around UPSERT conflicts.

That was definitely a gap. The FOR UPDATE step previously issued:

    SELECT 1 FROM mv WHERE (...) FOR UPDATE

with no ORDER BY, so two overlapping refreshes could lock the existing
rows in different physical orders and deadlock before either one reached
the next statement.

To fix this, the next patch gives the locking SELECT a deterministic
ORDER BY on the unique key columns so every refresh acquires row locks
in the same sequence. The upsert now feeds from an ordered source as
well.

> The CONCURRENTLY behavior also feels somewhat unintuitive here.

I'll include this change in the next patch. After more thinking about
it (and some feedback), I decided that going this way seems better. So any
prior discussion about CONCURRENTLY vs without is now talking about the
inverse of the current version of the patch.

Additionally, I've fixed a "scope drift" issue where the two different
implementations
had inconsistent behavior. A row's non-key column changes in the base
table, shifting it
into the WHERE predicate's scope. Previously, this caused a unique
constraint
violation for the match_merge path because the old row was invisible to the
filter
and never deleted. This was previously handled by the direct_mod path
properly.

Now, both the direct-modification and match/merge paths resolve this by
using an INSERT ... ON CONFLICT DO UPDATE step (or DO NOTHING if there
are no non-key columns) against the arbiter index. This safely updates
the out-of-scope stale row in place without duplicating the key.

> It may also help to document the intended guarantees around overlapping

Here is my attempt at saying what the implementation actually guarantees;
I will fit this into the docs in some way. If anyone notices something
wrong with the below, please speak up.

Concurrent Partial Refresh (direct_mod path):
The FOR UPDATE / upsert-CTE path takes RowExclusiveLock. Readers are
never blocked. Neither RowExclusiveLock nor the per-row FOR UPDATE
locks conflict with a plain SELECT. The refresh issues two statements
in one transaction: the locking SELECT, then the select/upsert/
anti-join-delete CTE. Under the default READ COMMITTED, these take
separate snapshots, but the FOR UPDATE row locks are held to transaction
end, so they bridge both statements. An overlapping refresh blocks on
those locks for the full duration of the refreshing transaction, not
just for the locking SELECT, so the gap between the two statements does
not expose existing MV rows. The CTE's upsert and delete are a single
statement, so a reader sees either the pre-refresh or post-refresh
state of an affected row, never a half-applied one.

Concurrent partial refreshes whose predicates touch overlapping existing
MV rows are serialized. The second waits on the first's FOR UPDATE
locks. Refreshes over disjoint row sets run in parallel. Within the
predicate scope, the MV is made consistent with the query snapshot: rows
present in the snapshot are upserted, rows that no longer appear are
deleted via the anti-join.

Rows whose key falls outside the predicate are not touched. Note this
is keyed on the unique index, not on the predicate. As mentioned with
the drift fix, if the predicate matches a fresh row whose key collides
with an existing MV row that does not currently match the predicate,
ON CONFLICT will update that existing row, and the step-one FOR UPDATE
will not have locked it. This only arises when the predicate references
non-key columns. When the predicate ranges over the unique-key columns,
the colliding row necessarily matches too.

It does not lock the base tables. Base-table DML committed after the
refresh's snapshot is not reflected, identical to a normal full REFRESH.

SELECT FOR UPDATE only serializes overlapping refreshes covering rows
that already exist in the MV. Two refreshes that both insert the same
new logical key are serialized by ON CONFLICT and the unique index, not
by FOR UPDATE. The outcome is still correct. The last writer wins on
that key.

The predicate must be non-volatile (enforced) and a usable unique index
is required (enforced).

The above assumes READ COMMITTED. I haven't thought through how things
will work with other isolation levels.

Non-Concurrent Partial Refresh (match_merge path):
The WHERE diff/merge path takes an ExclusiveLock and operates similarly
to the standard full-refresh diff/merge, but with the diff scope
restricted to rows matching the predicate. Readers are allowed, writers
are blocked, and overlapping refreshes are serialized at the table
level. The main difference from a full concurrent refresh is that its
final insert uses ON CONFLICT DO UPDATE (just like the direct_mod path)
specifically to resolve unique key violations caused by rows drifting
into the predicate's scope.

I will provide a new patch shortly.

Thanks,
Adam Brusselback

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 27 May 2026 17:04:53 -0700

Hello!

The patch in its current form has a security escalation bug, WHERE
functions are executed with the privileges of the owner, not the
maintainer. As one example, see the following script demonstrates
unprivileged write, but reads are also of course possible:

CREATE ROLE mvowner;
CREATE ROLE lowpriv;

CREATE SCHEMA atk AUTHORIZATION lowpriv;
CREATE TABLE loot (note text);
CREATE MATERIALIZED VIEW mv AS SELECT 1 AS id;
CREATE UNIQUE INDEX ON mv (id);

ALTER TABLE loot OWNER TO mvowner;
ALTER MATERIALIZED VIEW mv OWNER TO mvowner;
GRANT MAINTAIN ON mv TO lowpriv;

SET ROLE lowpriv;
GRANT USAGE ON SCHEMA atk TO mvowner;
CREATE FUNCTION atk.w() RETURNS void LANGUAGE plpgsql VOLATILE AS
$$ BEGIN INSERT INTO public.loot VALUES ('written by ' || current_user); END $$;
CREATE FUNCTION atk.p(int) RETURNS boolean LANGUAGE plpgsql STABLE AS
$$ BEGIN PERFORM atk.w(); RETURN true; END $$;

REFRESH MATERIALIZED VIEW mv WHERE atk.p(id);
RESET ROLE;

SELECT DISTINCT note FROM loot;

Only maintain permission + write access to any schema is required for it.

There's also another issue where an error during refresh removes the
modification restrictions:

CREATE TABLE base (id int, code int, val text);
INSERT INTO base VALUES (1, 100, 'a'), (2, 200, 'b'), (3, 300, 'c');
CREATE MATERIALIZED VIEW mv AS SELECT id, code, val FROM base;
CREATE UNIQUE INDEX mv_code_uq ON mv (code);
-- fails as it should
DELETE FROM mv WHERE id = 1;
-- will fail, as it should
UPDATE base SET code = 999 WHERE id IN (1, 2);
REFRESH MATERIALIZED VIEW mv WHERE id <= 2;
-- succeeds, but it shouldn't
DELETE FROM mv WHERE id = 1;
-- we can also insert now
INSERT INTO mv (id, code, val) VALUES (42, 4242, 'injected');

---

From: Adam Brusselback <[email-masked]>
Date: Thu, 28 May 2026 22:53:15 -0400

Hi Zsolt, and hackers,

On the privilege escalation: Yup, that isn't good. Thank you for catching
that.
In both paths the predicate is concatenated directly into the SQL that
evaluates the view, e.g. in refresh_by_direct_modification:
    SELECT * FROM (<view definition>) mv WHERE (<predicate>)

and in refresh_by_match_merge:
    SELECT ctid, * FROM <matview> WHERE (<predicate>)

There's one plan, executed under one userid. I can't run the (<view
definition>) subquery as the owner and the WHERE (<predicate>) as the
invoker, SPI executes the whole statement in whatever security context is
active when it runs.

So the predicate runs as the owner. The levers left are what the predicate
may contain and who may run it. Here is what I was thinking:
- Predicate functions all leakproof: allow for anyone with the privilege to
refresh today (MAINTAIN or owner). A leakproof predicate in owner context
can't leak the owner's data or do anything the invoker couldn't, so nothing
escalates.
- Predicate contains a non-leakproof function: require ownership (or
superuser). Invoker and owner are then the same trust domain, so
owner-context execution doesn't escalate.

This keeps MAINTAIN working for the common case, predicates over columns
with built-in operators. The tightening only hits custom non-leakproof
predicate functions.

If anyone else has better ideas, i'm all ears.

Your second issue is due to a missing PG_TRY around the
OpenMatViewIncrementalMaintenance()/Close pair in the direct-modification
path (the match/merge site already handles it). An error between open and
close goes past the close and leaves matview_maintenance_depth above zero
for the session, which is what lets plain DELETE/INSERT through afterward.
Will fix.

Thanks,
Adam