MIME-Version: 1.0
References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv> <CAOVWO5p7PieGTm3GJC8NAYgSEQLoBdZw9Se4u=EbPrr1mW7d5Q@mail.gmail.com>
 <CAOVWO5rWkGXx37tpsAuTwrY5muPePHG44ke0xwYcakWVfhkPaw@mail.gmail.com>
 <CAOVWO5qchazoL8H+-d4eZ_zm8jFUH4YF3WvWnXqcDEh8i7hbhw@mail.gmail.com>
 <CAOVWO5qF2_FV7M=USdSgjjPO124YMpw7TY+M4+mnkNPOA2Bstg@mail.gmail.com>
 <CA+FpmFe7pgJRi16zP8Liz5vuUkVdTnPy30Zr=mXzZA3shKgvCw@mail.gmail.com>
 <CAOVWO5rfJGpxRDn496gAHmAxW7L_72==KkOcz0q16YOvqm4-=g@mail.gmail.com>
 <CAOVWO5p2U8rMvh3pJ4WaNN7ES4SbiEKXLV_0pZ-UdYp0kACa7g@mail.gmail.com>
 <CA+FpmFeLJDWZSx1TSRVodFbQOLWa-SXBCiG0rspu--HtadaJ+w@mail.gmail.com>
 <CA+FpmFdCtzBO56sAg9hdJ79gALNTfBDc0OxLah2wLAimeORt0A@mail.gmail.com>
 <CAOVWO5ouwJTgGsXguP=pZn6wB2fL+xErK9+pdg98vCApq7F10g@mail.gmail.com>
 <tencent_175CBC8C83733BB7D09242AFDAD814749907@qq.com> <tencent_7FA4F0B1D63BE55A8608EFF600CD4E371405@qq.com>
In-Reply-To: <tencent_7FA4F0B1D63BE55A8608EFF600CD4E371405@qq.com>
From: surya poondla <suryapoondla4@gmail.com>
Date: Fri, 20 Mar 2026 11:01:22 -0700
Message-ID: <CAOVWO5pQ4jyR1M6XYkjkg7c1KshrzaG-9mng7AGBGghZpYKR-Q@mail.gmail.com>
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
To: cca5507 <cca5507@qq.com>
Cc: Rafia Sabih <rafia.pghackers@gmail.com>, Giuliano Gagliardi <gogi@gogi.tv>, 
	pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ae5714064d78799b"
Archived-At: <https://www.postgresql.org/message-id/CAOVWO5pQ4jyR1M6XYkjkg7c1KshrzaG-9mng7AGBGghZpYKR-Q%40mail.gmail.com>
Precedence: bulk

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

Hi ChangAo,

Thank you for the detailed review.

For issue 1, my fix removes the IS NOT NULL guard from the pre-check so
that *= can detect all duplicate rows, including those containing NULLs.
(Note: The semantics of *=  has always treated NULL as equal to NULL.)

The reasoning is straightforward: the JOIN uses *= to match newdata rows
against MV rows. If newdata contains two *=-equal rows, both would match
the same MV row in the JOIN, producing a wrong diff. The pre-check must
therefore use the same *= semantics to catch exactly those duplicates
which is what my fix does by removing the IS NOT NULL guard.
The IS NOT NULL guard was the bug as it was hiding real duplicates from
detection.

Your approach leaves the pre-check unchanged and instead replaces *= in
the JOIN with record_image_eq_variant (NULL != NULL). I see two concerns:
1. record_image_eq_variant applies NULL != NULL globally to all rows in
   the JOIN, not just duplicate ones. This means any unchanged row
   containing any NULL in any column will never match its counterpart
   during the JOIN, causing a DELETE + INSERT for that row on every
   refresh even when the data has not changed. The original issue 2 was
   specifically about nullable indexed columns, your fix extends the
   performance problem to all nullable columns anywhere in the row,
   which makes the performance worse than issue 2.
2. The error surfaced becomes a unique_violation from the index rather
   than the explicit "contains duplicate rows" message, which is harder
   for users to diagnose.

Regards,
Surya Poondla

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

<div dir=3D"ltr"><div>Hi ChangAo,<br><br>Thank you for the detailed review.=
<br><br>For issue 1, my fix removes the IS NOT NULL guard from the pre-chec=
k so<br>that *=3D can detect all duplicate rows, including those containing=
 NULLs.<br>(Note: The semantics of *=3D =C2=A0has always treated NULL as eq=
ual to NULL.)<br><br>The reasoning is straightforward: the JOIN uses *=3D t=
o match newdata rows<br>against MV rows. If newdata contains two *=3D-equal=
 rows, both would match<br>the same MV row in the JOIN, producing a wrong d=
iff. The pre-check must<br>therefore use the same *=3D semantics to catch e=
xactly those duplicates=C2=A0<br>which is what my fix does by removing the =
IS NOT NULL guard.<br>The IS NOT NULL guard was the bug as it was hiding re=
al duplicates from detection.<br><br>Your approach leaves the pre-check unc=
hanged and instead replaces *=3D in<br>the JOIN with record_image_eq_varian=
t (NULL !=3D NULL). I see two concerns:<br>1. record_image_eq_variant appli=
es NULL !=3D NULL globally to all rows in<br>=C2=A0 =C2=A0the JOIN, not jus=
t duplicate ones. This means any unchanged row<br>=C2=A0 =C2=A0containing a=
ny NULL in any column will never match its counterpart<br>=C2=A0 =C2=A0duri=
ng the JOIN, causing a DELETE + INSERT for that row on every<br>=C2=A0 =C2=
=A0refresh even when the data has not changed. The original issue 2 was<br>=
=C2=A0 =C2=A0specifically about nullable indexed columns, your fix extends =
the<br>=C2=A0 =C2=A0performance problem to all nullable columns anywhere in=
 the row,<br>=C2=A0 =C2=A0which makes the performance worse than issue=C2=
=A02.<br>2. The error surfaced becomes a unique_violation from the index ra=
ther<br>=C2=A0 =C2=A0than the explicit &quot;contains duplicate rows&quot; =
message, which is harder<br>=C2=A0 =C2=A0for users to diagnose.<br><br>Rega=
rds,<br>Surya Poondla<br></div></div>

--000000000000ae5714064d78799b--