| 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.
| 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" |