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
<<attachment: benchmark.zip>>
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
