Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: surya poondla <suryapoondla4(at)gmail(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org, Joao Rito <joaovictor(dot)rito(at)gmail(dot)com>, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, cca5507 <cca5507(at)qq(dot)com>, Giuliano Gagliardi <gogi(at)gogi(dot)tv>
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: 2026-11-17 21:37:05
Message-ID: CAN4CZFPh4vdrApAO8PWUri23BR-AS_EBES==25TvZ6vVbBp0vA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello

On Tue, 17 Nov 2026, surya poondla <suryapoondla4(at)gmail(dot)com> wrote:
> 2. The pre-check now tests the unique-key columns explicitly with
> "newdata.col IS NOT NULL", so it is clear which rows it skips and why.

CREATE TYPE pair AS (x int, y int);
CREATE TABLE t (k pair, b text);
INSERT INTO t VALUES (ROW(1, NULL), 'x');
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
CREATE UNIQUE INDEX ON m (k);
INSERT INTO t VALUES (ROW(1, NULL), 'x');
REFRESH MATERIALIZED VIEW CONCURRENTLY m;
-- master: ERROR: new data for materialized view "m" contains
duplicate rows without any null columns
-- v8: succeeds, m has 1 row while t has 2

newdata.k IS NOT NULL uses row semantics, so it is false for
ROW(1,NULL) and the pre-check skips the row, but the join compares k
with record_eq, which treats NULL fields as equal, so both new rows
still match the same old row. The same happens with a domain over a
composite type, and with ROW(NULL,NULL). Maybe it could use IS
DISTINCT FROM NULL instead?

> It could
> say "...without any null key columns", maybe on master only.

I would simply stop at "contains duplicate rows", and remove the rest
of the message.

In response to

Browse pgsql-bugs by date

  From Date Subject
Previous Message Alexander Lakhin 2026-11-17 19:00:01 Re: BUG #19910: json(b)_to_tsvector() nested array filter gives internal error XX000 "unexpected end of flag array"