MIME-Version: 1.0
References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv>
In-Reply-To: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv>
From: Rafia Sabih <rafia.pghackers@gmail.com>
Date: Wed, 11 Feb 2026 20:48:04 +0100
Message-ID: <CA+FpmFedy3XWRhn8_T6y=K9cfRQE98hAQs-2BM9riWQz8oURuQ@mail.gmail.com>
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
To: Giuliano Gagliardi <gogi@gogi.tv>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000348439064a91a703"
Archived-At: <https://www.postgresql.org/message-id/CA%2BFpmFedy3XWRhn8_T6y%3DK9cfRQE98hAQs-2BM9riWQz8oURuQ%40mail.gmail.com>
Precedence: bulk

--000000000000348439064a91a703
Content-Type: multipart/alternative; boundary="000000000000348436064a91a701"

--000000000000348436064a91a701
Content-Type: text/plain; charset="UTF-8"

On Sun, 8 Feb 2026 at 22:49, Giuliano Gagliardi <gogi@gogi.tv> wrote:

> I noticed the following two issues while looking at the code that handles
> REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in
> matview.c):
>
> 1.
>
> At the beginning of the function, there is some code that checks for
> duplicate
> rows, but it does not catch the following case:
>
> CREATE TABLE t(a text, b text);
> INSERT INTO t VALUES('test', null);
> CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
> CREATE UNIQUE INDEX ON m(a);
> INSERT INTO t VALUES('test', null); -- t now contains two identical rows
>
> REFRESH MATERIALIZED VIEW CONCURRENTLY m;
>      -> no error, but m still contains only one row!
> REFRESH MATERIALIZED VIEW m;
>      -> error (as expected)
>
Interesting issue and thanks for pointing it out.
Going over the code in the function you mentioned(refresh_by_match_merge()
in matview.c), I found out that it is explicitly checking for the columns
where it is not NULL.
appendStringInfo(&querybuf,
"SELECT newdata.*::%s FROM %s newdata "
"WHERE newdata.* IS NOT NULL AND EXISTS "
"(SELECT 1 FROM %s newdata2 WHERE newdata2.* IS NOT NULL "
"AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
"newdata.ctid)",
It is mentioned in the comments above as well that it checks for the
duplicates in the rows without NULLs.
However, if I changed the query as in the attached patch, it errors out as
otherwise I would have expected.
Honestly, I do not understand why it is checking for duplicates excluding
null values.
Behaviour wise this definitely seems like a bug, but I am not sure if the
attached patch is the right way to fix it.

-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, 8 Feb 2=
026 at 22:49, Giuliano Gagliardi &lt;<a href=3D"mailto:gogi@gogi.tv">gogi@g=
ogi.tv</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex">I noticed the following two issues while looking at the code that han=
dles<br>
REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in matview=
.c):<br>
<br>
1.<br>
<br>
At the beginning of the function, there is some code that checks for duplic=
ate<br>
rows, but it does not catch the following case:<br>
<br>
CREATE TABLE t(a text, b text);<br>
INSERT INTO t VALUES(&#39;test&#39;, null);<br>
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;<br>
CREATE UNIQUE INDEX ON m(a);<br>
INSERT INTO t VALUES(&#39;test&#39;, null); -- t now contains two identical=
 rows<br>
<br>
REFRESH MATERIALIZED VIEW CONCURRENTLY m;<br>
=C2=A0 =C2=A0 =C2=A0-&gt; no error, but m still contains only one row!<br>
REFRESH MATERIALIZED VIEW m;<br>
=C2=A0 =C2=A0 =C2=A0-&gt; error (as expected)<br></blockquote></div><div>In=
teresting issue and thanks for pointing it out.</div><div>Going over the co=
de in the function you mentioned(refresh_by_match_merge() in matview.c), I =
found out that it is explicitly checking for the columns where it is not NU=
LL.</div><div>appendStringInfo(&amp;querybuf,<br>					 &quot;SELECT newdata=
.*::%s FROM %s newdata &quot;<br>					 &quot;WHERE newdata.* IS NOT NULL AN=
D EXISTS &quot;<br>					 &quot;(SELECT 1 FROM %s newdata2 WHERE newdata2.* =
IS NOT NULL &quot;<br>					 &quot;AND newdata2.* OPERATOR(pg_catalog.*=3D) =
newdata.* &quot;<br>					 &quot;AND newdata2.ctid OPERATOR(pg_catalog.&lt;&=
gt;) &quot;<br>					 &quot;newdata.ctid)&quot;,</div><div>It is mentioned i=
n the comments above as well that it checks for the duplicates in the rows =
without NULLs.</div><div>However, if I changed the query as in the attached=
 patch, it errors out as otherwise I would have expected.</div><div>Honestl=
y, I do not understand why it is checking for duplicates excluding null val=
ues.</div><div>Behaviour wise this definitely seems like a bug, but I am no=
t sure if the attached patch is the right way to fix it.</div><div><br></di=
v><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" cla=
ss=3D"gmail_signature"><div dir=3D"ltr">Regards,<br>Rafia Sabih<div>CYBERTE=
C PostgreSQL International GmbH<br></div></div></div></div>

--000000000000348436064a91a701--
--000000000000348439064a91a703
Content-Type: application/octet-stream; 
	name="0001-Check-for-duplicate-rows-with-NULLs.patch"
Content-Disposition: attachment; 
	filename="0001-Check-for-duplicate-rows-with-NULLs.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mlifxlwe0>
X-Attachment-Id: f_mlifxlwe0

RnJvbSAwY2E0YjEyMTRjODZmMWI5NTllNDMzOWNjOWI4ZTBhMWM2YWEwZmQzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBSYWZpYSBTYWJpaCA8cmFmaWEuc2FiaWhAY3liZXJ0ZWMuYXQ+
CkRhdGU6IFdlZCwgMTEgRmViIDIwMjYgMTE6Mzg6MDcgLTA4MDAKU3ViamVjdDogW1BBVENIXSBD
aGVjayBmb3IgZHVwbGljYXRlIHJvd3Mgd2l0aCBOVUxMcwoKSW4gY2FzZSBvZiBjb25jdXJyZW50
IHJlZnJlc2ggb2YgbWF0ZXJpYWxpemVkIHZpZXcsIGNoZWNrIGZvcgpjb2x1bW5zIHdpdGggTlVM
TCB2YWx1ZXMuIElmIHRoZXJlIGFyZSBkdXBsaWNhdGUgZW50cmllcyB3aXRoIE5VTExTCnRoZW4g
ZXJyb3Igb3V0LgotLS0KIHNyYy9iYWNrZW5kL2NvbW1hbmRzL21hdHZpZXcuYyB8IDYgKysrLS0t
CiAxIGZpbGUgY2hhbmdlZCwgMyBpbnNlcnRpb25zKCspLCAzIGRlbGV0aW9ucygtKQoKZGlmZiAt
LWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL21hdHZpZXcuYyBiL3NyYy9iYWNrZW5kL2NvbW1h
bmRzL21hdHZpZXcuYwppbmRleCA4MWE1NWEzM2VmMi4uNDhiYjI1ODJhOTIgMTAwNjQ0Ci0tLSBh
L3NyYy9iYWNrZW5kL2NvbW1hbmRzL21hdHZpZXcuYworKysgYi9zcmMvYmFja2VuZC9jb21tYW5k
cy9tYXR2aWV3LmMKQEAgLTY0OCw5ICs2NDgsOSBAQCByZWZyZXNoX2J5X21hdGNoX21lcmdlKE9p
ZCBtYXR2aWV3T2lkLCBPaWQgdGVtcE9pZCwgT2lkIHJlbG93bmVyLAogCXJlc2V0U3RyaW5nSW5m
bygmcXVlcnlidWYpOwogCWFwcGVuZFN0cmluZ0luZm8oJnF1ZXJ5YnVmLAogCQkJCQkgIlNFTEVD
VCBuZXdkYXRhLio6OiVzIEZST00gJXMgbmV3ZGF0YSAiCi0JCQkJCSAiV0hFUkUgbmV3ZGF0YS4q
IElTIE5PVCBOVUxMIEFORCBFWElTVFMgIgotCQkJCQkgIihTRUxFQ1QgMSBGUk9NICVzIG5ld2Rh
dGEyIFdIRVJFIG5ld2RhdGEyLiogSVMgTk9UIE5VTEwgIgotCQkJCQkgIkFORCBuZXdkYXRhMi4q
IE9QRVJBVE9SKHBnX2NhdGFsb2cuKj0pIG5ld2RhdGEuKiAiCisJCQkJCSAiV0hFUkUgRVhJU1RT
ICIKKwkJCQkJICIoU0VMRUNUIDEgRlJPTSAlcyBuZXdkYXRhMiBXSEVSRSAiCisJCQkJCSAiIG5l
d2RhdGEyLiogT1BFUkFUT1IocGdfY2F0YWxvZy4qPSkgbmV3ZGF0YS4qICIKIAkJCQkJICJBTkQg
bmV3ZGF0YTIuY3RpZCBPUEVSQVRPUihwZ19jYXRhbG9nLjw+KSAiCiAJCQkJCSAibmV3ZGF0YS5j
dGlkKSIsCiAJCQkJCSB0ZW1wbmFtZSwgdGVtcG5hbWUsIHRlbXBuYW1lKTsKLS0gCjIuMzkuNSAo
QXBwbGUgR2l0LTE1NCkKCg==
--000000000000348439064a91a703--