Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

On Sun, 8 Feb 2026 at 22:49, Giuliano Gagliardi <gogi(at)gogi(dot)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