| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | David Rowley | 2026-11-17 03:55:15 | Re: BUG #19904: pg_column_is_updatable(): "negative bitmapset member not allowed" for large attnum (int16 overflow) |