Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

From: João Rito <joaovictor(dot)rito(at)gmail(dot)com>
To: suryapoondla4(at)gmail(dot)com
Cc: nagata(at)sraoss(dot)co(dot)jp, robertmhaas(at)gmail(dot)com, rafia(dot)pghackers(at)gmail(dot)com, cca5507(at)qq(dot)com, 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-18 19:03:35
Message-ID: CADMs5OO9B0vBnFi18eG91OGxehPTn9uNLmH6S3fdfOGEbexCNA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi Surya, hi all,

Thank you for v8. I tested it and it looks good to me. I may have
missed something, so please correct me where I am wrong.

What I did:

- Applied v8 to current master (c790e38b4a). It applies with
git apply --check --whitespace=error and builds with 0 warnings
(-O2, --without-icu, no --enable-cassert). make check passes
(243 tests) on master and with v8.
- Repeated my v7 cases. Two identical rows (test, NULL) with a unique
index on the first column: master leaves 1 row without error, v8 raises
the error. Same for (k1, NULL, 1) twice. (NULL, NULL) twice, a
two-column unique index with a duplicate only through NULL, and
NULLS NOT DISTINCT behave as before. A true duplicate still errors.
200k rows with about 20 percent NULLs and two unique indexes: 0 rows
missing and 0 extra, as on master.
- A NULL in a column outside the unique key plus a duplicated row now
errors, and a NULL inside a unique-key column does not. I also tried
two single-column unique indexes, a partial unique index, an
expression unique index and a mixed (a, lower(b)) index, each next to a
plain unique index, always with the old row identical to the duplicated
one. The partial, expression and mixed indexes did not change which
columns are checked, as I read in the code, and I saw no refresh that
succeeds where a plain REFRESH would fail.
- Pre-check alone, 1M rows, EXPLAIN ANALYZE, 3 runs each. With y all NULL
and unique indexes on x and on y, v8 took about 45-60 ms against
about 115-165 ms on master. On 2M rows with k 50 percent NULL and
indexes on (id) and (k,id), the pre-check in a real refresh went from
about 12.2 s (merge, two sorts on disk) to about 1.9 s (hash).
Compared with checking only "IS NOT NULL" without the equality
conditions, v8 was 2 to 3 times faster in my runs.
- One result differs from your numbers. With y all NULL and a unique
index only on x, I measured about 0.9-2.0 s for v8 against about
0.11-0.4 s on master, because master skips every row there. A full
refresh of 1M rows took about 1 s longer in some runs. I may not have
your exact schema, so please read this as a note, not as a problem.

Two small comments on the code:

- The message "contains duplicate rows without any null columns" can now
appear for a row that has NULLs outside the key, for example
"Row: (test,)". Would a wording without "without any null columns" be
clearer? I understand if you prefer to keep the existing text.
- I read the new header comment next to the code and found them
consistent. The pre-check test for NULL on one of two identical rows
looked right to me because "*=" treats NULLs as equal.

What I did not test: --enable-cassert, TAP tests or check-world (only
make check), meson builds, the back branches, concurrent refreshes from
two sessions, row security, nondeterministic collations, materialized
views larger than 2M rows, and your exact timing setup. Timings come from
a shared VPS with one sequence per case, so they vary by about 2x between
equal runs.

Thanks again for the work on this,

Joao Rito

On Tue, Nov 17, 2026 02:36 AM, surya poondla <suryapoondla4(at)gmail(dot)com>
wrote:

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2026-11-18 20:10:35 Re: BUG #19904: pg_column_is_updatable(): "negative bitmapset member not allowed" for large attnum (int16 overflow)
Previous Message Tom Lane 2026-11-18 18:10:19 Re: Inconsistent time zone handling in timetz