MIME-Version: 1.0
References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv> <CAOVWO5p7PieGTm3GJC8NAYgSEQLoBdZw9Se4u=EbPrr1mW7d5Q@mail.gmail.com>
In-Reply-To: <CAOVWO5p7PieGTm3GJC8NAYgSEQLoBdZw9Se4u=EbPrr1mW7d5Q@mail.gmail.com>
From: surya poondla <suryapoondla4@gmail.com>
Date: Wed, 11 Feb 2026 16:34:25 -0800
Message-ID: <CAOVWO5rWkGXx37tpsAuTwrY5muPePHG44ke0xwYcakWVfhkPaw@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="0000000000003d65e2064a95a7d6"
Archived-At: <https://www.postgresql.org/message-id/CAOVWO5rWkGXx37tpsAuTwrY5muPePHG44ke0xwYcakWVfhkPaw%40mail.gmail.com>
Precedence: bulk

--0000000000003d65e2064a95a7d6
Content-Type: multipart/alternative; boundary="0000000000003d65df064a95a7d4"

--0000000000003d65df064a95a7d4
Content-Type: text/plain; charset="UTF-8"

Hi Giuliano,

Regarding the issue 1,
>
> 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)
>
>
Thank you for the pointers, I made a patch in refresh_by_match_merge()
which reports an error in the REFRESH MATERIALIZED VIEW CONCURRENTLY case
too.

The issue was REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly
skipping duplicate detection for rows containing any NULL values. This was
happening because the "WHERE newdata.* IS NOT NULL" condition returns false
if any column contains NULL.
My patch removes the "IS NOT NULL" preconditions from the duplicate
detection query. The query now correctly checks all rows using the record
equality operator (*=), which treats NULL as equal to NULL (i.e True).

Here is the output with my patch:
postgres=# CREATE TABLE t(a text, b text);
CREATE TABLE
postgres=#   INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=#
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=#
postgres=# CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)

postgres=# SELECT * FROM t;
  a   | b
------+---
 test |
(1 row)

postgres=# INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=# SELECT * FROM t;
  a   | b
------+---
 test |
 test |
(2 rows)

postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)

postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY m;
2026-02-11 15:57:46.751 PST [39510] ERROR:  new data for materialized view
"m" contains duplicate rows
2026-02-11 15:57:46.751 PST [39510] DETAIL:  Row: (test,)
2026-02-11 15:57:46.751 PST [39510] STATEMENT:  REFRESH MATERIALIZED VIEW
CONCURRENTLY m;
ERROR:  new data for materialized view "m" contains duplicate rows
DETAIL:  Row: (test,)
postgres=#
postgres=# REFRESH MATERIALIZED VIEW m;
2026-02-11 15:57:55.877 PST [39510] ERROR:  could not create unique index
"m_a_idx"
2026-02-11 15:57:55.877 PST [39510] DETAIL:  Key (a)=(test) is duplicated.
2026-02-11 15:57:55.877 PST [39510] STATEMENT:  REFRESH MATERIALIZED VIEW m;
ERROR:  could not create unique index "m_a_idx"
DETAIL:  Key (a)=(test) is duplicated.
postgres=#

Regards,
Surya Poondla

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Giuliano,<br></div><div class=3D"gmail=
_quote gmail_quote_container"><div><br>Regarding the issue 1,<span class=3D=
"gmail-im"><blockquote class=3D"gmail_quote" style=3D"margin: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 handles<br>REFRESH MATE=
RIALIZED 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 duplicate<br>rows, but it does not catch the following case:<br><br>CRE=
ATE 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 O=
N m(a);<br>INSERT INTO t VALUES(&#39;test&#39;, null); -- t now contains tw=
o 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><=
br></blockquote></span><div><br></div></div><div>Thank you for the pointers=
, I made a patch in=C2=A0refresh_by_match_merge() which reports an error in=
 the=C2=A0REFRESH MATERIALIZED VIEW CONCURRENTLY case too.=C2=A0</div><div>=
<br>The issue was REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly sk=
ipping duplicate detection for rows containing any NULL values. This was ha=
ppening=C2=A0because=C2=A0the=C2=A0&quot;WHERE newdata.* IS NOT NULL&quot; =
condition returns false if any column contains NULL.<br>My patch removes th=
e &quot;IS NOT NULL&quot; preconditions from the duplicate detection query.=
 The query now correctly checks all rows using the record equality operator=
 (*=3D), which treats NULL as equal to NULL (i.e True).<br><br>Here is the =
output with my patch:<br>postgres=3D# CREATE TABLE t(a text, b text);<br>CR=
EATE TABLE<br>postgres=3D# =C2=A0 INSERT INTO t VALUES(&#39;test&#39;, null=
);<br>INSERT 0 1<br>postgres=3D#<br>postgres=3D# =C2=A0 CREATE MATERIALIZED=
 VIEW m AS SELECT * FROM t;<br>SELECT 1<br>postgres=3D#<br>postgres=3D# CRE=
ATE UNIQUE INDEX ON m(a);<br>CREATE INDEX<br>postgres=3D# SELECT * FROM m;<=
br>=C2=A0 a =C2=A0 | b<br>------+---<br>=C2=A0test |<br>(1 row)<br><br>post=
gres=3D# SELECT * FROM t;<br>=C2=A0 a =C2=A0 | b<br>------+---<br>=C2=A0tes=
t |<br>(1 row)<br><br>postgres=3D# INSERT INTO t VALUES(&#39;test&#39;, nul=
l);<br>INSERT 0 1<br>postgres=3D# SELECT * FROM t;<br>=C2=A0 a =C2=A0 | b<b=
r>------+---<br>=C2=A0test |<br>=C2=A0test |<br>(2 rows)<br><br>postgres=3D=
# SELECT * FROM m;<br>=C2=A0 a =C2=A0 | b<br>------+---<br>=C2=A0test |<br>=
(1 row)<br><br>postgres=3D# REFRESH MATERIALIZED VIEW CONCURRENTLY m;<br>20=
26-02-11 15:57:46.751 PST [39510] ERROR: =C2=A0new data for materialized vi=
ew &quot;m&quot; contains duplicate rows<br>2026-02-11 15:57:46.751 PST [39=
510] DETAIL: =C2=A0Row: (test,)<br>2026-02-11 15:57:46.751 PST [39510] STAT=
EMENT: =C2=A0REFRESH MATERIALIZED VIEW CONCURRENTLY m;<br>ERROR: =C2=A0new =
data for materialized view &quot;m&quot; contains duplicate rows<br>DETAIL:=
 =C2=A0Row: (test,)<br>postgres=3D#<br>postgres=3D# REFRESH MATERIALIZED VI=
EW m;<br>2026-02-11 15:57:55.877 PST [39510] ERROR: =C2=A0could not create =
unique index &quot;m_a_idx&quot;<br>2026-02-11 15:57:55.877 PST [39510] DET=
AIL: =C2=A0Key (a)=3D(test) is duplicated.<br>2026-02-11 15:57:55.877 PST [=
39510] STATEMENT: =C2=A0REFRESH MATERIALIZED VIEW m;<br>ERROR: =C2=A0could =
not create unique index &quot;m_a_idx&quot;<br>DETAIL: =C2=A0Key (a)=3D(tes=
t) is duplicated.<br>postgres=3D#<br><br>Regards,=C2=A0<br>Surya Poondla</d=
iv></div></div>

--0000000000003d65df064a95a7d4--
--0000000000003d65e2064a95a7d6
Content-Type: application/octet-stream; 
	name="0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-detect.patch"
Content-Disposition: attachment; 
	filename="0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-detect.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mliq3rzz0>
X-Attachment-Id: f_mliq3rzz0

RnJvbSBkMjZlMDg0ZWQwMGNkZTIxZDkxN2NkNGRjNTJmMmFiMTQ2N2ZkYzE5IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBzcG9vbmRsYSA8c19wb29uZGxhQGFwcGxlLmNvbT4KRGF0ZTog
V2VkLCAxMSBGZWIgMjAyNiAxNjowNjo1NiAtMDgwMApTdWJqZWN0OiBbUEFUQ0hdIEZpeCBSRUZS
RVNIIE1BVEVSSUFMSVpFRCBWSUVXIENPTkNVUlJFTlRMWSB0byBkZXRlY3QgTlVMTAogY29udGFp
bmluZyBkdXBsaWNhdGVzLgoKSXNzdWU6ClJFRlJFU0ggTUFURVJJQUxJWkVEIFZJRVcgQ09OQ1VS
UkVOVExZIHdhcyBpbmNvcnJlY3RseSBza2lwcGluZyBkdXBsaWNhdGUKZGV0ZWN0aW9uIGZvciBy
b3dzIGNvbnRhaW5pbmcgYW55IE5VTEwgdmFsdWVzLiBUaGlzIHdvdWxkIGNhdXNlIHRoZSByZWZy
ZXNoCnRvIHNpbGVudGx5IHN1Y2NlZWQgd2hpbGUgbGVhdmluZyB0aGUgbWF0ZXJpYWxpemVkIHZp
ZXcgd2l0aCBzdGFsZSBkYXRhLApyYXRoZXIgdGhhbiBwcm9wZXJseSByZXBvcnRpbmcgZHVwbGlj
YXRlIHJvdyBlcnJvcnMuCgpUaGUgYnVnIG9jY3VycmVkIGJlY2F1c2UgdGhlIGR1cGxpY2F0ZSBj
aGVjayB1c2VkICJXSEVSRSBuZXdkYXRhLiogSVMgTk9UIE5VTEwiCndoaWNoIHJldHVybnMgZmFs
c2UgaWYgYW55IGNvbHVtbiBjb250YWlucyBOVUxMLiBXaGVuIGR1cGxpY2F0ZXMgZXhpc3RlZCBp
bgpyb3dzIHdpdGggTlVMTHMgKGUuZy4sIHR3byByb3dzIG9mICgndGVzdCcsIE5VTEwpKSwgdGhl
IGNoZWNrIHdhcyBza2lwcGVkCmVudGlyZWx5LiBUaGUgc3Vic2VxdWVudCBGVUxMIE9VVEVSIEpP
SU4gd291bGQgdGhlbiBtYXRjaCBib3RoIGR1cGxpY2F0ZSByb3dzCnRvIHRoZSBzYW1lIG9sZCBy
b3csIHByb2R1Y2luZyBhbiBlbXB0eSBkaWZmLCBjYXVzaW5nIG5vIHVwZGF0ZXMgdG8gYmUgYXBw
bGllZC4KCkZpeDoKVGhlIGZpeCByZW1vdmVzIHRoZSAiSVMgTk9UIE5VTEwiIHByZWNvbmRpdGlv
bnMgZnJvbSB0aGUgZHVwbGljYXRlIGRldGVjdGlvbgpxdWVyeS4gVGhlIHF1ZXJ5IG5vdyBjb3Jy
ZWN0bHkgY2hlY2tzIGFsbCByb3dzIHVzaW5nIHRoZSByZWNvcmQgZXF1YWxpdHkKb3BlcmF0b3Ig
KCo9KSwgd2hpY2ggdHJlYXRzIE5VTEwgYXMgZXF1YWwgdG8gTlVMTC4gVGhpcyBtYXRjaGVzIHRo
ZSBzYW1lCmVxdWFsaXR5IHNlbWFudGljcyB1c2VkIGJ5IHRoZSBGVUxMIE9VVEVSIEpPSU4gaW4g
dGhlIGRpZmYgcXVlcnksIGVuc3VyaW5nCmNvbnNpc3RlbnQgZHVwbGljYXRlIGRldGVjdGlvbi4K
Ck5vdGU6ClRoZSBub24tY29uY3VycmVudCBSRUZSRVNIIHdhcyB1bmFmZmVjdGVkIHNpbmNlIGl0
IHJlYnVpbGRzIGluZGV4ZXMgZnJvbQpzY3JhdGNoLCB3aGljaCBwcm9wZXJseSBkZXRlY3RzIGR1
cGxpY2F0ZXMgZHVyaW5nIGluZGV4IGNyZWF0aW9uLgotLS0KIHNyYy9iYWNrZW5kL2NvbW1hbmRz
L21hdHZpZXcuYyB8IDIwICsrKysrKysrKysrLS0tLS0tLS0tCiAxIGZpbGUgY2hhbmdlZCwgMTEg
aW5zZXJ0aW9ucygrKSwgOSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9j
b21tYW5kcy9tYXR2aWV3LmMgYi9zcmMvYmFja2VuZC9jb21tYW5kcy9tYXR2aWV3LmMKaW5kZXgg
ODFhNTVhMzNlZjIuLjZhYWY3YTY4YzIwIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5k
cy9tYXR2aWV3LmMKKysrIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvbWF0dmlldy5jCkBAIC02MzUs
MTEgKzYzNSwxMyBAQCByZWZyZXNoX2J5X21hdGNoX21lcmdlKE9pZCBtYXR2aWV3T2lkLCBPaWQg
dGVtcE9pZCwgT2lkIHJlbG93bmVyLAogCQllbG9nKEVSUk9SLCAiU1BJX2V4ZWMgZmFpbGVkOiAl
cyIsIHF1ZXJ5YnVmLmRhdGEpOwogCiAJLyoKLQkgKiBXZSBuZWVkIHRvIGVuc3VyZSB0aGF0IHRo
ZXJlIGFyZSBub3QgZHVwbGljYXRlIHJvd3Mgd2l0aG91dCBOVUxMcyBpbgotCSAqIHRoZSBuZXcg
ZGF0YSBzZXQgYmVmb3JlIHdlIGNhbiBjb3VudCBvbiB0aGUgImRpZmYiIHJlc3VsdHMuICBDaGVj
ayBmb3IKLQkgKiB0aGF0IGluIGEgd2F5IHRoYXQgYWxsb3dzIHNob3dpbmcgdGhlIGZpcnN0IGR1
cGxpY2F0ZWQgcm93IGZvdW5kLiAgRXZlbgotCSAqIGFmdGVyIHdlIHBhc3MgdGhpcyB0ZXN0LCBh
IHVuaXF1ZSBpbmRleCBvbiB0aGUgbWF0ZXJpYWxpemVkIHZpZXcgbWF5Ci0JICogZmluZCBhIGR1
cGxpY2F0ZSBrZXkgcHJvYmxlbS4KKwkgKiBXZSBuZWVkIHRvIGVuc3VyZSB0aGF0IHRoZXJlIGFy
ZSBub3QgZHVwbGljYXRlIHJvd3MgaW4gdGhlIG5ldyBkYXRhIHNldAorCSAqIGJlZm9yZSB3ZSBj
YW4gY291bnQgb24gdGhlICJkaWZmIiByZXN1bHRzLiAgQ2hlY2sgZm9yIHRoYXQgaW4gYSB3YXkK
KwkgKiB0aGF0IGFsbG93cyBzaG93aW5nIHRoZSBmaXJzdCBkdXBsaWNhdGVkIHJvdyBmb3VuZC4g
IFdlIGNoZWNrIGZvcgorCSAqIGR1cGxpY2F0ZXMgdXNpbmcgdGhlIHJlY29yZCBlcXVhbGl0eSBv
cGVyYXRvciAoKj0pLCB3aGljaCB0cmVhdHMgTlVMTHMKKwkgKiBhcyBlcXVhbCB0byBlYWNoIG90
aGVyIC0gdGhlIHNhbWUgc2VtYW50aWNzIHVzZWQgYnkgdGhlIEZVTEwgT1VURVIgSk9JTgorCSAq
IGluIHRoZSBkaWZmIHF1ZXJ5IGJlbG93LiAgRXZlbiBhZnRlciB3ZSBwYXNzIHRoaXMgdGVzdCwg
YSB1bmlxdWUgaW5kZXgKKwkgKiBvbiB0aGUgbWF0ZXJpYWxpemVkIHZpZXcgbWF5IGZpbmQgYSBk
dXBsaWNhdGUga2V5IHByb2JsZW0uCiAJICoKIAkgKiBOb3RlOiBoZXJlIGFuZCBiZWxvdywgd2Ug
dXNlICJ0YWJsZW5hbWUuKjo6dGFibGVyb3d0eXBlIiBhcyBhIGhhY2sgdG8KIAkgKiBrZWVwICIu
KiIgZnJvbSBiZWluZyBleHBhbmRlZCBpbnRvIG11bHRpcGxlIGNvbHVtbnMgaW4gYSBTRUxFQ1Qg
bGlzdC4KQEAgLTY0OCw5ICs2NTAsOSBAQCByZWZyZXNoX2J5X21hdGNoX21lcmdlKE9pZCBtYXR2
aWV3T2lkLCBPaWQgdGVtcE9pZCwgT2lkIHJlbG93bmVyLAogCXJlc2V0U3RyaW5nSW5mbygmcXVl
cnlidWYpOwogCWFwcGVuZFN0cmluZ0luZm8oJnF1ZXJ5YnVmLAogCQkJCQkgIlNFTEVDVCBuZXdk
YXRhLio6OiVzIEZST00gJXMgbmV3ZGF0YSAiCi0JCQkJCSAiV0hFUkUgbmV3ZGF0YS4qIElTIE5P
VCBOVUxMIEFORCBFWElTVFMgIgotCQkJCQkgIihTRUxFQ1QgMSBGUk9NICVzIG5ld2RhdGEyIFdI
RVJFIG5ld2RhdGEyLiogSVMgTk9UIE5VTEwgIgotCQkJCQkgIkFORCBuZXdkYXRhMi4qIE9QRVJB
VE9SKHBnX2NhdGFsb2cuKj0pIG5ld2RhdGEuKiAiCisJCQkJCSAiV0hFUkUgRVhJU1RTICIKKwkJ
CQkJICIoU0VMRUNUIDEgRlJPTSAlcyBuZXdkYXRhMiAiCisJCQkJCSAiV0hFUkUgbmV3ZGF0YTIu
KiBPUEVSQVRPUihwZ19jYXRhbG9nLio9KSBuZXdkYXRhLiogIgogCQkJCQkgIkFORCBuZXdkYXRh
Mi5jdGlkIE9QRVJBVE9SKHBnX2NhdGFsb2cuPD4pICIKIAkJCQkJICJuZXdkYXRhLmN0aWQpIiwK
IAkJCQkJIHRlbXBuYW1lLCB0ZW1wbmFtZSwgdGVtcG5hbWUpOwpAQCAtNjY3LDcgKzY2OSw3IEBA
IHJlZnJlc2hfYnlfbWF0Y2hfbWVyZ2UoT2lkIG1hdHZpZXdPaWQsIE9pZCB0ZW1wT2lkLCBPaWQg
cmVsb3duZXIsCiAJCSAqLwogCQllcmVwb3J0KEVSUk9SLAogCQkJCShlcnJjb2RlKEVSUkNPREVf
Q0FSRElOQUxJVFlfVklPTEFUSU9OKSwKLQkJCQkgZXJybXNnKCJuZXcgZGF0YSBmb3IgbWF0ZXJp
YWxpemVkIHZpZXcgXCIlc1wiIGNvbnRhaW5zIGR1cGxpY2F0ZSByb3dzIHdpdGhvdXQgYW55IG51
bGwgY29sdW1ucyIsCisJCQkJIGVycm1zZygibmV3IGRhdGEgZm9yIG1hdGVyaWFsaXplZCB2aWV3
IFwiJXNcIiBjb250YWlucyBkdXBsaWNhdGUgcm93cyIsCiAJCQkJCQlSZWxhdGlvbkdldFJlbGF0
aW9uTmFtZShtYXR2aWV3UmVsKSksCiAJCQkJIGVycmRldGFpbCgiUm93OiAlcyIsCiAJCQkJCQkg
ICBTUElfZ2V0dmFsdWUoU1BJX3R1cHRhYmxlLT52YWxzWzBdLCBTUElfdHVwdGFibGUtPnR1cGRl
c2MsIDEpKSkpOwotLSAKMi4zOS41IChBcHBsZSBHaXQtMTU0KQoK
--0000000000003d65e2064a95a7d6--