Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY

From: Joao Rito <joaovictor(dot)rito(at)gmail(dot)com>
To: Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, surya poondla <suryapoondla4(at)gmail(dot)com>
Cc: Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>, Robert Haas <robertmhaas(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-14 21:33:56
Message-ID: 20261114213356.310-1-joaovictor.rito@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi Rafia, hi Surya,

Thank you for the patches. I tested the v7 series (v7-0001 for the
duplicate detection and v7-0002 for the nullable indexed columns). My
results are good for 0001 and mixed for 0002, details below.

What I did:

- Applied both patches to current master (0dbe240dc0). They apply cleanly
and build without warnings (-O2, --without-icu, no --enable-cassert).
- make check passes (243 tests) on master, with 0001 and with 0001+0002,
including the new matview regression tests.
- Correctness, comparing master, 0001 and 0001+0002 on a few small cases:
- Two identical rows (test, NULL) with a unique index on the first
column: on master REFRESH ... CONCURRENTLY succeeds without error and
leaves 1 row in the view while the query returns 2. With 0001 (and
with 0002) it raises "new data for materialized view "m" contains
duplicate rows"; the plain REFRESH also fails on this data (with a
unique index error), so the results now agree.
- Same with a third row added, (k1, NULL, 1) twice plus (k2, z, 2): master
silently keeps 2 rows, 0001 raises the error.
- Two (NULL, NULL) rows with a unique index on a: succeeds in all three
builds, 2 rows. This also matches Yugo's note that (NULL, NULL) twice
already worked on master. Two-column unique index with duplicates only
through NULL: succeeds in all three. A true duplicate (r,s,2) twice
errors in all three, only the message text changes (the "without any
null columns" part is gone). NULLS NOT DISTINCT gives the same unique
violation in all three.
- 200k rows with about 20 percent NULLs, two unique indexes, then
updates and deletes and a refresh: 0 rows missing and 0 extra in all
three builds (198012 rows).
- Performance on 2M rows (k is 50 percent NULL, unique indexes on (id) and
on (k,id)) and on 1M rows (y all NULL, unique indexes on x and on y):
- 0001 changes the plan of the duplicate pre-check from a merge semi
join with two sorts (37.1 s) to a hash semi join (9.95 s), in one
auto_explain run.
- With 0002 I could not see the benefit. A refresh with no data change
still deletes and inserts 3,000,000 tuples after 3 refreshes in all
three builds, in both scenarios. The join of the diff query still has
"mv.k = newdata.k" in the hash condition with 0002 (same plan as with
0001 alone).
- I think this agrees with Surya's note of 21 July that 0002 does not
help. My guess for the reason: 0002 skips nullable columns only when
attr->attnotnull is set for some index column, but the columns of a
materialized view show attnotnull = f even when the source column is
NOT NULL (I checked pg_attribute). If I read the patch right, the new
branch is then never taken for a materialized view. I did not debug
this further, so please take it as a guess. The new regression tests
for 0002 pass, but they only check that the refresh succeeds, so they
would not notice this.

Timings are noisy on my machine (one refresh of the 2M row case took from
24 s to 88 s), so I would not read anything into the elapsed times, only
into the tuple counts and the plans.

I did not test: --enable-cassert, TAP tests or check-world (only make
check), meson builds, the back branches, concurrent refreshes, expression
or partial unique indexes, row security, or materialized views bigger than
2M rows. Each timing is a single sequence of three runs.

Thanks again,

Joao Rito

In response to

Browse pgsql-bugs by date

  From Date Subject
Previous Message Joao Rito 2026-11-14 21:33:45 Re: BUG #19807: CHECK constraint not enforced on partitioned table