public inbox for [email protected]
help / color / mirror / Atom feed[Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
10+ messages / 5 participants
[nested] [flat]
* [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2025-12-08 20:58 Adam Brusselback <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Adam Brusselback @ 2025-12-08 20:58 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
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
Attachments:
[application/zip] benchmark.zip (7.1K, 3-benchmark.zip)
download
[text/x-patch] 001_implement_mat_view_where.patch (61.8K, 4-001_implement_mat_view_where.patch)
download | inline diff:
From 599d6a8f3d48aed56f960b1e4beba37aeeba0a71 Mon Sep 17 00:00:00 2001
From: Adam Brusselback <[email protected]>
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, '[email protected]', '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 protected]', 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 protected] | 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 protected]', '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 protected]', 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2025-12-09 05:08 Kirk Wolak <[email protected]>
parent: Adam Brusselback <[email protected]>
1 sibling, 2 replies; 10+ messages in thread
From: Kirk Wolak @ 2025-12-09 05:08 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; +Cc: Adam Brusselback <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrey Borodin <[email protected]>
On Mon, Dec 8, 2025 at 3:58 PM Adam Brusselback <[email protected]>
wrote:
> 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
>
> +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...
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2025-12-09 05:35 Nico Williams <[email protected]>
parent: Kirk Wolak <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Nico Williams @ 2025-12-09 05:35 UTC (permalink / raw)
To: Kirk Wolak <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Adam Brusselback <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrey Borodin <[email protected]>
On Tue, Dec 09, 2025 at 12:08:58AM -0500, Kirk Wolak wrote:
> 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).
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
--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2025-12-09 16:27 Adam Brusselback <[email protected]>
parent: Kirk Wolak <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Adam Brusselback @ 2025-12-09 16:27 UTC (permalink / raw)
To: Kirk Wolak <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
> 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.
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
> 2,000 times per day.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2026-03-23 14:23 Adam Brusselback <[email protected]>
parent: Adam Brusselback <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adam Brusselback @ 2026-03-23 14:23 UTC (permalink / raw)
To: Dharin Shah <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2026-04-09 17:32 Adam Brusselback <[email protected]>
parent: Adam Brusselback <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adam Brusselback @ 2026-04-09 17:32 UTC (permalink / raw)
To: Dharin Shah <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
Attachments:
[application/x-patch] v2-0001-implement_mat_view_where.patch (62.2K, 3-v2-0001-implement_mat_view_where.patch)
download | inline diff:
From 2a2d7ba4a3c68d64717577e47e4dfd6f724e620a Mon Sep 17 00:00:00 2001
From: Adam Brusselback <[email protected]>
Date: Thu, 9 Apr 2026 13:16:34 -0400
Subject: [PATCH v2] 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 | 813 ++++++++++++++++--
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 | 344 ++++++++
src/test/regress/parallel_schedule | 5 +
src/test/regress/sql/matview_where.sql | 302 +++++++
11 files changed, 1468 insertions(+), 70 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 6dbb831ca89..5015562dae7 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 f7d8007f796..1dc7358fa52 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 both the row-locking and refresh steps
+ * avoiding expensive calls (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 lockPlan; /* SELECT FOR UPDATE */
+ SPIPlanPtr refreshPlan; /* Fused CTE: Evaluate -> Upsert -> Delete */
+} 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 uint64 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,23 +158,183 @@ 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;
+ if (stmt->concurrent)
+ lockmode = ExclusiveLock;
+ else if (stmt->whereClause)
+ lockmode = RowExclusiveLock;
+ else
+ lockmode = AccessExclusiveLock;
/*
* Get a lock until end of transaction.
@@ -135,24 +345,40 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
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 a two-step approach
+ * (SELECT FOR UPDATE followed by a CTE upsert/delete).
+ * Uses RowExclusiveLock, allowing concurrent reads and concurrent writes
+ * to non-overlapping rows. Overlapping writes are serialized by row locks.
+ *
+ * 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 but blocking all
+ * concurrent writes.
+ *
+ * 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 +388,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 +432,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 +470,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 +534,83 @@ 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
+ /*
+ * STRATEGY 1: PARTIAL NON-CONCURRENT
+ */
+ if (qual && !concurrent && !skipData)
{
- tableSpace = matviewRel->rd_rel->reltablespace;
- relpersistence = matviewRel->rd_rel->relpersistence;
+ processed = refresh_by_direct_modification(matviewOid, relowner,
+ save_sec_context, qual_str,
+ params);
}
/*
- * 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 2: CONCURRENT (PARTIAL or FULL)
*/
- OIDNewHeap = make_new_heap(matviewOid, tableSpace,
- matviewRel->rd_rel->relam,
- relpersistence, ExclusiveLock);
- Assert(CheckRelationOidLockedByMe(OIDNewHeap, AccessExclusiveLock, false));
-
- /* Generate the data, if wanted. */
- if (!skipData)
+ else if (concurrent)
{
- DestReceiver *dest;
+ Oid tableSpace;
+ char relpersistence;
+ Oid OIDNewHeap;
+ int old_depth = matview_maintenance_depth;
- dest = CreateTransientRelDestReceiver(OIDNewHeap);
- processed = refresh_matview_datafill(dest, dataQuery, queryString,
- is_create);
- }
+ tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
+ relpersistence = RELPERSISTENCE_TEMP;
- /* Make the matview match the newly generated data. */
- if (concurrent)
- {
- int old_depth = matview_maintenance_depth;
+ /*
+ * 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 +618,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,6 +851,355 @@ 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
+ *
+ * This modifies the materialized view in-place without creating a temporary
+ * heap or swapping relfilenumbers. It requires a usable unique index on the
+ * matview for conflict resolution.
+ *
+ * Concurrency is handled in two steps, each executed as a separate SPI
+ * statement:
+ *
+ * 1. Lock existing rows matching the WHERE clause via SELECT FOR UPDATE.
+ * This serializes concurrent partial refreshes that touch overlapping
+ * rows while allowing non-overlapping refreshes to proceed in parallel.
+ *
+ * 2. Execute a single CTE that evaluates the underlying query, upserts
+ * the results into the matview, and deletes rows that no longer match
+ * the predicate (via anti-join against the fresh query output).
+ *
+ * To avoid rebuilding the SQL and re-preparing the SPI plans on every call,
+ * we cache both plans in a session-level hash table keyed by matview OID.
+ *
+ * Returns the number of rows processed by the refresh CTE.
+ */
+static uint64
+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;
+ bool found;
+ uint64 result_processed = 0;
+
+ matviewRel = table_open(matviewOid, NoLock);
+
+ /* Find a usable unique index, preferring the primary key. */
+ 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.")));
+
+ /* Look up or create a plan cache entry for this matview. */
+ if (!MatViewRefreshCache)
+ InitMatViewCache();
+
+ cacheEntry = (MatViewPartialRefreshCache *) hash_search(MatViewRefreshCache,
+ &matviewOid,
+ HASH_ENTER,
+ &found);
+
+ /*
+ * We have a cache hit ONLY if the entry exists, the unique index matches,
+ * and the WHERE clause string perfectly matches. We also ensure
+ * whereClauseStr is not NULL to prevent a strcmp segfault if a previous
+ * compilation failed midway.
+ */
+ if (found &&
+ cacheEntry->uniqueIndexOid == uniqueIndexOid &&
+ cacheEntry->whereClauseStr != NULL &&
+ whereClauseStr != NULL &&
+ strcmp(cacheEntry->whereClauseStr, whereClauseStr) == 0)
+ {
+ /* Cache is valid. Do nothing. */
+ }
+ else
+ {
+ if (found)
+ {
+ /* Index or WHERE clause changed; discard stale plans. */
+ if (cacheEntry->lockPlan)
+ SPI_freeplan(cacheEntry->lockPlan);
+ if (cacheEntry->refreshPlan)
+ SPI_freeplan(cacheEntry->refreshPlan);
+ if (cacheEntry->whereClauseStr)
+ pfree(cacheEntry->whereClauseStr);
+ }
+
+ cacheEntry->lockPlan = NULL;
+ cacheEntry->refreshPlan = NULL;
+ cacheEntry->whereClauseStr = NULL;
+ }
+
+ OpenMatViewIncrementalMaintenance();
+
+ SPI_connect();
+
+ /* Prepare plans if we don't have valid cached ones. */
+ if (cacheEntry->lockPlan == NULL || cacheEntry->refreshPlan == NULL)
+ {
+ StringInfoData buf;
+ char *view_sql;
+ char *matview_name;
+ const char *matview_alias;
+ Oid *argtypes = NULL;
+ int nargs = 0;
+ Relation indexRel;
+ Form_pg_index indexStruct;
+ TupleDesc tupdesc = matviewRel->rd_att;
+ StringInfoData conflict_cols;
+ StringInfoData set_clause;
+ StringInfoData join_clause;
+ bool first;
+ bool has_non_key_cols = false;
+ int i;
+ MemoryContext oldcxt;
+
+ matview_name = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+ matview_alias = quote_identifier(RelationGetRelationName(matviewRel));
+ view_sql = get_matview_view_query(matviewOid);
+
+ if (params && params->numParams > 0)
+ {
+ nargs = params->numParams;
+ argtypes = (Oid *) palloc(nargs * sizeof(Oid));
+ for (i = 0; i < nargs; i++)
+ argtypes[i] = params->params[i].ptype;
+ }
+
+ /*
+ * Prepare the row-locking statement. This acquires FOR UPDATE locks
+ * on matview rows matching the WHERE clause to serialize concurrent
+ * partial refreshes on overlapping rows.
+ */
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "SELECT 1 FROM %s mv WHERE (%s) FOR UPDATE",
+ matview_name, whereClauseStr);
+
+ cacheEntry->lockPlan = SPI_prepare(buf.data, nargs, argtypes);
+ if (cacheEntry->lockPlan == NULL)
+ elog(ERROR, "SPI_prepare failed for lock acquisition: %s", buf.data);
+ SPI_keepplan(cacheEntry->lockPlan);
+
+ /*
+ * Build the refresh CTE: evaluate the underlying query with the WHERE
+ * predicate, upsert the results, and delete matview rows that no
+ * longer appear in the query output.
+ */
+ indexRel = index_open(uniqueIndexOid, AccessShareLock);
+ indexStruct = indexRel->rd_index;
+
+ initStringInfo(&conflict_cols);
+ initStringInfo(&set_clause);
+ initStringInfo(&join_clause);
+
+ /* Build the ON CONFLICT column list and anti-join condition. */
+ first = true;
+ for (i = 0; i < indexStruct->indnkeyatts; i++)
+ {
+ int attnum = indexStruct->indkey.values[i];
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+ const char *quoted;
+
+ quoted = quote_identifier(NameStr(attr->attname));
+
+ if (!first)
+ {
+ appendStringInfoString(&conflict_cols, ", ");
+ appendStringInfoString(&join_clause, " AND ");
+ }
+ first = false;
+
+ appendStringInfoString(&conflict_cols, quoted);
+ appendStringInfo(&join_clause,
+ "nd.%s IS NOT DISTINCT FROM mv.%s",
+ quoted, quoted);
+ }
+
+ /* Build the DO UPDATE SET clause for non-key columns. */
+ first = true;
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+ const char *quoted;
+ bool is_key = false;
+ int j;
+
+ if (attr->attisdropped)
+ continue;
+
+ for (j = 0; j < indexStruct->indnkeyatts; 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;
+
+ quoted = quote_identifier(NameStr(attr->attname));
+ appendStringInfo(&set_clause, "%s = EXCLUDED.%s", quoted, quoted);
+ }
+
+ index_close(indexRel, NoLock);
+
+ resetStringInfo(&buf);
+
+ if (has_non_key_cols)
+ {
+ appendStringInfo(&buf,
+ "WITH new_data AS MATERIALIZED ( "
+ " SELECT * FROM (%s) %s WHERE (%s) "
+ "), "
+ "upsert AS ( "
+ " INSERT INTO %s SELECT * FROM new_data "
+ " ON CONFLICT (%s) DO UPDATE SET %s "
+ ") "
+ "DELETE FROM %s mv WHERE (%s) AND NOT EXISTS ( "
+ " SELECT 1 FROM new_data nd WHERE %s"
+ ")",
+ view_sql, matview_alias, whereClauseStr,
+ matview_name, conflict_cols.data, set_clause.data,
+ matview_name, whereClauseStr, join_clause.data);
+ }
+ else
+ {
+ appendStringInfo(&buf,
+ "WITH new_data AS MATERIALIZED ( "
+ " SELECT * FROM (%s) %s WHERE (%s) "
+ "), "
+ "upsert AS ( "
+ " INSERT INTO %s SELECT * FROM new_data "
+ " ON CONFLICT (%s) DO NOTHING "
+ ") "
+ "DELETE FROM %s mv WHERE (%s) AND NOT EXISTS ( "
+ " SELECT 1 FROM new_data nd WHERE %s"
+ ")",
+ view_sql, matview_alias, whereClauseStr,
+ matview_name, conflict_cols.data,
+ matview_name, whereClauseStr, join_clause.data);
+ }
+
+ cacheEntry->refreshPlan = SPI_prepare(buf.data, nargs, argtypes);
+ if (cacheEntry->refreshPlan == NULL)
+ elog(ERROR, "SPI_prepare failed for refresh CTE: %s", buf.data);
+ SPI_keepplan(cacheEntry->refreshPlan);
+
+ /* Save cache metadata in a long-lived context. */
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ cacheEntry->uniqueIndexOid = uniqueIndexOid;
+ cacheEntry->whereClauseStr = pstrdup(whereClauseStr);
+ MemoryContextSwitchTo(oldcxt);
+
+ pfree(matview_name);
+ pfree(view_sql);
+ pfree(buf.data);
+ pfree(conflict_cols.data);
+ pfree(set_clause.data);
+ pfree(join_clause.data);
+ if (argtypes != NULL)
+ pfree(argtypes);
+ }
+
+
+ /* Execute: lock matching rows, then run the refresh CTE. */
+ if (matview_execute_spi_plan(cacheEntry->lockPlan, params, false) < 0)
+ elog(ERROR, "SPI_execute_plan failed during lock acquisition");
+
+ if (matview_execute_spi_plan(cacheEntry->refreshPlan, params, false) < 0)
+ elog(ERROR, "SPI_execute_plan failed during refresh");
+
+ result_processed = SPI_processed;
+
+ SPI_finish();
+ CloseMatViewIncrementalMaintenance();
+ table_close(matviewRel, NoLock);
+
+ return result_processed;
+}
+
/*
* refresh_by_match_merge
*
@@ -572,6 +1217,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 +1238,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 +1353,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 +1483,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);
/*
@@ -898,7 +1584,8 @@ refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence)
}
/*
- * Check whether specified index is usable for match merge.
+ * Check whether the specified index is usable for refresh_by_match_merge
+ * or refresh_by_direct_modification.
*/
static bool
is_usable_unique_index(Relation indexRel)
@@ -946,8 +1633,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.
+ * - REFRESH ... WHERE ... which modifies the matview in-place.
*/
bool
MatViewIncrementalMaintenanceIsEnabled(void)
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 4b30f768680..3380f5e68e1 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1229,7 +1229,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 2d1b19d1f53..b587967ec15 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -326,6 +326,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> opt_single_name
%type <list> opt_qualified_name
%type <boolean> opt_concurrently opt_usingindex
+%type <node> opt_refresh_where_clause
%type <dbehavior> opt_drop_behavior
%type <list> opt_utility_option_list
%type <list> opt_wait_with_clause
@@ -5097,17 +5098,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 1d34c19913e..56ae3bd2f94 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1694,7 +1694,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 738c731c1a9..ff46542d66c 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 91377a6cde3..3fc088fd262 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4159,6 +4159,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..75b02e84a6a
--- /dev/null
+++ b/src/test/regress/expected/matview_where.out
@@ -0,0 +1,344 @@
+--
+-- 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 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 protected]', '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 protected]', 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 protected] | 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 cc365393bb7..738442d9a32 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..c3faceed27d
--- /dev/null
+++ b/src/test/regress/sql/matview_where.sql
@@ -0,0 +1,302 @@
+--
+-- 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 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 protected]', '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 protected]', 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2026-05-21 05:17 vellaipandiyan sm <[email protected]>
parent: Adam Brusselback <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: vellaipandiyan sm @ 2026-05-21 05:17 UTC (permalink / raw)
To: Adam Brusselback <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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 protected]>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2026-05-26 18:53 Adam Brusselback <[email protected]>
parent: vellaipandiyan sm <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Adam Brusselback @ 2026-05-26 18:53 UTC (permalink / raw)
To: vellaipandiyan sm <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
> partial refreshes and concurrent DML on base tables.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2026-05-28 00:04 Zsolt Parragi <[email protected]>
parent: Adam Brusselback <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Zsolt Parragi @ 2026-05-28 00:04 UTC (permalink / raw)
To: [email protected]
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');
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
@ 2026-05-29 02:53 Adam Brusselback <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Adam Brusselback @ 2026-05-29 02:53 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-05-29 02:53 UTC | newest]
Thread overview: 10+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-08 20:58 [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW Adam Brusselback <[email protected]>
2025-12-09 05:08 ` Kirk Wolak <[email protected]>
2025-12-09 05:35 ` Nico Williams <[email protected]>
2025-12-09 16:27 ` Adam Brusselback <[email protected]>
2026-03-23 14:23 ` Adam Brusselback <[email protected]>
2026-04-09 17:32 ` Adam Brusselback <[email protected]>
2026-05-28 00:04 ` Zsolt Parragi <[email protected]>
2026-05-29 02:53 ` Adam Brusselback <[email protected]>
2026-05-21 05:17 ` vellaipandiyan sm <[email protected]>
2026-05-26 18:53 ` Adam Brusselback <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox