public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: surya poondla <suryapoondla4@gmail.com>
To: Giuliano Gagliardi <gogi@gogi.tv>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: Thu, 12 Feb 2026 18:08:08 -0800
Message-ID: <CAOVWO5qF2_FV7M=USdSgjjPO124YMpw7TY+M4+mnkNPOA2Bstg@mail.gmail.com> (raw)
In-Reply-To: <CAOVWO5qchazoL8H+-d4eZ_zm8jFUH4YF3WvWnXqcDEh8i7hbhw@mail.gmail.com>
References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv>
<CAOVWO5p7PieGTm3GJC8NAYgSEQLoBdZw9Se4u=EbPrr1mW7d5Q@mail.gmail.com>
<CAOVWO5rWkGXx37tpsAuTwrY5muPePHG44ke0xwYcakWVfhkPaw@mail.gmail.com>
<CAOVWO5qchazoL8H+-d4eZ_zm8jFUH4YF3WvWnXqcDEh8i7hbhw@mail.gmail.com>
Hi All,
Right now, I am exploring the issue 2.
>
I am not sure if anyone else has submitted a patch for issue 2. I don't see
any updates from the mailing list yet. I guess the mailing list has some
delays, apologies if my efforts look duplicated to other people's efforts.
I have a potential patch for issue 2.
I was able to reproduce the issue, saw the performance degradation, and,
with my patch (attached) I see an improvement in the REFRESH MATERIALIZED
VIEW CONCURRENTLY.
The main crux of issue 2 is: when a materialized view has unique index on a
nullable column, and when we did REFRESH MATERIALIZED VIEW CONCURRENTLY it
would include that column in the FULL OUTER
JOIN condition used to detect changes.
The nullable column was showing severe performance degradation because NULL
= NULL comparisons evaluate to NULL, making all rows appear different even
when unchanged!
The fix I explored is to skip nullable columns when building the FULL OUTER
JOIN conditions. Only include columns with NOT NULL constraints from unique
indexes. The record equality operator (*=) is always included and handles
nullable columns correctly.
Here is the output and performance improvement:
postgres=# \timing on
Timing is on.
postgres=# DROP MATERIALIZED VIEW IF EXISTS s CASCADE;
NOTICE: materialized view "s" does not exist, skipping
DROP MATERIALIZED VIEW
Time: 0.858 ms
postgres=#
postgres=# CREATE MATERIALIZED VIEW s AS SELECT generate_series as x, null
as y FROM generate_series(1, 1000000);
SELECT 1000000
Time: 1076.254 ms (00:01.076)
postgres=#
postgres=# CREATE UNIQUE INDEX ON s(x);
CREATE INDEX
Time: 375.026 ms
postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY s;
REFRESH MATERIALIZED VIEW
Time: 3807.143 ms (00:03.807)
postgres=# CREATE UNIQUE INDEX ON s(y);
CREATE INDEX
Time: 331.382 ms
postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY s;
REFRESH MATERIALIZED VIEW
Time: 3636.049 ms (00:03.636)
postgres=#
As we can see the REFRESH MATERIALIZED VIEW CONCURRENTLY now takes 3636.049
ms
With the current patch for issue 2, there is a trade-off.
The fix skips nullable columns from the join condition to avoid slowness
when NULLs exist (9s vs 3s in testing). This may slightly slow down cases
where nullable columns (unique index) never contain NULLs.
Users can restore full performance by adding the NOT NULL constraints to
the column if they know there will never be any nulls on that column.
I would love to hear any feedback on this tradeoff and am happy to
implement relevant changes.
Note: The attached patch addresses both issue 1, issue 2.
Regards,
Surya Poondla.
Attachments:
[application/octet-stream] 0002-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-NULL-hand.patch (9.6K, 3-0002-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-NULL-hand.patch)
download | inline diff:
From 60e20b59346b6e631c7bea6517dd55d9b4764192 Mon Sep 17 00:00:00 2001
From: spoondla <s_poondla@apple.com>
Date: Wed, 11 Feb 2026 16:06:56 -0800
Subject: [PATCH v2] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY, NULL handling
bugs.
Bug 1: REFRESH MATERIALIZED VIEW CONCURRENTLY doesn't throw expected error as REFRESH MATERIALIZED VIEW
Issue:
REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly skipping duplicate
detection for rows containing any NULL values. This would cause the refresh
to silently succeed while leaving the materialized view with stale data,
rather than properly reporting duplicate row errors.
The bug occurred because the duplicate check used "WHERE newdata.* IS NOT NULL"
which returns false if any column contains NULL. When duplicates existed in
rows with NULLs (e.g., two rows of ('test', NULL)), the check was skipped
entirely. The subsequent FULL OUTER JOIN would then match both duplicate rows
to the same old row, producing an empty diff, causing no updates to be applied.
Fix:
The fix removes the "IS NOT NULL" preconditions from the duplicate detection
query. The query now correctly checks all rows using the record equality
operator (*=), which treats NULL as equal to NULL. This matches the same
equality semantics used by the FULL OUTER JOIN in the diff query, ensuring
consistent duplicate detection.
Note:
The non-concurrent REFRESH was unaffected since it rebuilds indexes from
scratch, which properly detects duplicates during index creation.
Bug 2: Performance degradation in "REFRESH MATERIALIZED VIEW CONCURRENTLY" with nullable indexed columns
Issue:
When a materialized view had a unique index on a nullable column, REFRESH
MATERIALIZED VIEW CONCURRENTLY would include that column in the FULL OUTER
JOIN condition used to detect changes. This caused severe performance
degradation because NULL = NULL comparisons evaluate to NULL,
making all rows appear different even when unchanged.
Example: A materialized view with 1M rows where a nullable indexed column
contains all NULLs. When data is unchanged, the refresh should produce an
empty diff (0 rows). However, because NULL = NULL evaluates to NULL (not TRUE),
the join fails to match any rows. The diff incorrectly shows all 1M rows as
deleted and all 1M rows as newly inserted (2M total), causing unnecessary
use of resources which drastically increased the time for "refresh materialized view concurrently" operation.
Fix:
Skip nullable columns when building the FULL OUTER JOIN conditions. Only
include columns with NOT NULL constraints from unique indexes in the join
predicate. This is semantically correct because nullable columns in unique
indexes don't provide actual uniqueness for NULL values (because multiple NULLs
can exist in unique indexes). The record equality operator (*=) is always
included in the join and correctly handles NULL comparisons.
The fix adds a new variable 'addedAnyQuals' to track whether any column conditions
were actually added to the query, separate from 'foundUniqueIndex' which tracks
whether a usable unique index exists (for validation).
This ensures correct SQL syntax when all columns in an index are nullable.
---
src/backend/commands/matview.c | 55 ++++++++++++++++++++-------
src/test/regress/expected/matview.out | 2 +-
2 files changed, 43 insertions(+), 14 deletions(-)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 81a55a33ef2..5d2cd0e5a02 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -602,6 +602,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
char *nsp;
TupleDesc tupdesc;
bool foundUniqueIndex;
+ bool addedAnyQuals;
List *indexoidlist;
ListCell *indexoidscan;
int16 relnatts;
@@ -635,11 +636,13 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
elog(ERROR, "SPI_exec failed: %s", querybuf.data);
/*
- * We need to ensure that there are not duplicate rows without NULLs in
- * the new data set before we can count on the "diff" results. Check for
- * that in a way that allows showing the first duplicated row found. Even
- * after we pass this test, a unique index on the materialized view may
- * find a duplicate key problem.
+ * We need to ensure that there are not duplicate rows in the new data set
+ * before we can count on the "diff" results. Check for that in a way
+ * that allows showing the first duplicated row found. We check for
+ * duplicates using the record equality operator (*=), which treats NULLs
+ * as equal to each other - the same semantics used by the FULL OUTER JOIN
+ * in the diff query below. Even after we pass this test, a unique index
+ * on the materialized view may find a duplicate key problem.
*
* Note: here and below, we use "tablename.*::tablerowtype" as a hack to
* keep ".*" from being expanded into multiple columns in a SELECT list.
@@ -648,9 +651,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
resetStringInfo(&querybuf);
appendStringInfo(&querybuf,
"SELECT newdata.*::%s FROM %s newdata "
- "WHERE newdata.* IS NOT NULL AND EXISTS "
- "(SELECT 1 FROM %s newdata2 WHERE newdata2.* IS NOT NULL "
- "AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
+ "WHERE EXISTS "
+ "(SELECT 1 FROM %s newdata2 "
+ "WHERE newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
"newdata.ctid)",
tempname, tempname, tempname);
@@ -667,7 +670,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
*/
ereport(ERROR,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
- errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns",
+ errmsg("new data for materialized view \"%s\" contains duplicate rows",
RelationGetRelationName(matviewRel)),
errdetail("Row: %s",
SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1))));
@@ -715,6 +718,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
tupdesc = matviewRel->rd_att;
opUsedForQual = palloc0_array(Oid, relnatts);
foundUniqueIndex = false;
+ addedAnyQuals = false;
indexoidlist = RelationGetIndexList(matviewRel);
@@ -722,9 +726,11 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
{
Oid indexoid = lfirst_oid(indexoidscan);
Relation indexRel;
+ bool has_usable_unique_index;
indexRel = index_open(indexoid, RowExclusiveLock);
- if (is_usable_unique_index(indexRel))
+ has_usable_unique_index = is_usable_unique_index(indexRel);
+ if (has_usable_unique_index)
{
Form_pg_index indexStruct = indexRel->rd_index;
int indnkeyatts = indexStruct->indnkeyatts;
@@ -732,6 +738,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
Datum indclassDatum;
int i;
+ /* Mark that we found at least one usable unique index */
+ foundUniqueIndex = true;
+
/* Must get indclass the hard way. */
indclassDatum = SysCacheGetAttrNotNull(INDEXRELID,
indexRel->rd_indextuple,
@@ -753,6 +762,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
const char *leftop;
const char *rightop;
+ /*
+ * Skip columns that don't have a NOT NULL constraint.
+ *
+ * Nullable columns in unique indexes don't actually provide
+ * uniqueness for NULL values (multiple NULLs are allowed), so
+ * they can't reliably be used to identify matching rows. Using
+ * them in the join condition would cause NULL = NULL comparisons
+ * which evaluate to NULL making the rows appear different when they're
+ * actually the same according to the record equality operator (*=).
+ */
+ if (!attr->attnotnull)
+ continue;
+
/*
* Identify the equality operator associated with this index
* column. First we need to look up the column's opclass.
@@ -788,7 +810,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
/*
* Actually add the qual, ANDed with any others.
*/
- if (foundUniqueIndex)
+ if (addedAnyQuals)
appendStringInfoString(&querybuf, " AND ");
leftop = quote_qualified_identifier("newdata",
@@ -801,7 +823,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
op,
rightop, attrtype);
- foundUniqueIndex = true;
+ addedAnyQuals = true;
}
}
@@ -826,8 +848,15 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
errmsg("could not find suitable unique index on materialized view \"%s\"",
RelationGetRelationName(matviewRel)));
+ /*
+ * Always add the record equality check. This handles all columns
+ * including nullable ones that we may have skipped above.
+ */
+ if (addedAnyQuals)
+ appendStringInfoString(&querybuf, " AND ");
+
appendStringInfoString(&querybuf,
- " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) "
+ "newdata.* OPERATOR(pg_catalog.*=) mv.*) "
"WHERE newdata.* IS NULL OR mv.* IS NULL "
"ORDER BY tid");
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 0355720dfc6..a7a26f28218 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -396,7 +396,7 @@ REFRESH MATERIALIZED VIEW mvtest_mv;
ERROR: could not create unique index "mvtest_mv_a_idx"
DETAIL: Key (a)=(1) is duplicated.
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv;
-ERROR: new data for materialized view "mvtest_mv" contains duplicate rows without any null columns
+ERROR: new data for materialized view "mvtest_mv" contains duplicate rows
DETAIL: Row: (1,10)
DROP TABLE mvtest_foo CASCADE;
NOTICE: drop cascades to materialized view mvtest_mv
--
2.39.5 (Apple Git-154)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: suryapoondla4@gmail.com, gogi@gogi.tv, pgsql-bugs@lists.postgresql.org
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
In-Reply-To: <CAOVWO5qF2_FV7M=USdSgjjPO124YMpw7TY+M4+mnkNPOA2Bstg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox