Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

From: surya poondla <suryapoondla4(at)gmail(dot)com>
To: 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>
Cc: Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, cca5507 <cca5507(at)qq(dot)com>, Giuliano Gagliardi <gogi(at)gogi(dot)tv>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: 2026-11-17 05:36:41
Message-ID: CAOVWO5oBsuj9J7Mh-Qsf3MFCvGj_0zQ1HOBh4UDgb-dCU=SKXQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi Yugo, Robert,

Thank you both for the analysis, and sorry for the late reply I
was occupied with some other stuff. Joao, thank you for testing v7.

Robert, I agree with your point, the header comment's claim that the
join "does work correctly in the face of rows which have at least one NULL
value" is where things went wrong.
A NULL only stops a match when it is in a column covered by a usable unique
index. The attached v8 follows your
suggestion and can be back-patchable.

Changes in v8:
1. The duplicate pre-check no longer skips rows with a NULL in *any*
column. It only skips rows with a NULL in a unique-key column, i.e. the
rows the diff join can never match. The existing error then fires for the
case in the original report.

2. I rewrote the header comment. It now explains why the join is correct as
in the unique index ensures each new row matches at most
one old row, and the pre-check ensures each old row matches at most one new
row. It also explains why identical rows with a NULL in a unique-key
column are harmless (they never match, so all of them are inserted and the
unique index decides) and why a NULL elsewhere gives no such
protection. It also notes the delete-and-reinsert cost of such rows.

Yugo, regarding your points:
1. Agreed, the (NULL, NULL) x 2 case already worked on the latest master,
so I removed it from the commit message.
It is still in the regression tests, to make sure the tighter check does
not reject it.

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.
I did keep the per-column equality conditions inside the EXISTS, though,
because "*=" is not hashable. Without a hashable join key the planner has
to sort
both copies of the new data on "*=". Running the pre-check query by itself
under EXPLAIN ANALYZE on 1M rows (no duplicates, half with a
NULL key), I measured:

master
~760 ms (merge join on *=)
IS NOT NULL only, no equality conditions ~1470-1650 ms (merge
join on *=)
IS NOT NULL + equality conditions (v8) ~150-180 ms
(hash join)

The equality conditions don't change which rows are reported, the btree
equality is reflexive, so identical rows with non-NULL keys always
satisfy them. A comment in the code says so. For the same reason, v8
reports the same rows as v7; the changes make the logic explicit and
documented rather than changing behavior.

One open question is v8 keeps the existing message, "new data for
materialized view ... contains duplicate rows without any null columns",
so that no translatable string changes in the back branches. For the
reported case it now looks odd next to "DETAIL: Row: (test,)". It could
say "...without any null key columns", maybe on master only. I have no
strong opinion and am happy to go either way.

v8 does not include the old 0002 (issue 2) as it has no effect on
materialized views because their
columns never have attnotnull set. Issue 2 needs a different approach, so
I'd rather keep it out of this fix.

Regards,
Surya Poondla

Attachment Content-Type Size
v8-0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-with-d.patch application/octet-stream 15.8 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Rajesh Kumar Thandapani 2026-11-17 06:54:05 Re: BUG #19776: pg_combinebackup ignores the truncation_block_length of an intermediate incremental backup
Previous Message PG Bug reporting form 2026-11-17 05:03:22 BUG #19910: json(b)_to_tsvector() nested array filter gives internal error XX000 "unexpected end of flag array"