Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

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