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

--00000000000037b650064a91c45c
Content-Type: text/plain; charset="UTF-8"

Hi Giuliano,

Thank you for the test case, yes I am able to reproduce the behavior for
issue1

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)
>
> Adding the output here for a complete picture.
postgres=# CREATE TABLE t(a text, b text);
CREATE TABLE
postgres=# INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=# CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=# CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=# INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY m;
REFRESH MATERIALIZED VIEW
postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)
postgres=# REFRESH MATERIALIZED VIEW m;
ERROR:  could not create unique index "m_a_idx"
DETAIL:  Key (a)=(test) is duplicated.
postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)

Yes, I believe "REFRESH MATERIALIZED VIEW CONCURRENTLY m;" should ideally
throw the same error as REFRESH MATERIALIZED VIEW m;

I am still trying to understand the CONCURRENTLY behavior in detail and
will share more of my findings on this potential issue.

Regards,
Surya Poondla

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Giuliano,</div><div><br></div>Thank yo=
u for the test case, yes I am able to reproduce the behavior for issue1<br>=
<div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"=
gmail_attr"><br></div><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 n=
oticed the following two issues while looking at the code that handles<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><br></blockquote><div>Addi=
ng the output here for a complete picture.<br>postgres=3D# CREATE TABLE t(a=
 text, b text);<br>CREATE TABLE<br>postgres=3D# INSERT INTO t VALUES(&#39;t=
est&#39;, null);<br>INSERT 0 1<br>postgres=3D# CREATE MATERIALIZED VIEW m A=
S SELECT * FROM t;<br>SELECT 1<br>postgres=3D# CREATE UNIQUE INDEX ON m(a);=
<br>CREATE INDEX<br>postgres=3D# INSERT INTO t VALUES(&#39;test&#39;, null)=
;<br>INSERT 0 1<br>postgres=3D# REFRESH MATERIALIZED VIEW CONCURRENTLY m;<b=
r>REFRESH MATERIALIZED VIEW<br>postgres=3D# SELECT * FROM m;<br>=C2=A0 a =
=C2=A0 | b<br>------+---<br>=C2=A0test |<br>(1 row)<br>postgres=3D# REFRESH=
 MATERIALIZED VIEW m;<br>ERROR: =C2=A0could not create unique index &quot;m=
_a_idx&quot;<br>DETAIL: =C2=A0Key (a)=3D(test) is duplicated.<br>postgres=
=3D# SELECT * FROM m;<br>=C2=A0 a =C2=A0 | b<br>------+---<br>=C2=A0test |<=
br>(1 row)<br><br>Yes, I believe &quot;REFRESH MATERIALIZED VIEW CONCURRENT=
LY m;&quot; should ideally throw the same error as=C2=A0REFRESH MATERIALIZE=
D VIEW m;<br><br>I am still trying to understand the CONCURRENTLY behavior =
in detail and will share more of my findings on this potential issue.</div>=
<div><br></div><div>Regards,<br>Surya Poondla<br><br><br></div></div></div>

--00000000000037b650064a91c45c--