MIME-Version: 1.0
References: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
 <CAGXjcjn3Mf2QpAz-jqkNQ=uJcjpShDtZNPn0B6v_R2YcmQ=m3A@mail.gmail.com>
In-Reply-To: 
 <CAGXjcjn3Mf2QpAz-jqkNQ=uJcjpShDtZNPn0B6v_R2YcmQ=m3A@mail.gmail.com>
From: Adam Brusselback <adambrusselback@gmail.com>
Date: Tue, 26 May 2026 14:53:28 -0400
Message-ID: 
 <CAMjNa7cnyWqWQT5FwXX8myfej4ZLhEKLsBUdtW1vmvYK-KxbPA@mail.gmail.com>
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
To: vellaipandiyan sm <vellaipandiyan.sm@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b1e8130652bd0334"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMjNa7cnyWqWQT5FwXX8myfej4ZLhEKLsBUdtW1vmvYK-KxbPA%40mail.gmail.com>
Precedence: bulk

--000000000000b1e8130652bd0334
Content-Type: text/plain; charset="UTF-8"

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

--000000000000b1e8130652bd0334
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Vellaipandiyan, thanks for the review.<br><br>&gt; I wo=
nder whether overlapping refreshes could still encounter deadlock<br>&gt; s=
cenarios around UPSERT conflicts.<br><br>That was definitely a gap. The FOR=
 UPDATE step previously issued:<br><br>=C2=A0 =C2=A0 SELECT 1 FROM mv WHERE=
 (...) FOR UPDATE<br><br>with no ORDER BY, so two overlapping refreshes cou=
ld lock the existing<br>rows in different physical orders and deadlock befo=
re either one reached<br>the next statement.<br><br>To fix this, the next p=
atch gives the locking SELECT a deterministic<br>ORDER BY on the unique key=
 columns so every refresh acquires row locks<br>in the same sequence. The u=
psert now feeds from an ordered source as<br>well.<br><br>&gt; The CONCURRE=
NTLY behavior also feels somewhat unintuitive here.<br><br>I&#39;ll include=
 this change in the next patch. After more thinking about<br><div>it (and s=
ome feedback), I decided that going this way seems better. So any</div><div=
>prior discussion about CONCURRENTLY vs without is now talking about the</d=
iv><div>inverse of the current version of the patch.</div><br><div>Addition=
ally, I&#39;ve fixed a &quot;scope drift&quot; issue where the two differen=
t implementations</div><div>had inconsistent behavior. A row&#39;s non-key=
=C2=A0column changes in the base table, shifting it=C2=A0</div><div>into th=
e WHERE=C2=A0predicate&#39;s scope. Previously, this caused a unique constr=
aint</div><div>violation=C2=A0for the match_merge path because the old row =
was invisible to the filter</div>and never deleted. This was previously han=
dled by the direct_mod path properly. <br><br>Now, both the direct-modifica=
tion and match/merge paths resolve this by<br>using an INSERT ... ON CONFLI=
CT DO UPDATE step (or DO NOTHING if there<br>are no non-key columns) agains=
t the arbiter index. This safely updates<br>the out-of-scope stale row in p=
lace without duplicating the key.<br><br>&gt; It may also help to document =
the intended guarantees around overlapping<br>&gt; partial refreshes and co=
ncurrent DML on base tables.<br><br>Here is my attempt at saying what the i=
mplementation actually guarantees;<br>I will fit this into the docs in some=
 way. If anyone notices something<br>wrong with the below, please speak up.=
<br><br>Concurrent Partial Refresh (direct_mod path):<br>The FOR UPDATE / u=
psert-CTE path takes RowExclusiveLock. Readers are<br>never blocked. Neithe=
r RowExclusiveLock nor the per-row FOR UPDATE<br>locks conflict with a plai=
n SELECT. The refresh issues two statements<br>in one transaction: the lock=
ing SELECT, then the select/upsert/<br>anti-join-delete CTE. Under the defa=
ult READ COMMITTED, these take<br>separate snapshots, but the FOR UPDATE ro=
w locks are held to transaction<br>end, so they bridge both statements. An =
overlapping refresh blocks on<br>those locks for the full duration of the r=
efreshing transaction, not<br>just for the locking SELECT, so the gap betwe=
en the two statements does<br>not expose existing MV rows. The CTE&#39;s up=
sert and delete are a single<br>statement, so a reader sees either the pre-=
refresh or post-refresh<br>state of an affected row, never a half-applied o=
ne.<br><br>Concurrent partial refreshes whose predicates touch overlapping =
existing<br>MV rows are serialized. The second waits on the first&#39;s FOR=
 UPDATE<br>locks. Refreshes over disjoint row sets run in parallel. Within =
the<br>predicate scope, the MV is made consistent with the query snapshot: =
rows<br>present in the snapshot are upserted, rows that no longer appear ar=
e<br>deleted via the anti-join.<br><br>Rows whose key falls outside the pre=
dicate are not touched. Note this<br>is keyed on the unique index, not on t=
he predicate. As mentioned with<br>the drift fix, if the predicate matches =
a fresh row whose key collides<br>with an existing MV row that does not cur=
rently match the predicate,<br>ON CONFLICT will update that existing row, a=
nd the step-one FOR UPDATE<br>will not have locked it. This only arises whe=
n the predicate references<br>non-key columns. When the predicate ranges ov=
er the unique-key columns,<br>the colliding row necessarily matches too.<br=
><br>It does not lock the base tables. Base-table DML committed after the<b=
r>refresh&#39;s snapshot is not reflected, identical to a normal full REFRE=
SH.<br><br>SELECT FOR UPDATE only serializes overlapping refreshes covering=
 rows<br>that already exist in the MV. Two refreshes that both insert the s=
ame<br>new logical key are serialized by ON CONFLICT and the unique index, =
not<br>by FOR UPDATE. The outcome is still correct. The last writer wins on=
<br>that key.<br><br>The predicate must be non-volatile (enforced) and a us=
able unique index<br>is required (enforced).<br><br>The above assumes READ =
COMMITTED. I haven&#39;t thought through how things<br>will work with other=
 isolation levels.<br><br>Non-Concurrent Partial Refresh (match_merge path)=
:<br>The WHERE diff/merge path takes an ExclusiveLock and operates similarl=
y<br>to the standard full-refresh diff/merge, but with the diff scope<br>re=
stricted to rows matching the predicate. Readers are allowed, writers<br>ar=
e blocked, and overlapping refreshes are serialized at the table<br>level. =
The main difference from a full concurrent refresh is that its<br>final ins=
ert uses ON CONFLICT DO UPDATE (just like the direct_mod path)<br>specifica=
lly to resolve unique key violations caused by rows drifting<br>into the pr=
edicate&#39;s scope.<br><br>I will provide a new patch shortly.<br><br>Than=
ks,<br>Adam Brusselback</div>

--000000000000b1e8130652bd0334--