Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

On Mon, 27 Jul 2026 16:28:06 -0400
Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Jul 21, 2026 at 2:55 PM surya poondla <suryapoondla4(at)gmail(dot)com> wrote:
> > For issue 1 (v7-0001) the behaviour is correct:
> > 1) A duplicate row containing NULLs is now detected and reported:
> > REFRESH MATERIALIZED VIEW CONCURRENTLY m;
> > ERROR: new data for materialized view "m" contains duplicate rows
>
> I agree that there's a bug here, but I'm not sure that we've got a
> sufficiently clear-eyed view of where the bug is. The proposed fix
> doesn't touch the function header comment, which lays out the
> algorithm that Kevin intended to use and the reasoning behind it. I
> think there's a bug in that algorithm, which means that the function
> should probably be updating it. But before we get to that point, what
> exactly is the bug?
>
> * This is called after a new version of the data has been created in a
> * temporary table. It performs a full outer join against the old version of
> * the data, producing "diff" results. This join cannot work if there are any
> * duplicated rows in either the old or new versions, in the sense that every
> * column would compare as equal between the two rows. It does work correctly
> * in the face of rows which have at least one NULL value, with all non-NULL
> * columns equal.
>
> I think the bug is in this last sentence. If that statement were true,
> then the delta table would be constructed correctly in the test case
> from the original email, because in the example, all the rows involved
> contain at least one NULL value. But in fact, the delta table ends up
> being wrong in that test case. As far as it can see, every row in the
> new query output is present in the materialized view and every row in
> the materialized view is present in the new query output, and so it
> misses the fact that there is a new row to insert.

I also agree that the current behavior is a bug because it can leave the
materialized view inconsistent with its base tables.

As Robert suggested, the function header comment should also be updated to
clarify that the algorithm works correctly in the presence of duplicated rows
with NULL values only whenthe NULL appears in a column covered by a usable
unique index.

I also think it would be helpful if the comment explained why this case works,
rather than simply stating that it does.

Additionally, I have a few comments on the patch.

(1)
The commit message says:

This correctly handles two cases that the old approach got wrong:
- (test, NULL) x2 with index on a: a='test' is non-null and
duplicated, so the duplicate is correctly detected and an error
is raised.
- (NULL, NULL) x2 with index on a: a=NULL, and unique indexes
allow multiple NULLs (each is treated as distinct), so the
refresh correctly succeeds and updates the view to two rows.

However, the seccond case is not fixed by this commit.
The current code already handles this case correctly.

(2)

+ *
+ * Using index column operators rather than *= alone is important: it
+ * correctly excludes rows whose indexed columns are NULL, because
+ * unique indexes treat NULLs as distinct so those rows do not cause
+ * join ambiguity.

I am not sure whether using the equality operators here is necessary. Since
the purpose seems to be excluding rows with NULLs in unique-index columns,
explicit NULL checks might make the intention clearer. Is there a reason to
prefer the equality operators here?

Regards,
Yugo Nagata

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>