pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
26+ messages / 9 participants
[nested] [flat]

* Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-09 06:49  Giuliano Gagliardi <gogi@gogi.tv>
  0 siblings, 2 replies; 26+ messages in thread

From: Giuliano Gagliardi @ 2026-02-09 06:49 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

I noticed the following two issues while looking at the code that handles
REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in matview.c):

1.

At the beginning of the function, there is some code that checks for duplicate
rows, but it does not catch the following case:

CREATE TABLE t(a text, b text);
INSERT INTO t VALUES('test', null);
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
CREATE UNIQUE INDEX ON m(a);
INSERT INTO t VALUES('test', null); -- t now contains two identical rows

REFRESH MATERIALIZED VIEW CONCURRENTLY m;
     -> no error, but m still contains only one row!
REFRESH MATERIALIZED VIEW m;
     -> error (as expected)

2.

Do I understand correctly that the join creating the "diff" table is given
equality conditions for all columns referenced in any unique indexes? This
led me to think that a unique index on a column with many null entries
would enlarge the "diff" table.

In the following example, creating the second unique index noticeably worsens
the performance of REFRESH MATERIALIZED VIEW CONCURRENTLY:

CREATE MATERIALIZED VIEW s AS SELECT generate_series as x, null as y FROM generate_series(1, 1000000);
CREATE UNIQUE INDEX ON s(x);
REFRESH MATERIALIZED VIEW CONCURRENTLY s;
     -> runs for ~1700 ms
CREATE UNIQUE INDEX ON s(y);
REFRESH MATERIALIZED VIEW CONCURRENTLY s;
     -> runs for ~9000 ms

Kind regards,
Giuliano






^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-11 19:48  Rafia Sabih <rafia.pghackers@gmail.com>
  parent: Giuliano Gagliardi <gogi@gogi.tv>
  1 sibling, 0 replies; 26+ messages in thread

From: Rafia Sabih @ 2026-02-11 19:48 UTC (permalink / raw)
  To: Giuliano Gagliardi <gogi@gogi.tv>; +Cc: pgsql-bugs@lists.postgresql.org

On Sun, 8 Feb 2026 at 22:49, Giuliano Gagliardi <gogi@gogi.tv> wrote:

> I noticed the following two issues while looking at the code that handles
> REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in
> matview.c):
>
> 1.
>
> At the beginning of the function, there is some code that checks for
> duplicate
> rows, but it does not catch the following case:
>
> CREATE TABLE t(a text, b text);
> INSERT INTO t VALUES('test', null);
> CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
> CREATE UNIQUE INDEX ON m(a);
> INSERT INTO t VALUES('test', null); -- t now contains two identical rows
>
> REFRESH MATERIALIZED VIEW CONCURRENTLY m;
>      -> no error, but m still contains only one row!
> REFRESH MATERIALIZED VIEW m;
>      -> error (as expected)
>
Interesting issue and thanks for pointing it out.
Going over the code in the function you mentioned(refresh_by_match_merge()
in matview.c), I found out that it is explicitly checking for the columns
where it is not NULL.
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.* "
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
"newdata.ctid)",
It is mentioned in the comments above as well that it checks for the
duplicates in the rows without NULLs.
However, if I changed the query as in the attached patch, it errors out as
otherwise I would have expected.
Honestly, I do not understand why it is checking for duplicates excluding
null values.
Behaviour wise this definitely seems like a bug, but I am not sure if the
attached patch is the right way to fix it.

-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

Attachments:

0001-Check-for-duplicate-rows-with-NULLs.patch (application/octet-stream, 1.2K) download From 0ca4b1214c86f1b959e4339cc9b8e0a1c6aa0fd3 Mon Sep 17 00:00:00 2001 From: Rafia Sabih <rafia.sabih@cybertec.at> Date: Wed, 11 Feb 2026 11:38:07 -0800 Subject: [PATCH] Check for duplicate rows with NULLs In case of concurrent refresh of materialized view, check for columns with NULL values. If there are duplicate entries with NULLS then error out. --- src/backend/commands/matview.c | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..48bb2582a92 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -648,9 +648,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); -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-11 19:56  surya poondla <suryapoondla4@gmail.com>
  parent: Giuliano Gagliardi <gogi@gogi.tv>
  1 sibling, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-02-11 19:56 UTC (permalink / raw)
  To: Giuliano Gagliardi <gogi@gogi.tv>; +Cc: pgsql-bugs@lists.postgresql.org

Hi Giuliano,

Thank you for the test case, yes I am able to reproduce the behavior for
issue1

I noticed the following two issues while looking at the code that handles
> REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in
> matview.c):
>
> 1.
>
> At the beginning of the function, there is some code that checks for
> duplicate
> rows, but it does not catch the following case:
>
> CREATE TABLE t(a text, b text);
> INSERT INTO t VALUES('test', null);
> CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
> CREATE UNIQUE INDEX ON m(a);
> INSERT INTO t VALUES('test', null); -- t now contains two identical rows
>
> REFRESH MATERIALIZED VIEW CONCURRENTLY m;
>      -> no error, but m still contains only one row!
> REFRESH MATERIALIZED VIEW m;
>      -> error (as expected)
>
> Adding the output here for a complete picture.
postgres=# CREATE TABLE t(a text, b text);
CREATE TABLE
postgres=# INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=# CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=# CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=# INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY m;
REFRESH MATERIALIZED VIEW
postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)
postgres=# REFRESH MATERIALIZED VIEW m;
ERROR:  could not create unique index "m_a_idx"
DETAIL:  Key (a)=(test) is duplicated.
postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)

Yes, I believe "REFRESH MATERIALIZED VIEW CONCURRENTLY m;" should ideally
throw the same error as REFRESH MATERIALIZED VIEW m;

I am still trying to understand the CONCURRENTLY behavior in detail and
will share more of my findings on this potential issue.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-12 00:34  surya poondla <suryapoondla4@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-02-12 00:34 UTC (permalink / raw)
  To: Giuliano Gagliardi <gogi@gogi.tv>; +Cc: pgsql-bugs@lists.postgresql.org

Hi Giuliano,

Regarding the issue 1,
>
> I noticed the following two issues while looking at the code that handles
> REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in
> matview.c):
>
> 1.
>
> At the beginning of the function, there is some code that checks for
> duplicate
> rows, but it does not catch the following case:
>
> CREATE TABLE t(a text, b text);
> INSERT INTO t VALUES('test', null);
> CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
> CREATE UNIQUE INDEX ON m(a);
> INSERT INTO t VALUES('test', null); -- t now contains two identical rows
>
> REFRESH MATERIALIZED VIEW CONCURRENTLY m;
>      -> no error, but m still contains only one row!
> REFRESH MATERIALIZED VIEW m;
>      -> error (as expected)
>
>
Thank you for the pointers, I made a patch in refresh_by_match_merge()
which reports an error in the REFRESH MATERIALIZED VIEW CONCURRENTLY case
too.

The issue was REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly
skipping duplicate detection for rows containing any NULL values. This was
happening because the "WHERE newdata.* IS NOT NULL" condition returns false
if any column contains NULL.
My patch 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 (i.e True).

Here is the output with my patch:
postgres=# CREATE TABLE t(a text, b text);
CREATE TABLE
postgres=#   INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=#
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=#
postgres=# CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)

postgres=# SELECT * FROM t;
  a   | b
------+---
 test |
(1 row)

postgres=# INSERT INTO t VALUES('test', null);
INSERT 0 1
postgres=# SELECT * FROM t;
  a   | b
------+---
 test |
 test |
(2 rows)

postgres=# SELECT * FROM m;
  a   | b
------+---
 test |
(1 row)

postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY m;
2026-02-11 15:57:46.751 PST [39510] ERROR:  new data for materialized view
"m" contains duplicate rows
2026-02-11 15:57:46.751 PST [39510] DETAIL:  Row: (test,)
2026-02-11 15:57:46.751 PST [39510] STATEMENT:  REFRESH MATERIALIZED VIEW
CONCURRENTLY m;
ERROR:  new data for materialized view "m" contains duplicate rows
DETAIL:  Row: (test,)
postgres=#
postgres=# REFRESH MATERIALIZED VIEW m;
2026-02-11 15:57:55.877 PST [39510] ERROR:  could not create unique index
"m_a_idx"
2026-02-11 15:57:55.877 PST [39510] DETAIL:  Key (a)=(test) is duplicated.
2026-02-11 15:57:55.877 PST [39510] STATEMENT:  REFRESH MATERIALIZED VIEW m;
ERROR:  could not create unique index "m_a_idx"
DETAIL:  Key (a)=(test) is duplicated.
postgres=#

Regards,
Surya Poondla

Attachments:

0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-detect.patch (application/octet-stream, 3.9K) download From d26e084ed00cde21d917cd4dc52f2ab1467fdc19 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Wed, 11 Feb 2026 16:06:56 -0800 Subject: [PATCH] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY to detect NULL containing duplicates. 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. --- src/backend/commands/matview.c | 20 +++++++++++--------- 1 file changed, 11 insertions(+), 9 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..6aaf7a68c20 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -635,11 +635,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 +650,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 +669,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)))); -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-12 15:47  surya poondla <suryapoondla4@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-02-12 15:47 UTC (permalink / raw)
  To: Giuliano Gagliardi <gogi@gogi.tv>; +Cc: pgsql-bugs@lists.postgresql.org

Hi All,

Looks like postgres mailing threads has some delay, the mailing list was
not updated timely and I couldn't see the patch that Rafia sent earlier
yesterday. I could only see their patch today and coincedentally it matches
the patch I suggested.

Right now, I am exploring the issue 2.

Regards,
Surya Poondla

>

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-13 02:08  surya poondla <suryapoondla4@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-02-13 02:08 UTC (permalink / raw)
  To: Giuliano Gagliardi <gogi@gogi.tv>; +Cc: pgsql-bugs@lists.postgresql.org

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:

0002-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-NULL-hand.patch (application/octet-stream, 9.6K) download 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)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-13 23:37  Rafia Sabih <rafia.pghackers@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: Rafia Sabih @ 2026-02-13 23:37 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

On Thu, 12 Feb 2026 at 18:08, surya poondla <suryapoondla4@gmail.com> wrote:

> 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.
>
> Thanks for working on this.
Firstly, since both are different issues, it makes sense to write patches
for each of them separately.
Secondly, for issue 1 it is important to understand why the code was
explicitly done for null columns, what are the scenarios in which this
modified code could cause issues.
Also, for issue 1, additional test case should be added.

For issue 2, it would be helpful if you may share some performance numbers
to confirm if this solution is only improving the performance and not
causing any regressions.
-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-02-18 22:32  surya poondla <suryapoondla4@gmail.com>
  parent: Rafia Sabih <rafia.pghackers@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-02-18 22:32 UTC (permalink / raw)
  To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

Hi All,


Also, for issue 1, additional test case should be added.
>
Sure, I will add test cases for issue 1.


For issue 2, it would be helpful if you may share some performance numbers
> to confirm if this solution is only improving the performance and not
> causing any regressions.
>
I ran check, check-world and didn't see any regressions.

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
>>
> Regrading the performance, (quoting the output from my previous message)
with unique index having NULL values we see that both "REFRESH MATERIALIZED
VIEW CONCURRENTLY s;" operations (operation 1 was after CREATE UNIQUE INDEX
ON s(x); and operation 2 was after CREATE UNIQUE INDEX ON s(x);) take about
the same time. Without the patch, operation 2 was taking around ~11000
ms, due to NULL = NULL comparison checks and this was causing the
degradation.

Regarding different commits to each issue, I don't have any
particular opinion but since both the issues are related to the same
function and NULL comparison, I feel we can have a single commit, but open
to create 2 commits too.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-02 22:45  surya poondla <suryapoondla4@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-03-02 22:45 UTC (permalink / raw)
  To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

Hi All,

Thank you Rafia for the suggestions.
I split both the bugs in 2 different commits, attaching the patches here.

For bug1, I added the test case for NULL values too.

For bug 2, I only changed matview.c and added no test case as the timings
are not constant.

I ran the regression tests for both the patches and all tests succeeded in
both cases.

Regards,
Surya Poondla

>

Attachments: download all (2 files, .tar.gz)

0003-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-detect_bug1.patch (application/octet-stream, 6.7K) download From a74bf7261d82f84b27396221e60cec440045dad5 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:22:11 -0800 Subject: [PATCH v3] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY to detect duplicate rows with NULLs 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. Added a regression test covering the case of duplicate rows that contain NULL values. Note: The non-concurrent REFRESH was unaffected since it rebuilds indexes from scratch, which properly detects duplicates during index creation. --- src/backend/commands/matview.c | 20 +++++++++++--------- src/test/regress/expected/matview.out | 16 +++++++++++++++- src/test/regress/sql/matview.sql | 10 ++++++++++ 3 files changed, 36 insertions(+), 10 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..e90b10972ef 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -635,11 +635,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 +650,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 +669,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)))); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 0355720dfc6..3f66ada1b6b 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -396,10 +396,24 @@ 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 +-- test that duplicate rows containing NULLs are also detected (bug fix) +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +ERROR: could not create unique index "mvtest_mv_a_idx" +DETAIL: Key (a)=(test) is duplicated. +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +ERROR: new data for materialized view "mvtest_mv" contains duplicate rows +DETAIL: Row: (test,) +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..d14a5b95384 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -135,6 +135,16 @@ REFRESH MATERIALIZED VIEW mvtest_mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; DROP TABLE mvtest_foo CASCADE; +-- test that duplicate rows containing NULLs are also detected (bug fix) +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; -- 2.39.5 (Apple Git-154)
0003-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-performance_bug2.patch (application/octet-stream, 5.0K) download From 982d721013abf04b5eba36ce1abab350894883b2 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:23:17 -0800 Subject: [PATCH v3] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY performance with nullable indexed columns Issue: When a materialized view has a unique index on a nullable column, REFRESH MATERIALIZED VIEW CONCURRENTLY included that column in the FULL OUTER JOIN condition used to detect changes. This caused severe performance degradation because standard equality (col = col) evaluates to NULL when either side is NULL, causing unchanged rows to appear as both deleted and re-inserted. 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, so 2M changes in 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 equality 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 do not guarantee row uniqueness for NULL values (multiple NULLs are allowed). The record equality operator (*=), which is always appended to the join predicate, already handles all columns including nullable ones (i.e treating NULL as equal to NULL.) 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 | 30 +++++++++++++++++++++++++++--- 1 file changed, 27 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..236c233914c 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; @@ -715,6 +716,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); @@ -732,6 +734,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, Datum indclassDatum; int i; + foundUniqueIndex = true; + /* Must get indclass the hard way. */ indclassDatum = SysCacheGetAttrNotNull(INDEXRELID, indexRel->rd_indextuple, @@ -753,6 +757,18 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, const char *leftop; const char *rightop; + /* + * Skip nullable columns. Nullable columns in unique indexes + * don't provide row uniqueness for NULL values (multiple NULLs + * are allowed), so they cannot reliably identify matching rows. + * Including them in the join condition causes NULL = NULL + * comparisons which evaluate to NULL, making unchanged rows + * appear different. The record equality operator (*=) appended + * below handles all columns including nullable ones. + */ + 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 +804,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 +817,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, op, rightop, attrtype); - foundUniqueIndex = true; + addedAnyQuals = true; } } @@ -826,8 +842,16 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, errmsg("could not find suitable unique index on materialized view \"%s\"", RelationGetRelationName(matviewRel))); + /* + * The record equality operator (*=) is always included in the join + * predicate. It handles all columns correctly including nullable ones + * that were skipped above, since *= treats NULL as equal to NULL. + */ + 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"); -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-05 01:01  Rafia Sabih <rafia.pghackers@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: Rafia Sabih @ 2026-03-05 01:01 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

On Mon, 2 Mar 2026 at 14:45, surya poondla <suryapoondla4@gmail.com> wrote:

> Hi All,
>
> Thank you Rafia for the suggestions.
> I split both the bugs in 2 different commits, attaching the patches here.
>
> For bug1, I added the test case for NULL values too.
>
> Thanks for working on this. This looks good to me.
+-- test that duplicate rows containing NULLs are also detected (bug fix)
I wouldn't use bug fix here, it looks fine without it.

>

-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-09 13:57  Rafia Sabih <rafia.pghackers@gmail.com>
  parent: Rafia Sabih <rafia.pghackers@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: Rafia Sabih @ 2026-03-09 13:57 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

In order to take this further, I think it would be good to add this to
commitfest.

On Wed, 4 Mar 2026 at 17:01, Rafia Sabih <rafia.pghackers@gmail.com> wrote:

>
>
> On Mon, 2 Mar 2026 at 14:45, surya poondla <suryapoondla4@gmail.com>
> wrote:
>
>> Hi All,
>>
>> Thank you Rafia for the suggestions.
>> I split both the bugs in 2 different commits, attaching the patches here.
>>
>> For bug1, I added the test case for NULL values too.
>>
>> Thanks for working on this. This looks good to me.
> +-- test that duplicate rows containing NULLs are also detected (bug fix)
> I wouldn't use bug fix here, it looks fine without it.
>
>>
>
> --
> Regards,
> Rafia Sabih
> CYBERTEC PostgreSQL International GmbH
>


-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-12 00:45  surya poondla <suryapoondla4@gmail.com>
  parent: Rafia Sabih <rafia.pghackers@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-03-12 00:45 UTC (permalink / raw)
  To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

Hi Rafia,
Thank you for the suggestion.

I created the commit fest entries:
https://commitfest.postgresql.org/patch/6579/ (for bug 1)
https://commitfest.postgresql.org/patch/6580/ (for the performance
improvement)

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-12 11:33  cca5507 <cca5507@qq.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: cca5507 @ 2026-03-12 11:33 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi,

I think we might want the "*=" operator treat NULL as not equal to NULL and
this is why we add "IS NOT NULL" to the duplicate detection query.

Your patch treats NULL as equal to NULL, which is different from the SQL
standard, may confuse users.

So I think we should make the "*=" operator treat NULL as not equal to NULL
or add a new operator to implement it. Thoughts?

--
Regards,
ChangAo Chen


^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-13 07:20  cca5507 <cca5507@qq.com>
  parent: cca5507 <cca5507@qq.com>
  0 siblings, 1 reply; 26+ messages in thread

From: cca5507 @ 2026-03-13 07:20 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

> I think we might want the "*=" operator treat NULL as not equal to NULL and
> this is why we add "IS NOT NULL" to the duplicate detection query.
> 
> Your patch treats NULL as equal to NULL, which is different from the SQL
> standard, may confuse users.
> 
> So I think we should make the "*=" operator treat NULL as not equal to NULL
> or add a new operator to implement it. Thoughts?

Attach a patch. I add a new built-in function called record_image_eq_variant
which considers two NULLs not equal so that each row can match at most one
row during the full join.

--
Regards,
ChangAo Chen


Attachments:

v4-0001-Fix-wrong-result-of-refresh-matview-concurrently.patch (application/octet-stream, 7.1K) download From cde9f3c1af6aa1c2c33db651af31a81fd5f9a724 Mon Sep 17 00:00:00 2001 From: ChangAo Chen <cca5507@qq.com> Date: Fri, 13 Mar 2026 14:53:21 +0800 Subject: [PATCH v4] Fix wrong result of refresh matview concurrently. During populating the diff table, we use the "*=" operator which considers two NULLs equal. This can lead to one row may match more than one row during the full join. If this happens, we will get a wrong diff table. To fix it, we add a new built-in function called record_image_eq_variant which considers two NULLs not equal and use it when populating the diff table. This makes sure that each row can match at most one row during the full join. --- src/backend/commands/matview.c | 6 +++- src/backend/utils/adt/rowtypes.c | 46 +++++++++++++++++---------- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 5 +++ src/test/regress/expected/matview.out | 18 +++++++++++ src/test/regress/sql/matview.sql | 17 ++++++++++ 6 files changed, 75 insertions(+), 19 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..fc63e86eac1 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -826,8 +826,12 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, errmsg("could not find suitable unique index on materialized view \"%s\"", RelationGetRelationName(matviewRel))); + /* + * We use record_image_eq_variant which considers two NULLs not equal + * so that each row can match at most one row during the full join. + */ appendStringInfoString(&querybuf, - " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) " + " AND pg_catalog.record_image_eq_variant(newdata.*, mv.*)) " "WHERE newdata.* IS NULL OR mv.* IS NULL " "ORDER BY tid"); diff --git a/src/backend/utils/adt/rowtypes.c b/src/backend/utils/adt/rowtypes.c index e4eb7111ee7..64592162fab 100644 --- a/src/backend/utils/adt/rowtypes.c +++ b/src/backend/utils/adt/rowtypes.c @@ -1583,16 +1583,19 @@ record_image_cmp(FunctionCallInfo fcinfo) } /* - * record_image_eq : + * record_image_eq_internal : * compares two records for identical contents, based on byte images * result : * returns true if the records are identical, false otherwise. * * Note: we do not use record_image_cmp here, since we can avoid * de-toasting for unequal lengths this way. + * + * Note: we consider two NULLs equal if null_equals_null is true, otherwise + * not equal. */ -Datum -record_image_eq(PG_FUNCTION_ARGS) +static bool +record_image_eq_internal(FunctionCallInfo fcinfo, bool null_equals_null) { HeapTupleHeader record1 = PG_GETARG_HEAPTUPLEHEADER(0); HeapTupleHeader record2 = PG_GETARG_HEAPTUPLEHEADER(1); @@ -1720,21 +1723,18 @@ record_image_eq(PG_FUNCTION_ARGS) j + 1))); /* - * We consider two NULLs equal; NULL > not-NULL. + * We consider two NULLs equal if null_equals_null is + * true, otherwise not equal. */ - if (!nulls1[i1] || !nulls2[i2]) - { - if (nulls1[i1] || nulls2[i2]) - { - result = false; - break; - } - - /* Compare the pair of elements */ + if (nulls1[i1] && nulls2[i2]) + result = null_equals_null; + else if (nulls1[i1] || nulls2[i2]) + result = false; + else result = datum_image_eq(values1[i1], values2[i2], att1->attbyval, att2->attlen); - if (!result) - break; - } + + if (!result) + break; /* equal, so continue to next column */ i1++, i2++, j++; @@ -1764,7 +1764,19 @@ record_image_eq(PG_FUNCTION_ARGS) PG_FREE_IF_COPY(record1, 0); PG_FREE_IF_COPY(record2, 1); - PG_RETURN_BOOL(result); + return result; +} + +Datum +record_image_eq(PG_FUNCTION_ARGS) +{ + PG_RETURN_BOOL(record_image_eq_internal(fcinfo, true)); +} + +Datum +record_image_eq_variant(PG_FUNCTION_ARGS) +{ + PG_RETURN_BOOL(record_image_eq_internal(fcinfo, false)); } Datum diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 90f46b03502..3da6a75ff87 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202603101 +#define CATALOG_VERSION_NO 202603131 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 361e2cfffeb..42e7d5f946d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10856,6 +10856,11 @@ proname => 'btequalimage', prorettype => 'bool', proargtypes => 'oid', prosrc => 'btequalimage' }, +# used for REFRESH MATERIALIZED VIEW CONCURRENTLY +{ oid => '9145', descr => 'variant of record_image_eq', + proname => 'record_image_eq_variant', prorettype => 'bool', + proargtypes => 'record record', prosrc => 'record_image_eq_variant' }, + # Extensions { oid => '3082', descr => 'list available extensions', proname => 'pg_available_extensions', procost => '10', prorows => '100', diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 0355720dfc6..ebd9d806698 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -400,6 +400,24 @@ ERROR: new data for materialized view "mvtest_mv" contains duplicate rows witho DETAIL: Row: (1,10) DROP TABLE mvtest_foo CASCADE; NOTICE: drop cascades to materialized view mvtest_mv +-- test that two NULLs not equal when populating the diff table +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(1, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo SELECT * FROM mvtest_foo; +DO $$ +BEGIN + BEGIN + REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; + EXCEPTION + WHEN unique_violation THEN RAISE NOTICE 'unique violation'; + END; +END; +$$; +NOTICE: unique violation +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..a27f81c349d 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -135,6 +135,23 @@ REFRESH MATERIALIZED VIEW mvtest_mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; DROP TABLE mvtest_foo CASCADE; +-- test that two NULLs not equal when populating the diff table +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(1, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo SELECT * FROM mvtest_foo; +DO $$ +BEGIN + BEGIN + REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; + EXCEPTION + WHEN unique_violation THEN RAISE NOTICE 'unique violation'; + END; +END; +$$; +DROP TABLE mvtest_foo CASCADE; + -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; -- 2.34.1
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-20 18:01  surya poondla <suryapoondla4@gmail.com>
  parent: cca5507 <cca5507@qq.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-03-20 18:01 UTC (permalink / raw)
  To: cca5507 <cca5507@qq.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi ChangAo,

Thank you for the detailed review.

For issue 1, my fix removes the IS NOT NULL guard from the pre-check so
that *= can detect all duplicate rows, including those containing NULLs.
(Note: The semantics of *=  has always treated NULL as equal to NULL.)

The reasoning is straightforward: the JOIN uses *= to match newdata rows
against MV rows. If newdata contains two *=-equal rows, both would match
the same MV row in the JOIN, producing a wrong diff. The pre-check must
therefore use the same *= semantics to catch exactly those duplicates
which is what my fix does by removing the IS NOT NULL guard.
The IS NOT NULL guard was the bug as it was hiding real duplicates from
detection.

Your approach leaves the pre-check unchanged and instead replaces *= in
the JOIN with record_image_eq_variant (NULL != NULL). I see two concerns:
1. record_image_eq_variant applies NULL != NULL globally to all rows in
   the JOIN, not just duplicate ones. This means any unchanged row
   containing any NULL in any column will never match its counterpart
   during the JOIN, causing a DELETE + INSERT for that row on every
   refresh even when the data has not changed. The original issue 2 was
   specifically about nullable indexed columns, your fix extends the
   performance problem to all nullable columns anywhere in the row,
   which makes the performance worse than issue 2.
2. The error surfaced becomes a unique_violation from the index rather
   than the explicit "contains duplicate rows" message, which is harder
   for users to diagnose.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-21 06:19  cca5507 <cca5507@qq.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: cca5507 @ 2026-03-21 06:19 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi Surya,

> For issue 1, my fix removes the IS NOT NULL guard from the pre-check so
> that *= can detect all duplicate rows, including those containing NULLs.
> (Note: The semantics of *=  has always treated NULL as equal to NULL.)
> 
> The reasoning is straightforward: the JOIN uses *= to match newdata rows
> against MV rows. If newdata contains two *=-equal rows, both would match
> the same MV row in the JOIN, producing a wrong diff. The pre-check must
> therefore use the same *= semantics to catch exactly those duplicates 
> which is what my fix does by removing the IS NOT NULL guard.
> The IS NOT NULL guard was the bug as it was hiding real duplicates from detection.

If I understand correctly, your fix will break the following case which works well currently:

CREATE TABLE t (a int, b int);
INSERT INTO t VALUES (null, null);
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
CREATE UNIQUE INDEX ON m(a);
INSERT INTO t VALUES (null, null);
REFRESH MATERIALIZED VIEW CONCURRENTLY m;

Your fix will report an error because of the two (null, null) rows. On master, this case
works well because of the join condition "mv.a = newdada.a" which considers two
NULLs not equal, so we will get a correct diff table.

> Your approach leaves the pre-check unchanged and instead replaces *= in
> the JOIN with record_image_eq_variant (NULL != NULL). I see two concerns:
> 1. record_image_eq_variant applies NULL != NULL globally to all rows in
>    the JOIN, not just duplicate ones. This means any unchanged row
>    containing any NULL in any column will never match its counterpart
>    during the JOIN, causing a DELETE + INSERT for that row on every
>    refresh even when the data has not changed. The original issue 2 was
>    specifically about nullable indexed columns, your fix extends the
>    performance problem to all nullable columns anywhere in the row,
>    which makes the performance worse than issue 2.

Yeah, you're right. Any row containing any NULL in any column will get into the
diff table. But it's for correctness. Maybe user should avoid using CONCURRENTLY
with a lot of rows containing NULL.

> 2. The error surfaced becomes a unique_violation from the index rather
>    than the explicit "contains duplicate rows" message, which is harder
>    for users to diagnose.

I don't think it's a big issue.

--
Regards,
ChangAo Chen


^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-03-25 22:45  surya poondla <suryapoondla4@gmail.com>
  parent: cca5507 <cca5507@qq.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-03-25 22:45 UTC (permalink / raw)
  To: cca5507 <cca5507@qq.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi ChangAo,

Thank you for the continued review, your test case helped me find a
real issue in v3 patch, and I updated the fix accordingly.

You were correct that v3 was wrong. Removing IS NOT NULL and using *= alone
in the pre-check was too aggressive: *= treats NULL=NULL=true, which caused
(NULL,NULL)×2 with a unique index on a nullable column to be flagged
as duplicates even though the unique index allows multiple NULLs.

The updated patch takes a different approach. Instead of removing the guard
and relying on *= alone, the pre-check now uses the same per-column equality
operators as the FULL OUTER JOIN accumulated during the same
index-scanning loop. These operators treat NULL=NULL as false, which is
consistent with how
unique indexes actually work (NULLs are distinct).

For (test, NULL)×2 with a single index on a (non-null):
newdata2.a = newdata.a i.e 'test'='test' that is TRUE
newdata2.* *= newdata.* that is TRUE
Thus the duplicate is caught and error is raised

For (NULL, NULL)×2 with a single index on a (nullable):
newdata2.a = newdata.a i.e NULL=NULL that is NULL (false)
Thus no duplicate is caught, the refresh correctly succeeds, MV gets 2 rows

For (test, NULL)×2 with a composite index on (a, b):
newdata2.a = newdata.a i.e TRUE
newdata2.b = newdata.b i.e NULL=NULL that is NULL (false)
Combined: NULL is not caught, refresh correctly succeeds

Regarding your record_image_eq_variant approach: it correctly handles the
NULL-in-indexed-column case, but it introduces a performance regression for
unchanged rows where any non-indexed column contains NULL.
For example, an unchanged row (1, NULL) with a unique index on non-null
index on a would require a DELETE+INSERT on every CONCURRENTLY refresh
because
record_image_eq_variant((1,NULL),(1,NULL)) returns false.
This makes CONCURRENTLY impractical for any table where rows contain NULLs
in non-indexed columns.

The updated patch for bug 1.

Here is some additional tests I did:
postgres=# SET client_min_messages = WARNING;
SET
postgres=# -- Test 1: (test,NULL)×2, single index on 'a' should ERROR
postgres=#   CREATE TABLE t(a text, b text);
  INSERT INTO t VALUES('test', NULL);
  CREATE MATERIALIZED VCREATE TABLE
postgres=#   INSERT INTO t VALUES('test', NULL);
INSERT 0 1
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
  CREATE UNIQUE INDEX ON m(a);
  INSERT INTO t VALUES('test', NULL);
  REFSELECT 1
postgres=#   CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=#   INSERT INTO t VALUES('test', NULL);
INSERT 0 1
postgres=#   REFRESH MATERIALIZED VIEW CONCURRENTLY m;  -- must error
ERROR:  new data for materialized view "m" contains duplicate rows
DETAIL:  Row: (test,)
postgres=#   DROP TABLE t CASCADE;
DROP TABLE

postgres=# -- Test 2: (NULL,NULL)×2, single index on 'a' should SUCCEED
postgres=#   CREATE TABLE t(a int, b int);
CREATE TABLE
postgres=#   INSERT INTO t VALUES(NULL, NULL);
INSERT 0 1
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=#   CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=#   INSERT INTO t VALUES(NULL, NULL);
INSERT 0 1
postgres=#   REFRESH MATERIALIZED VIEW CONCURRENTLY m;  -- must succeed
  SELECT COUNT(*) FROM m;
  DROP TABLE t CASCADE;REFRESH MATERIALIZED VIEW
postgres=#   SELECT COUNT(*) FROM m;  -- should be 2
 count
-------
     2
(1 row)
postgres=#   DROP TABLE t CASCADE;
DROP TABLE

postgres=# --Test 3: (test,NULL)×2, composite index (a,b) should SUCCEED
postgres=#   CREATE TABLE t(a text, b text);
CREATE TABLE
postgres=#   INSERT INTO t VALUES('test', NULL);
INSERT 0 1
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=#   CREATE UNIQUE INDEX ON m(a, b);
  INSERT INTO t VALUES('test', NUL  CREATE UNIQUE INDEX ON m(a, b);
CREATE INDEX
postgres=#   INSERT INTO t VALUES('test', NULL);
INSERT 0 1
postgres=#   REFRESH MATERIALIZED VIEW CONCURRENTLY m;  -- must succeed
REFRESH MATERIALIZED VIEW
postgres=#   SELECT COUNT(*) FROM m;
  DROP TABLE t CASCADE;
  SELECT COUNT(*) FROM m;  -- must be 2
 count
-------
     2
(1 row)
postgres=#   DROP TABLE t CASCADE;
DROP TABLE

postgres=# -- Test 4: unchanged (1,NULL), index on 'a' should SUCCEED.
postgres=#   CREATE TABLE t(a int, b int);
CREATE TABLE
postgres=#   INSERT INTO t VALUES(1, NULL);
INSERT 0 1
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=#   CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=#   REFRESH MATERIALIZED VIEW CONCURRENTLY m;  -- must succeed
REFRESH MATERIALIZED VIEW
postgres=#   SELECT * FROM m;  -- must still show (1,)
 a | b
---+---
 1 |
(1 row)
postgres=#   DROP TABLE t CASCADE;
DROP TABLE

postgres=# -- Test 5: (1,NULL)×2, separate index on a AND b, should ERROR
postgres=#   CREATE TABLE t(a int, b int);
CREATE TABLE
postgres=#   INSERT INTO t VALUES(1, NULL);
INSERT 0 1
postgres=#   CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
SELECT 1
postgres=#   CREATE UNIQUE INDEX ON m(a);
CREATE INDEX
postgres=#   CREATE UNIQUE INDEX ON m(b);
CREATE INDEX
postgres=#   INSERT INTO t VALUES(1, NULL);
INSERT 0 1
postgres=#   REFRESH MATERIALIZED VIEW CONCURRENTLY m;  -- must error
ERROR:  duplicate key value violates unique constraint "m_a_idx"
DETAIL:  Key (a)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO public.m SELECT (diff.newdata).* FROM
pg_temp_2.pg_temp_16535_2 diff WHERE tid IS NULL"
postgres=#   DROP TABLE t CASCADE;
DROP TABLE
postgres=#

Made some minor changes to bug2 patch too.

Regards,
Surya Poondla

Attachments: download all (2 files, .tar.gz)

0005-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-detect_bug1.patch (application/octet-stream, 11.4K) download From f8419fe83c6e6831ff987b53f2bcae2f3173029b Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:22:11 -0800 Subject: [PATCH v5] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY to detect duplicate rows REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly skipping duplicate detection for rows containing NULL values. The pre-check query used "WHERE newdata.* IS NOT NULL" which caused rows with any NULL column to bypass duplicate detection entirely. The fix rebuilds the pre-check using the same per-column equality operators used by the diff JOIN (one condition per unique-indexed column). These operators treat NULL as not equal to NULL, matching unique index semantics where NULLs are considered distinct. Only rows whose indexed columns are non-null and equal are flagged as duplicates --- the same rows that would cause join ambiguity. This correctly handles two cases that the old approach got wrong: - (test, NULL) x2 with index on a: a='test' is non-null and duplicated, so the duplicate is correctly detected and an error is raised. - (NULL, NULL) x2 with index on a: a=NULL, and unique indexes allow multiple NULLs (each is treated as distinct), so the refresh correctly succeeds and updates the view to two rows. Added regression tests covering both cases. --- src/backend/commands/matview.c | 122 ++++++++++++++++++-------- src/test/regress/expected/matview.out | 33 ++++++- src/test/regress/sql/matview.sql | 22 +++++ 3 files changed, 137 insertions(+), 40 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..2a1694f3b4e 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -606,6 +606,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, ListCell *indexoidscan; int16 relnatts; Oid *opUsedForQual; + StringInfoData precheck_cond_buf; + bool precheck_has_cond; initStringInfo(&querybuf); matviewRel = table_open(matviewOid, NoLock); @@ -634,45 +636,6 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) 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. - * - * Note: here and below, we use "tablename.*::tablerowtype" as a hack to - * keep ".*" from being expanded into multiple columns in a SELECT list. - * Compare ruleutils.c's get_variable(). - */ - 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.* " - "AND newdata2.ctid OPERATOR(pg_catalog.<>) " - "newdata.ctid)", - tempname, tempname, tempname); - if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) - elog(ERROR, "SPI_exec failed: %s", querybuf.data); - if (SPI_processed > 0) - { - /* - * Note that this ereport() is returning data to the user. Generally, - * we would want to make sure that the user has been granted access to - * this data. However, REFRESH MAT VIEW is only able to be run by the - * owner of the mat view (or a superuser) and therefore there is no - * need to check for access to data in the mat view. - */ - ereport(ERROR, - (errcode(ERRCODE_CARDINALITY_VIOLATION), - errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns", - RelationGetRelationName(matviewRel)), - errdetail("Row: %s", - SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); - } - /* * Create the temporary "diff" table. * @@ -715,6 +678,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, tupdesc = matviewRel->rd_att; opUsedForQual = palloc0_array(Oid, relnatts); foundUniqueIndex = false; + initStringInfo(&precheck_cond_buf); + precheck_has_cond = false; indexoidlist = RelationGetIndexList(matviewRel); @@ -802,6 +767,30 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, rightop, attrtype); foundUniqueIndex = true; + + /* + * Also accumulate the same condition for the duplicate-row + * pre-check, comparing newdata2 against newdata (instead of + * newdata against mv). This lets us detect rows that are + * duplicates with respect to the unique index semantics --- + * i.e. rows whose indexed columns are non-null and equal --- + * without falsely flagging rows whose indexed columns are + * NULL (which unique indexes treat as distinct). + */ + if (precheck_has_cond) + appendStringInfoString(&precheck_cond_buf, " AND "); + + leftop = quote_qualified_identifier("newdata2", + NameStr(attr->attname)); + rightop = quote_qualified_identifier("newdata", + NameStr(attr->attname)); + + generate_operator_clause(&precheck_cond_buf, + leftop, attrtype, + op, + rightop, attrtype); + + precheck_has_cond = true; } } @@ -831,6 +820,61 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, "WHERE newdata.* IS NULL OR mv.* IS NULL " "ORDER BY tid"); + /* + * Before populating the diff table, check for duplicate rows in the + * new data set. We look for rows that are equal in all unique index + * columns (using the same operators as the join above, where + * NULL = NULL is false) AND are also *=-equal overall. Such rows + * would both match the same materialized view row in the join, + * producing an ambiguous diff. + * + * Using index column operators rather than *= alone is important: it + * correctly excludes rows whose indexed columns are NULL, because + * unique indexes treat NULLs as distinct so those rows do not cause + * join ambiguity. + * + * Note: here and below, we use "tablename.*::tablerowtype" as a hack + * to keep ".*" from being expanded into multiple columns in a SELECT + * list. Compare ruleutils.c's get_variable(). + * + * Even after we pass this test, a unique index on the materialized + * view may find a duplicate key problem. + */ + { + StringInfoData precheck_querybuf; + + initStringInfo(&precheck_querybuf); + appendStringInfo(&precheck_querybuf, + "SELECT newdata.*::%s FROM %s newdata " + "WHERE EXISTS " + "(SELECT 1 FROM %s newdata2 WHERE %s" + " AND newdata2.* OPERATOR(pg_catalog.*=) newdata.*" + " AND newdata2.ctid OPERATOR(pg_catalog.<>) newdata.ctid)", + tempname, tempname, tempname, precheck_cond_buf.data); + + if (SPI_execute(precheck_querybuf.data, false, 1) != SPI_OK_SELECT) + elog(ERROR, "SPI_exec failed: %s", precheck_querybuf.data); + + if (SPI_processed > 0) + { + /* + * Note that this ereport() is returning data to the user. + * Generally, we would want to make sure that the user has been + * granted access to this data. However, REFRESH MAT VIEW is + * only able to be run by the owner of the mat view (or a + * superuser) and therefore there is no need to check for access + * to data in the mat view. + */ + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + 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)))); + } + } + /* Populate the temporary "diff" table. */ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 0355720dfc6..1dcff26a2e4 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -396,8 +396,39 @@ 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 +-- test that duplicate rows containing NULLs in non-indexed columns are detected +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +ERROR: could not create unique index "mvtest_mv_a_idx" +DETAIL: Key (a)=(test) is duplicated. +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +ERROR: new data for materialized view "mvtest_mv" contains duplicate rows +DETAIL: Row: (test,) +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- test that rows with NULLs in the indexed column are not false positives: +-- unique indexes treat NULLs as distinct, so (NULL,NULL)x2 is a valid state +-- and CONCURRENTLY should succeed and update the view to reflect both rows +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT COUNT(*) FROM mvtest_mv; + count +------- + 2 +(1 row) + DROP TABLE mvtest_foo CASCADE; NOTICE: drop cascades to materialized view mvtest_mv -- make sure that all columns covered by unique indexes works diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..e65cd4fa94b 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -135,6 +135,28 @@ REFRESH MATERIALIZED VIEW mvtest_mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; DROP TABLE mvtest_foo CASCADE; +-- test that duplicate rows containing NULLs in non-indexed columns are detected +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + +-- test that rows with NULLs in the indexed column are not false positives: +-- unique indexes treat NULLs as distinct, so (NULL,NULL)x2 is a valid state +-- and CONCURRENTLY should succeed and update the view to reflect both rows +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT COUNT(*) FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; -- 2.39.5 (Apple Git-154)
0005-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-performance_bug2.patch (application/octet-stream, 10.0K) download From 15fb766ee6cf7245092fabd7c506b880f9f0b6b6 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:23:17 -0800 Subject: [PATCH v5] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY performance with nullable indexed columns When a materialized view has a unique index on a nullable column, the join condition used to detect changes included NULL = NULL comparisons which evaluate to NULL (false), causing every row with a NULL indexed value to appear as changed on every CONCURRENTLY refresh even when the data was unchanged. The fix skips nullable columns from the per-column join conditions when the index has at least one NOT NULL column. The NOT NULL column(s) are sufficient to identify matching rows uniquely, so omitting the nullable ones avoids the unnecessary NULL = NULL comparisons. The record equality operator (*=), appended to the join condition unconditionally, handles all columns including nullable ones and ensures changed rows are still detected correctly. When all columns of a unique index are nullable, no columns are skipped. Relying on *= alone in that case would be incorrect: *= treats NULL as equal to NULL, which can cause join ambiguity or silent data loss when rows contain all-null values. Falling back to including all columns preserves the original (slower but correct) behavior for that edge case. Added regression tests covering both the optimization path (index with at least one NOT NULL column) and the fallback path (all-nullable index). --- src/backend/commands/matview.c | 60 +++++++++++++++++++++++++-- src/test/regress/expected/matview.out | 29 +++++++++++++ src/test/regress/sql/matview.sql | 24 +++++++++++ 3 files changed, 110 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 81a55a33ef2..b0d7c59444a 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; @@ -715,6 +716,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); @@ -731,6 +733,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, oidvector *indclass; Datum indclassDatum; int i; + bool index_has_nonnull; + + foundUniqueIndex = true; /* Must get indclass the hard way. */ indclassDatum = SysCacheGetAttrNotNull(INDEXRELID, @@ -738,6 +743,29 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, Anum_pg_index_indclass); indclass = (oidvector *) DatumGetPointer(indclassDatum); + /* + * Pre-scan: check whether this index has at least one NOT NULL + * column. If it does, we can safely skip nullable columns in + * the join condition below (the NOT NULL column(s) uniquely + * identify rows, so omitting nullable columns does not introduce + * join ambiguity). If all columns are nullable we must include + * all of them to preserve correctness: skipping everything and + * relying on *= alone can cause join ambiguity or data loss + * because *= treats NULL as equal to NULL. + */ + index_has_nonnull = false; + for (i = 0; i < indnkeyatts; i++) + { + Form_pg_attribute attr_check = + TupleDescAttr(tupdesc, indexStruct->indkey.values[i] - 1); + + if (attr_check->attnotnull) + { + index_has_nonnull = true; + break; + } + } + /* Add quals for all columns from this index. */ for (i = 0; i < indnkeyatts; i++) { @@ -753,6 +781,24 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, const char *leftop; const char *rightop; + /* + * Skip nullable columns when this index has at least one NOT + * NULL column. Nullable columns in unique indexes allow + * multiple NULLs and NULL = NULL evaluates to NULL (false) in + * the join, making unchanged NULL-containing rows appear as + * changed on every refresh. The NOT NULL column(s) are + * sufficient to identify matching rows, so we can safely omit + * nullable ones without introducing join ambiguity. + * + * When all columns are nullable we must not skip any of them: + * with no per-column conditions only *= would remain, which + * can cause join ambiguity or data loss (see pre-scan comment + * above). We fall back to including all columns so the join + * behaves as in the original code. + */ + if (index_has_nonnull && !attr->attnotnull) + continue; + /* * Identify the equality operator associated with this index * column. First we need to look up the column's opclass. @@ -788,7 +834,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 +847,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, op, rightop, attrtype); - foundUniqueIndex = true; + addedAnyQuals = true; } } @@ -826,8 +872,16 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, errmsg("could not find suitable unique index on materialized view \"%s\"", RelationGetRelationName(matviewRel))); + /* + * The record equality operator (*=) is always included in the join + * predicate. It handles all columns correctly including nullable ones + * that were skipped above, since *= treats NULL as equal to NULL. + */ + 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..98db1e08185 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -418,6 +418,35 @@ CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; NOTICE: drop cascades to materialized view mvtest_mv2 +-- test that nullable indexed columns are skipped in the join condition when +-- the index has at least one NOT NULL column. The NOT NULL column alone +-- identifies matching rows, so the nullable column does not need to be in +-- the join and its NULL values do not cause unnecessary DELETE+INSERT churn. +CREATE TABLE mvtest_foo(a int NOT NULL, b int); +INSERT INTO mvtest_foo VALUES(1, NULL), (2, NULL), (3, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- a is NOT NULL, b is nullable +-- Add a second index on the nullable column alone to make it exercise the +-- all-nullable fallback path through the pre-scan. +CREATE UNIQUE INDEX ON mvtest_mv(b); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- test that CONCURRENTLY still works when all indexed columns are nullable +-- (falls back to including all columns, which is slower but correct). +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, 1); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- all-nullable index +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +SELECT * FROM mvtest_mv; + a | b +---+--- + | 1 +(1 row) + +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv -- make sure that types with unusual equality tests work CREATE TABLE mvtest_boxes (id serial primary key, b box); INSERT INTO mvtest_boxes (b) VALUES diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..624056a4dc5 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -153,6 +153,30 @@ CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; +-- test that nullable indexed columns are skipped in the join condition when +-- the index has at least one NOT NULL column. The NOT NULL column alone +-- identifies matching rows, so the nullable column does not need to be in +-- the join and its NULL values do not cause unnecessary DELETE+INSERT churn. +CREATE TABLE mvtest_foo(a int NOT NULL, b int); +INSERT INTO mvtest_foo VALUES(1, NULL), (2, NULL), (3, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- a is NOT NULL, b is nullable +-- Add a second index on the nullable column alone to make it exercise the +-- all-nullable fallback path through the pre-scan. +CREATE UNIQUE INDEX ON mvtest_mv(b); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +DROP TABLE mvtest_foo CASCADE; + +-- test that CONCURRENTLY still works when all indexed columns are nullable +-- (falls back to including all columns, which is slower but correct). +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, 1); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- all-nullable index +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +SELECT * FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that types with unusual equality tests work CREATE TABLE mvtest_boxes (id serial primary key, b box); INSERT INTO mvtest_boxes (b) VALUES -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-05-18 16:48  surya poondla <suryapoondla4@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-05-18 16:48 UTC (permalink / raw)
  To: cca5507 <cca5507@qq.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi All,

I rebased the patches to the latest code.

Regards,
Surya Poondla

>

Attachments: download all (2 files, .tar.gz)

0006-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-detect.patch (application/octet-stream, 11.4K) download From 19873f4a02509a6870623140ed5f63a8dd390b90 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:22:11 -0800 Subject: [PATCH v6] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY to detect duplicate rows REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly skipping duplicate detection for rows containing NULL values. The pre-check query used "WHERE newdata.* IS NOT NULL" which caused rows with any NULL column to bypass duplicate detection entirely. The fix rebuilds the pre-check using the same per-column equality operators used by the diff JOIN (one condition per unique-indexed column). These operators treat NULL as not equal to NULL, matching unique index semantics where NULLs are considered distinct. Only rows whose indexed columns are non-null and equal are flagged as duplicates --- the same rows that would cause join ambiguity. This correctly handles two cases that the old approach got wrong: - (test, NULL) x2 with index on a: a='test' is non-null and duplicated, so the duplicate is correctly detected and an error is raised. - (NULL, NULL) x2 with index on a: a=NULL, and unique indexes allow multiple NULLs (each is treated as distinct), so the refresh correctly succeeds and updates the view to two rows. Added regression tests covering both cases. --- src/backend/commands/matview.c | 122 ++++++++++++++++++-------- src/test/regress/expected/matview.out | 33 ++++++- src/test/regress/sql/matview.sql | 22 +++++ 3 files changed, 137 insertions(+), 40 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index f7d8007f796..b806ab46647 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -606,6 +606,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, ListCell *indexoidscan; int16 relnatts; Oid *opUsedForQual; + StringInfoData precheck_cond_buf; + bool precheck_has_cond; initStringInfo(&querybuf); matviewRel = table_open(matviewOid, NoLock); @@ -634,45 +636,6 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) 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. - * - * Note: here and below, we use "tablename.*::tablerowtype" as a hack to - * keep ".*" from being expanded into multiple columns in a SELECT list. - * Compare ruleutils.c's get_variable(). - */ - 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.* " - "AND newdata2.ctid OPERATOR(pg_catalog.<>) " - "newdata.ctid)", - tempname, tempname, tempname); - if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) - elog(ERROR, "SPI_exec failed: %s", querybuf.data); - if (SPI_processed > 0) - { - /* - * Note that this ereport() is returning data to the user. Generally, - * we would want to make sure that the user has been granted access to - * this data. However, REFRESH MAT VIEW is only able to be run by the - * owner of the mat view (or a superuser) and therefore there is no - * need to check for access to data in the mat view. - */ - ereport(ERROR, - (errcode(ERRCODE_CARDINALITY_VIOLATION), - errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns", - RelationGetRelationName(matviewRel)), - errdetail("Row: %s", - SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); - } - /* * Create the temporary "diff" table. * @@ -715,6 +678,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, tupdesc = matviewRel->rd_att; opUsedForQual = palloc0_array(Oid, relnatts); foundUniqueIndex = false; + initStringInfo(&precheck_cond_buf); + precheck_has_cond = false; indexoidlist = RelationGetIndexList(matviewRel); @@ -802,6 +767,30 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, rightop, attrtype); foundUniqueIndex = true; + + /* + * Also accumulate the same condition for the duplicate-row + * pre-check, comparing newdata2 against newdata (instead of + * newdata against mv). This lets us detect rows that are + * duplicates with respect to the unique index semantics --- + * i.e. rows whose indexed columns are non-null and equal --- + * without falsely flagging rows whose indexed columns are + * NULL (which unique indexes treat as distinct). + */ + if (precheck_has_cond) + appendStringInfoString(&precheck_cond_buf, " AND "); + + leftop = quote_qualified_identifier("newdata2", + NameStr(attr->attname)); + rightop = quote_qualified_identifier("newdata", + NameStr(attr->attname)); + + generate_operator_clause(&precheck_cond_buf, + leftop, attrtype, + op, + rightop, attrtype); + + precheck_has_cond = true; } } @@ -831,6 +820,61 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, "WHERE newdata.* IS NULL OR mv.* IS NULL " "ORDER BY tid"); + /* + * Before populating the diff table, check for duplicate rows in the + * new data set. We look for rows that are equal in all unique index + * columns (using the same operators as the join above, where + * NULL = NULL is false) AND are also *=-equal overall. Such rows + * would both match the same materialized view row in the join, + * producing an ambiguous diff. + * + * Using index column operators rather than *= alone is important: it + * correctly excludes rows whose indexed columns are NULL, because + * unique indexes treat NULLs as distinct so those rows do not cause + * join ambiguity. + * + * Note: here and below, we use "tablename.*::tablerowtype" as a hack + * to keep ".*" from being expanded into multiple columns in a SELECT + * list. Compare ruleutils.c's get_variable(). + * + * Even after we pass this test, a unique index on the materialized + * view may find a duplicate key problem. + */ + { + StringInfoData precheck_querybuf; + + initStringInfo(&precheck_querybuf); + appendStringInfo(&precheck_querybuf, + "SELECT newdata.*::%s FROM %s newdata " + "WHERE EXISTS " + "(SELECT 1 FROM %s newdata2 WHERE %s" + " AND newdata2.* OPERATOR(pg_catalog.*=) newdata.*" + " AND newdata2.ctid OPERATOR(pg_catalog.<>) newdata.ctid)", + tempname, tempname, tempname, precheck_cond_buf.data); + + if (SPI_execute(precheck_querybuf.data, false, 1) != SPI_OK_SELECT) + elog(ERROR, "SPI_exec failed: %s", precheck_querybuf.data); + + if (SPI_processed > 0) + { + /* + * Note that this ereport() is returning data to the user. + * Generally, we would want to make sure that the user has been + * granted access to this data. However, REFRESH MAT VIEW is + * only able to be run by the owner of the mat view (or a + * superuser) and therefore there is no need to check for access + * to data in the mat view. + */ + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + 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)))); + } + } + /* Populate the temporary "diff" table. */ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 0355720dfc6..1dcff26a2e4 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -396,8 +396,39 @@ 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 +-- test that duplicate rows containing NULLs in non-indexed columns are detected +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +ERROR: could not create unique index "mvtest_mv_a_idx" +DETAIL: Key (a)=(test) is duplicated. +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +ERROR: new data for materialized view "mvtest_mv" contains duplicate rows +DETAIL: Row: (test,) +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- test that rows with NULLs in the indexed column are not false positives: +-- unique indexes treat NULLs as distinct, so (NULL,NULL)x2 is a valid state +-- and CONCURRENTLY should succeed and update the view to reflect both rows +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT COUNT(*) FROM mvtest_mv; + count +------- + 2 +(1 row) + DROP TABLE mvtest_foo CASCADE; NOTICE: drop cascades to materialized view mvtest_mv -- make sure that all columns covered by unique indexes works diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..e65cd4fa94b 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -135,6 +135,28 @@ REFRESH MATERIALIZED VIEW mvtest_mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; DROP TABLE mvtest_foo CASCADE; +-- test that duplicate rows containing NULLs in non-indexed columns are detected +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + +-- test that rows with NULLs in the indexed column are not false positives: +-- unique indexes treat NULLs as distinct, so (NULL,NULL)x2 is a valid state +-- and CONCURRENTLY should succeed and update the view to reflect both rows +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT COUNT(*) FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; -- 2.39.5 (Apple Git-154)
0006-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-performan.patch (application/octet-stream, 10.0K) download From 662da36edbe9b190d78478c4fe128a93728f952f Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:23:17 -0800 Subject: [PATCH v6] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY performance with nullable indexed columns When a materialized view has a unique index on a nullable column, the join condition used to detect changes included NULL = NULL comparisons which evaluate to NULL (false), causing every row with a NULL indexed value to appear as changed on every CONCURRENTLY refresh even when the data was unchanged. The fix skips nullable columns from the per-column join conditions when the index has at least one NOT NULL column. The NOT NULL column(s) are sufficient to identify matching rows uniquely, so omitting the nullable ones avoids the unnecessary NULL = NULL comparisons. The record equality operator (*=), appended to the join condition unconditionally, handles all columns including nullable ones and ensures changed rows are still detected correctly. When all columns of a unique index are nullable, no columns are skipped. Relying on *= alone in that case would be incorrect: *= treats NULL as equal to NULL, which can cause join ambiguity or silent data loss when rows contain all-null values. Falling back to including all columns preserves the original (slower but correct) behavior for that edge case. Added regression tests covering both the optimization path (index with at least one NOT NULL column) and the fallback path (all-nullable index). --- src/backend/commands/matview.c | 60 +++++++++++++++++++++++++-- src/test/regress/expected/matview.out | 29 +++++++++++++ src/test/regress/sql/matview.sql | 24 +++++++++++ 3 files changed, 110 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index f7d8007f796..a47504992d6 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; @@ -715,6 +716,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); @@ -731,6 +733,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, oidvector *indclass; Datum indclassDatum; int i; + bool index_has_nonnull; + + foundUniqueIndex = true; /* Must get indclass the hard way. */ indclassDatum = SysCacheGetAttrNotNull(INDEXRELID, @@ -738,6 +743,29 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, Anum_pg_index_indclass); indclass = (oidvector *) DatumGetPointer(indclassDatum); + /* + * Pre-scan: check whether this index has at least one NOT NULL + * column. If it does, we can safely skip nullable columns in + * the join condition below (the NOT NULL column(s) uniquely + * identify rows, so omitting nullable columns does not introduce + * join ambiguity). If all columns are nullable we must include + * all of them to preserve correctness: skipping everything and + * relying on *= alone can cause join ambiguity or data loss + * because *= treats NULL as equal to NULL. + */ + index_has_nonnull = false; + for (i = 0; i < indnkeyatts; i++) + { + Form_pg_attribute attr_check = + TupleDescAttr(tupdesc, indexStruct->indkey.values[i] - 1); + + if (attr_check->attnotnull) + { + index_has_nonnull = true; + break; + } + } + /* Add quals for all columns from this index. */ for (i = 0; i < indnkeyatts; i++) { @@ -753,6 +781,24 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, const char *leftop; const char *rightop; + /* + * Skip nullable columns when this index has at least one NOT + * NULL column. Nullable columns in unique indexes allow + * multiple NULLs and NULL = NULL evaluates to NULL (false) in + * the join, making unchanged NULL-containing rows appear as + * changed on every refresh. The NOT NULL column(s) are + * sufficient to identify matching rows, so we can safely omit + * nullable ones without introducing join ambiguity. + * + * When all columns are nullable we must not skip any of them: + * with no per-column conditions only *= would remain, which + * can cause join ambiguity or data loss (see pre-scan comment + * above). We fall back to including all columns so the join + * behaves as in the original code. + */ + if (index_has_nonnull && !attr->attnotnull) + continue; + /* * Identify the equality operator associated with this index * column. First we need to look up the column's opclass. @@ -788,7 +834,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 +847,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, op, rightop, attrtype); - foundUniqueIndex = true; + addedAnyQuals = true; } } @@ -826,8 +872,16 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, errmsg("could not find suitable unique index on materialized view \"%s\"", RelationGetRelationName(matviewRel))); + /* + * The record equality operator (*=) is always included in the join + * predicate. It handles all columns correctly including nullable ones + * that were skipped above, since *= treats NULL as equal to NULL. + */ + 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..98db1e08185 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -418,6 +418,35 @@ CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; NOTICE: drop cascades to materialized view mvtest_mv2 +-- test that nullable indexed columns are skipped in the join condition when +-- the index has at least one NOT NULL column. The NOT NULL column alone +-- identifies matching rows, so the nullable column does not need to be in +-- the join and its NULL values do not cause unnecessary DELETE+INSERT churn. +CREATE TABLE mvtest_foo(a int NOT NULL, b int); +INSERT INTO mvtest_foo VALUES(1, NULL), (2, NULL), (3, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- a is NOT NULL, b is nullable +-- Add a second index on the nullable column alone to make it exercise the +-- all-nullable fallback path through the pre-scan. +CREATE UNIQUE INDEX ON mvtest_mv(b); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- test that CONCURRENTLY still works when all indexed columns are nullable +-- (falls back to including all columns, which is slower but correct). +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, 1); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- all-nullable index +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +SELECT * FROM mvtest_mv; + a | b +---+--- + | 1 +(1 row) + +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv -- make sure that types with unusual equality tests work CREATE TABLE mvtest_boxes (id serial primary key, b box); INSERT INTO mvtest_boxes (b) VALUES diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..624056a4dc5 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -153,6 +153,30 @@ CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; +-- test that nullable indexed columns are skipped in the join condition when +-- the index has at least one NOT NULL column. The NOT NULL column alone +-- identifies matching rows, so the nullable column does not need to be in +-- the join and its NULL values do not cause unnecessary DELETE+INSERT churn. +CREATE TABLE mvtest_foo(a int NOT NULL, b int); +INSERT INTO mvtest_foo VALUES(1, NULL), (2, NULL), (3, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- a is NOT NULL, b is nullable +-- Add a second index on the nullable column alone to make it exercise the +-- all-nullable fallback path through the pre-scan. +CREATE UNIQUE INDEX ON mvtest_mv(b); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +DROP TABLE mvtest_foo CASCADE; + +-- test that CONCURRENTLY still works when all indexed columns are nullable +-- (falls back to including all columns, which is slower but correct). +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, 1); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- all-nullable index +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +SELECT * FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that types with unusual equality tests work CREATE TABLE mvtest_boxes (id serial primary key, b box); INSERT INTO mvtest_boxes (b) VALUES -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-07-16 09:19  Rafia Sabih <rafia.pghackers@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: Rafia Sabih @ 2026-07-16 09:19 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

The performance patch required rebase, so attaching both the patches for
consistency.

On Mon, 18 May 2026 at 22:18, surya poondla <suryapoondla4@gmail.com> wrote:

> Hi All,
>
> I rebased the patches to the latest code.
>
> Regards,
> Surya Poondla
>
>>

-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

Attachments: download all (2 files, .tar.gz)

v7-0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-to-det.patch (application/octet-stream, 11.4K) download From f7edfdfbf4740776439cdb1f8ff22919a17513a9 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 2 Mar 2026 11:22:11 -0800 Subject: [PATCH v7 1/2] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY to detect duplicate rows REFRESH MATERIALIZED VIEW CONCURRENTLY was incorrectly skipping duplicate detection for rows containing NULL values. The pre-check query used "WHERE newdata.* IS NOT NULL" which caused rows with any NULL column to bypass duplicate detection entirely. The fix rebuilds the pre-check using the same per-column equality operators used by the diff JOIN (one condition per unique-indexed column). These operators treat NULL as not equal to NULL, matching unique index semantics where NULLs are considered distinct. Only rows whose indexed columns are non-null and equal are flagged as duplicates --- the same rows that would cause join ambiguity. This correctly handles two cases that the old approach got wrong: - (test, NULL) x2 with index on a: a='test' is non-null and duplicated, so the duplicate is correctly detected and an error is raised. - (NULL, NULL) x2 with index on a: a=NULL, and unique indexes allow multiple NULLs (each is treated as distinct), so the refresh correctly succeeds and updates the view to two rows. Added regression tests covering both cases. --- src/backend/commands/matview.c | 122 ++++++++++++++++++-------- src/test/regress/expected/matview.out | 33 ++++++- src/test/regress/sql/matview.sql | 22 +++++ 3 files changed, 137 insertions(+), 40 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index f7d8007f796..b806ab46647 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -606,6 +606,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, ListCell *indexoidscan; int16 relnatts; Oid *opUsedForQual; + StringInfoData precheck_cond_buf; + bool precheck_has_cond; initStringInfo(&querybuf); matviewRel = table_open(matviewOid, NoLock); @@ -634,45 +636,6 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) 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. - * - * Note: here and below, we use "tablename.*::tablerowtype" as a hack to - * keep ".*" from being expanded into multiple columns in a SELECT list. - * Compare ruleutils.c's get_variable(). - */ - 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.* " - "AND newdata2.ctid OPERATOR(pg_catalog.<>) " - "newdata.ctid)", - tempname, tempname, tempname); - if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) - elog(ERROR, "SPI_exec failed: %s", querybuf.data); - if (SPI_processed > 0) - { - /* - * Note that this ereport() is returning data to the user. Generally, - * we would want to make sure that the user has been granted access to - * this data. However, REFRESH MAT VIEW is only able to be run by the - * owner of the mat view (or a superuser) and therefore there is no - * need to check for access to data in the mat view. - */ - ereport(ERROR, - (errcode(ERRCODE_CARDINALITY_VIOLATION), - errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns", - RelationGetRelationName(matviewRel)), - errdetail("Row: %s", - SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); - } - /* * Create the temporary "diff" table. * @@ -715,6 +678,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, tupdesc = matviewRel->rd_att; opUsedForQual = palloc0_array(Oid, relnatts); foundUniqueIndex = false; + initStringInfo(&precheck_cond_buf); + precheck_has_cond = false; indexoidlist = RelationGetIndexList(matviewRel); @@ -802,6 +767,30 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, rightop, attrtype); foundUniqueIndex = true; + + /* + * Also accumulate the same condition for the duplicate-row + * pre-check, comparing newdata2 against newdata (instead of + * newdata against mv). This lets us detect rows that are + * duplicates with respect to the unique index semantics --- + * i.e. rows whose indexed columns are non-null and equal --- + * without falsely flagging rows whose indexed columns are + * NULL (which unique indexes treat as distinct). + */ + if (precheck_has_cond) + appendStringInfoString(&precheck_cond_buf, " AND "); + + leftop = quote_qualified_identifier("newdata2", + NameStr(attr->attname)); + rightop = quote_qualified_identifier("newdata", + NameStr(attr->attname)); + + generate_operator_clause(&precheck_cond_buf, + leftop, attrtype, + op, + rightop, attrtype); + + precheck_has_cond = true; } } @@ -831,6 +820,61 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, "WHERE newdata.* IS NULL OR mv.* IS NULL " "ORDER BY tid"); + /* + * Before populating the diff table, check for duplicate rows in the + * new data set. We look for rows that are equal in all unique index + * columns (using the same operators as the join above, where + * NULL = NULL is false) AND are also *=-equal overall. Such rows + * would both match the same materialized view row in the join, + * producing an ambiguous diff. + * + * Using index column operators rather than *= alone is important: it + * correctly excludes rows whose indexed columns are NULL, because + * unique indexes treat NULLs as distinct so those rows do not cause + * join ambiguity. + * + * Note: here and below, we use "tablename.*::tablerowtype" as a hack + * to keep ".*" from being expanded into multiple columns in a SELECT + * list. Compare ruleutils.c's get_variable(). + * + * Even after we pass this test, a unique index on the materialized + * view may find a duplicate key problem. + */ + { + StringInfoData precheck_querybuf; + + initStringInfo(&precheck_querybuf); + appendStringInfo(&precheck_querybuf, + "SELECT newdata.*::%s FROM %s newdata " + "WHERE EXISTS " + "(SELECT 1 FROM %s newdata2 WHERE %s" + " AND newdata2.* OPERATOR(pg_catalog.*=) newdata.*" + " AND newdata2.ctid OPERATOR(pg_catalog.<>) newdata.ctid)", + tempname, tempname, tempname, precheck_cond_buf.data); + + if (SPI_execute(precheck_querybuf.data, false, 1) != SPI_OK_SELECT) + elog(ERROR, "SPI_exec failed: %s", precheck_querybuf.data); + + if (SPI_processed > 0) + { + /* + * Note that this ereport() is returning data to the user. + * Generally, we would want to make sure that the user has been + * granted access to this data. However, REFRESH MAT VIEW is + * only able to be run by the owner of the mat view (or a + * superuser) and therefore there is no need to check for access + * to data in the mat view. + */ + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + 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)))); + } + } + /* Populate the temporary "diff" table. */ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 0355720dfc6..1dcff26a2e4 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -396,8 +396,39 @@ 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 +-- test that duplicate rows containing NULLs in non-indexed columns are detected +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +ERROR: could not create unique index "mvtest_mv_a_idx" +DETAIL: Key (a)=(test) is duplicated. +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +ERROR: new data for materialized view "mvtest_mv" contains duplicate rows +DETAIL: Row: (test,) +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- test that rows with NULLs in the indexed column are not false positives: +-- unique indexes treat NULLs as distinct, so (NULL,NULL)x2 is a valid state +-- and CONCURRENTLY should succeed and update the view to reflect both rows +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT COUNT(*) FROM mvtest_mv; + count +------- + 2 +(1 row) + DROP TABLE mvtest_foo CASCADE; NOTICE: drop cascades to materialized view mvtest_mv -- make sure that all columns covered by unique indexes works diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..e65cd4fa94b 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -135,6 +135,28 @@ REFRESH MATERIALIZED VIEW mvtest_mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; DROP TABLE mvtest_foo CASCADE; +-- test that duplicate rows containing NULLs in non-indexed columns are detected +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + +-- test that rows with NULLs in the indexed column are not false positives: +-- unique indexes treat NULLs as distinct, so (NULL,NULL)x2 is a valid state +-- and CONCURRENTLY should succeed and update the view to reflect both rows +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES(NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT COUNT(*) FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; -- 2.39.5 (Apple Git-154)
v7-0002-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-perfor.patch (application/octet-stream, 13.3K) download From 543cb6eb2583bdff031ee694fb86cada6c37db62 Mon Sep 17 00:00:00 2001 From: Rafia Sabih <rafia.sabih@cybertec.at> Date: Thu, 16 Jul 2026 14:47:11 +0530 Subject: [PATCH v7 2/2] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY performance with nullable indexed columns When a materialized view has a unique index on a nullable column, the join condition used to detect changes included NULL = NULL comparisons which evaluate to NULL (false), causing every row with a NULL indexed value to appear as changed on every CONCURRENTLY refresh even when the data was unchanged. The fix skips nullable columns from the per-column join conditions when the index has at least one NOT NULL column. The NOT NULL column(s) are sufficient to identify matching rows uniquely, so omitting the nullable ones avoids the unnecessary NULL = NULL comparisons. The record equality operator (*=), appended to the join condition unconditionally, handles all columns including nullable ones and ensures changed rows are still detected correctly. When all columns of a unique index are nullable, no columns are skipped. Relying on *= alone in that case would be incorrect: *= treats NULL as equal to NULL, which can cause join ambiguity or silent data loss when rows contain all-null values. Falling back to including all columns preserves the original (slower but correct) behavior for that edge case. Added regression tests covering both the optimization path (index with at least one NOT NULL column) and the fallback path (all-nullable index). --- src/backend/commands/matview.c | 101 ++++++++++++++++++++------ src/test/regress/expected/matview.out | 29 ++++++++ src/test/regress/sql/matview.sql | 24 ++++++ 3 files changed, 130 insertions(+), 24 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index b806ab46647..4e984845cd7 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; @@ -680,6 +681,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, foundUniqueIndex = false; initStringInfo(&precheck_cond_buf); precheck_has_cond = false; + addedAnyQuals = false; indexoidlist = RelationGetIndexList(matviewRel); @@ -696,6 +698,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, oidvector *indclass; Datum indclassDatum; int i; + bool index_has_nonnull; + + foundUniqueIndex = true; /* Must get indclass the hard way. */ indclassDatum = SysCacheGetAttrNotNull(INDEXRELID, @@ -703,6 +708,29 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, Anum_pg_index_indclass); indclass = (oidvector *) DatumGetPointer(indclassDatum); + /* + * Pre-scan: check whether this index has at least one NOT NULL + * column. If it does, we can safely skip nullable columns in the + * join condition below (the NOT NULL column(s) uniquely identify + * rows, so omitting nullable columns does not introduce join + * ambiguity). If all columns are nullable we must include all of + * them to preserve correctness: skipping everything and relying + * on *= alone can cause join ambiguity or data loss because *= + * treats NULL as equal to NULL. + */ + index_has_nonnull = false; + for (i = 0; i < indnkeyatts; i++) + { + Form_pg_attribute attr_check = + TupleDescAttr(tupdesc, indexStruct->indkey.values[i] - 1); + + if (attr_check->attnotnull) + { + index_has_nonnull = true; + break; + } + } + /* Add quals for all columns from this index. */ for (i = 0; i < indnkeyatts; i++) { @@ -718,6 +746,24 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, const char *leftop; const char *rightop; + /* + * Skip nullable columns when this index has at least one NOT + * NULL column. Nullable columns in unique indexes allow + * multiple NULLs and NULL = NULL evaluates to NULL (false) in + * the join, making unchanged NULL-containing rows appear as + * changed on every refresh. The NOT NULL column(s) are + * sufficient to identify matching rows, so we can safely omit + * nullable ones without introducing join ambiguity. + * + * When all columns are nullable we must not skip any of them: + * with no per-column conditions only *= would remain, which + * can cause join ambiguity or data loss (see pre-scan comment + * above). We fall back to including all columns so the join + * behaves as in the original code. + */ + if (index_has_nonnull && !attr->attnotnull) + continue; + /* * Identify the equality operator associated with this index * column. First we need to look up the column's opclass. @@ -753,7 +799,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", @@ -766,7 +812,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, op, rightop, attrtype); - foundUniqueIndex = true; + addedAnyQuals = true; /* * Also accumulate the same condition for the duplicate-row @@ -781,9 +827,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, appendStringInfoString(&precheck_cond_buf, " AND "); leftop = quote_qualified_identifier("newdata2", - NameStr(attr->attname)); + NameStr(attr->attname)); rightop = quote_qualified_identifier("newdata", - NameStr(attr->attname)); + NameStr(attr->attname)); generate_operator_clause(&precheck_cond_buf, leftop, attrtype, @@ -815,30 +861,37 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, errmsg("could not find suitable unique index on materialized view \"%s\"", RelationGetRelationName(matviewRel))); + /* + * The record equality operator (*=) is always included in the join + * predicate. It handles all columns correctly including nullable ones + * that were skipped above, since *= treats NULL as equal to NULL. + */ + 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"); /* - * Before populating the diff table, check for duplicate rows in the - * new data set. We look for rows that are equal in all unique index - * columns (using the same operators as the join above, where - * NULL = NULL is false) AND are also *=-equal overall. Such rows - * would both match the same materialized view row in the join, - * producing an ambiguous diff. + * Before populating the diff table, check for duplicate rows in the new + * data set. We look for rows that are equal in all unique index columns + * (using the same operators as the join above, where NULL = NULL is + * false) AND are also *=-equal overall. Such rows would both match the + * same materialized view row in the join, producing an ambiguous diff. * * Using index column operators rather than *= alone is important: it - * correctly excludes rows whose indexed columns are NULL, because - * unique indexes treat NULLs as distinct so those rows do not cause - * join ambiguity. + * correctly excludes rows whose indexed columns are NULL, because unique + * indexes treat NULLs as distinct so those rows do not cause join + * ambiguity. * - * Note: here and below, we use "tablename.*::tablerowtype" as a hack - * to keep ".*" from being expanded into multiple columns in a SELECT - * list. Compare ruleutils.c's get_variable(). + * Note: here and below, we use "tablename.*::tablerowtype" as a hack to + * keep ".*" from being expanded into multiple columns in a SELECT list. + * Compare ruleutils.c's get_variable(). * - * Even after we pass this test, a unique index on the materialized - * view may find a duplicate key problem. + * Even after we pass this test, a unique index on the materialized view + * may find a duplicate key problem. */ { StringInfoData precheck_querybuf; @@ -860,10 +913,10 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, /* * Note that this ereport() is returning data to the user. * Generally, we would want to make sure that the user has been - * granted access to this data. However, REFRESH MAT VIEW is - * only able to be run by the owner of the mat view (or a - * superuser) and therefore there is no need to check for access - * to data in the mat view. + * granted access to this data. However, REFRESH MAT VIEW is only + * able to be run by the owner of the mat view (or a superuser) + * and therefore there is no need to check for access to data in + * the mat view. */ ereport(ERROR, (errcode(ERRCODE_CARDINALITY_VIOLATION), @@ -871,7 +924,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, RelationGetRelationName(matviewRel)), errdetail("Row: %s", SPI_getvalue(SPI_tuptable->vals[0], - SPI_tuptable->tupdesc, 1)))); + SPI_tuptable->tupdesc, 1)))); } } diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 1dcff26a2e4..4fcc63d8249 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -449,6 +449,35 @@ CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; NOTICE: drop cascades to materialized view mvtest_mv2 +-- test that nullable indexed columns are skipped in the join condition when +-- the index has at least one NOT NULL column. The NOT NULL column alone +-- identifies matching rows, so the nullable column does not need to be in +-- the join and its NULL values do not cause unnecessary DELETE+INSERT churn. +CREATE TABLE mvtest_foo(a int NOT NULL, b int); +INSERT INTO mvtest_foo VALUES(1, NULL), (2, NULL), (3, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- a is NOT NULL, b is nullable +-- Add a second index on the nullable column alone to make it exercise the +-- all-nullable fallback path through the pre-scan. +CREATE UNIQUE INDEX ON mvtest_mv(b); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- test that CONCURRENTLY still works when all indexed columns are nullable +-- (falls back to including all columns, which is slower but correct). +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, 1); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- all-nullable index +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +SELECT * FROM mvtest_mv; + a | b +---+--- + | 1 +(1 row) + +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv -- make sure that types with unusual equality tests work CREATE TABLE mvtest_boxes (id serial primary key, b box); INSERT INTO mvtest_boxes (b) VALUES diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index e65cd4fa94b..5d4db248457 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -175,6 +175,30 @@ CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; +-- test that nullable indexed columns are skipped in the join condition when +-- the index has at least one NOT NULL column. The NOT NULL column alone +-- identifies matching rows, so the nullable column does not need to be in +-- the join and its NULL values do not cause unnecessary DELETE+INSERT churn. +CREATE TABLE mvtest_foo(a int NOT NULL, b int); +INSERT INTO mvtest_foo VALUES(1, NULL), (2, NULL), (3, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- a is NOT NULL, b is nullable +-- Add a second index on the nullable column alone to make it exercise the +-- all-nullable fallback path through the pre-scan. +CREATE UNIQUE INDEX ON mvtest_mv(b); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +DROP TABLE mvtest_foo CASCADE; + +-- test that CONCURRENTLY still works when all indexed columns are nullable +-- (falls back to including all columns, which is slower but correct). +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES(NULL, 1); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); -- all-nullable index +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; -- must succeed +SELECT * FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that types with unusual equality tests work CREATE TABLE mvtest_boxes (id serial primary key, b box); INSERT INTO mvtest_boxes (b) VALUES -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-07-21 18:55  surya poondla <suryapoondla4@gmail.com>
  parent: Rafia Sabih <rafia.pghackers@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: surya poondla @ 2026-07-21 18:55 UTC (permalink / raw)
  To: Rafia Sabih <rafia.pghackers@gmail.com>; +Cc: cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi Rafia,

Thanks for rebasing both patches.

I re-tested the v7 patches on the current master, all 245 regression tests
passed, and "make check TESTS=matview" runs good.

For issue 1 (v7-0001) the behaviour is correct:
1) A duplicate row containing NULLs is now detected and reported:
REFRESH MATERIALIZED VIEW CONCURRENTLY m;
ERROR:  new data for materialized view "m" contains duplicate rows

2) The case where the indexed column itself is NULL still succeeds, since a
unique index treats multiple NULLs as distinct:
CREATE TABLE t(a int, b int);
INSERT INTO t VALUES (NULL, NULL);
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
CREATE UNIQUE INDEX ON m(a);
INSERT INTO t VALUES (NULL, NULL);
REFRESH MATERIALIZED VIEW CONCURRENTLY m;  -- succeeds, m has 2 rows

So I believe v7-0001 is good to go.

While re-testing issue 2 (v7-0002), however, I found that it does not
actually fix the reported performance regression.
Re-running the original example on current master with v7-0002 applied:
CREATE MATERIALIZED VIEW s AS
SELECT generate_series AS x, NULL AS y FROM generate_series(1, 1000000);
CREATE UNIQUE INDEX ON s(x);
REFRESH MATERIALIZED VIEW CONCURRENTLY s;   -- 1512 ms
CREATE UNIQUE INDEX ON s(y);
REFRESH MATERIALIZED VIEW CONCURRENTLY s;   -- 4650 ms

The second refresh is still ~3x slower, i.e. the regression remains.

The reason is that the optimization skips a nullable column from the join
only when its index has at least one NOT NULL column:

if (index_has_nonnull && !attr->attnotnull)
          continue;

and index_has_nonnull is derived from the materialized view column's
attnotnull.
But a materialized view column is never marked NOT NULL:
CREATE MATERIALIZED VIEW does not propagate NOT NULL from the base
relation, and ALTER MATERIALIZED VIEW ... ALTER COLUMN ... SET NOT NULL
is explicitly not supported. So index_has_nonnull is always false, the
column is never skipped, and the patch is effectively a no-op for any
materialized view.

This also explains the discrepancy with my earlier numbers. The first
version of this patch skipped nullable columns unconditionally
(if (!attr->attnotnull) continue;), since all matview columns are nullable,
it skipped every column and reduced the join to
"newdata.* *= mv.*" alone.
That was fast, but it is exactly the behaviour ChangAo showed to be
incorrect for the (NULL, NULL) x 2 case,
because *= treats NULL as equal to NULL.
Adding the index_has_nonnull guard restored correctness but removed the
performance benefit entirely.

In other words, gating on the NOT NULL flag cannot work for materialized
views, the guard the patch does nothing, and without
it the join is incorrect. A working fix for issue 2 would have to rely on
something other than the column's NOT NULL flag, for example
detecting at refresh time whether the indexed column actually contains
NULLs, or a diff strategy that keeps NULLs distinct without forcing a
per-row DELETE+INSERT.

Given the above, I suggest we proceed with the issue 1 patch (v7-0001) on
its own, and treat issue 2 separately, since it needs a different
approach. I will keep exploring options for issue 2 and would welcome any
ideas.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-07-27 20:28  Robert Haas <robertmhaas@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: Robert Haas @ 2026-07-27 20:28 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

On Tue, Jul 21, 2026 at 2:55 PM surya poondla <suryapoondla4@gmail.com> wrote:
> For issue 1 (v7-0001) the behaviour is correct:
> 1) A duplicate row containing NULLs is now detected and reported:
> REFRESH MATERIALIZED VIEW CONCURRENTLY m;
> ERROR:  new data for materialized view "m" contains duplicate rows

I agree that there's a bug here, but I'm not sure that we've got a
sufficiently clear-eyed view of where the bug is. The proposed fix
doesn't touch the function header comment, which lays out the
algorithm that Kevin intended to use and the reasoning behind it. I
think there's a bug in that algorithm, which means that the function
should probably be updating it. But before we get to that point, what
exactly is the bug?

 * This is called after a new version of the data has been created in a
 * temporary table.  It performs a full outer join against the old version of
 * the data, producing "diff" results.  This join cannot work if there are any
 * duplicated rows in either the old or new versions, in the sense that every
 * column would compare as equal between the two rows.  It does work correctly
 * in the face of rows which have at least one NULL value, with all non-NULL
 * columns equal.

I think the bug is in this last sentence. If that statement were true,
then the delta table would be constructed correctly in the test case
from the original email, because in the example, all the rows involved
contain at least one NULL value.  But in fact, the delta table ends up
being wrong in that test case. As far as it can see, every row in the
new query output is present in the materialized view and every row in
the materialized view is present in the new query output, and so it
misses the fact that there is a new row to insert.

But ... how exactly is that sentence wrong? What the delta query
actually does is look for rows that are image-identical and SQL-equal
on all key columns. Now, that's really strange. If the rows are
image-identical, then you might think that they would *also* be
SQL-equal on all key columns, but that is not the case, because in SQL
equality, NULL = NULL evaluates to NULL, not true (or false). So what
that means is that the query considers two rows to be identical if
they actually are identical and if, in addition, every column covered
by a usable unique index is non-NULL. Flipping that statement around,
the query considers rows that actually are identical to be
non-identical if any column covered by a usable unique index contains
a NULL, which is pretty crazy IMHO.

But on the upside, the consequence of that craziness is that a unique
index that ignores nulls (which is the default) is still good enough
to make this code work. What will happen is that we'll see those
null-containing rows in the current version of the MV as all being
different from the null-containing rows in the new data we want to put
into the MV, so we'll delete them all and reinsert them on every
refresh. That's going to be terrible for performance if there are many
such rows, but that might not be the case, in which case it will work
fine.

However, all of the above logic is only correct when the null occurs
*in a column covered by a usable unique index*. If the null is
elsewhere in the tuple, as in case #1 from the original email, then it
doesn't force the delta query to treat those rows as unequal, and we
don't get pushed into the delete-and-insert path.

So the last sentence I quote above is sort of doubly wrong. First, the
behavior is only correct in the face of duplicated rows which have at
least one NULL value *in a column covered by a unique index*. Second,
"correct" only means that we manage to get the right answer, not that
the code works in an understandable way or is efficient. :-(

It might be a good idea to think about redesigning this whole
mechanism to work in a less-surprising way. But I think in the
back-branches, that's out of the question. What we can do, though, is
tighten the null precheck so it looks specifically for nulls in
columns covered by unique indexes and errors out if none are found.
With that change, the "new data for materialized view \"%s\" contains
duplicate rows without any null columns" error would trigger in case
#1 from the original email, which I think is the expected behavior.

-- 
Robert Haas
EDB: http://www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-07-31 07:44  Yugo Nagata <nagata@sraoss.co.jp>
  parent: Robert Haas <robertmhaas@gmail.com>
  0 siblings, 1 reply; 26+ messages in thread

From: Yugo Nagata @ 2026-07-31 07:44 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: surya poondla <suryapoondla4@gmail.com>; Rafia Sabih <rafia.pghackers@gmail.com>; cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

On Mon, 27 Jul 2026 16:28:06 -0400
Robert Haas <robertmhaas@gmail.com> wrote:

> On Tue, Jul 21, 2026 at 2:55 PM surya poondla <suryapoondla4@gmail.com> wrote:
> > For issue 1 (v7-0001) the behaviour is correct:
> > 1) A duplicate row containing NULLs is now detected and reported:
> > REFRESH MATERIALIZED VIEW CONCURRENTLY m;
> > ERROR:  new data for materialized view "m" contains duplicate rows
> 
> I agree that there's a bug here, but I'm not sure that we've got a
> sufficiently clear-eyed view of where the bug is. The proposed fix
> doesn't touch the function header comment, which lays out the
> algorithm that Kevin intended to use and the reasoning behind it. I
> think there's a bug in that algorithm, which means that the function
> should probably be updating it. But before we get to that point, what
> exactly is the bug?
> 
>  * This is called after a new version of the data has been created in a
>  * temporary table.  It performs a full outer join against the old version of
>  * the data, producing "diff" results.  This join cannot work if there are any
>  * duplicated rows in either the old or new versions, in the sense that every
>  * column would compare as equal between the two rows.  It does work correctly
>  * in the face of rows which have at least one NULL value, with all non-NULL
>  * columns equal.
> 
> I think the bug is in this last sentence. If that statement were true,
> then the delta table would be constructed correctly in the test case
> from the original email, because in the example, all the rows involved
> contain at least one NULL value.  But in fact, the delta table ends up
> being wrong in that test case. As far as it can see, every row in the
> new query output is present in the materialized view and every row in
> the materialized view is present in the new query output, and so it
> misses the fact that there is a new row to insert.

I also agree that the current behavior is a bug because it can leave the
materialized view inconsistent with its base tables.

As Robert suggested, the function header comment should also be updated to
clarify that the algorithm works correctly in the presence of duplicated rows
with NULL values only whenthe NULL appears in a column covered by a usable
unique index.

I also think it would be helpful if the comment explained why this case works,
rather than simply stating that it does.


Additionally, I have a few comments on the patch.

(1)
The commit message says:

 This correctly handles two cases that the old approach got wrong:
   - (test, NULL) x2 with index on a: a='test' is non-null and
     duplicated, so the duplicate is correctly detected and an error
     is raised.
   - (NULL, NULL) x2 with index on a: a=NULL, and unique indexes
     allow multiple NULLs (each is treated as distinct), so the
     refresh correctly succeeds and updates the view to two rows.

However, the seccond case is not fixed by this commit.
The current code already handles this case correctly.

(2)

+	 *
+	 * Using index column operators rather than *= alone is important: it
+	 * correctly excludes rows whose indexed columns are NULL, because
+	 * unique indexes treat NULLs as distinct so those rows do not cause
+	 * join ambiguity.

I am not sure whether using the equality operators here is necessary. Since
the purpose seems to be excluding rows with NULLs in unique-index columns,
explicit NULL checks might make the intention clearer. Is there a reason to
prefer the equality operators here?

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>






^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-11-14 21:33  Joao Rito <joaovictor.rito@gmail.com>
  parent: Yugo Nagata <nagata@sraoss.co.jp>
  0 siblings, 1 reply; 26+ messages in thread

From: Joao Rito @ 2026-11-14 21:33 UTC (permalink / raw)
  To: Rafia Sabih <rafia.pghackers@gmail.com>; surya poondla <suryapoondla4@gmail.com>; +Cc: Yugo Nagata <nagata@sraoss.co.jp>; Robert Haas <robertmhaas@gmail.com>; cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-11-17 05:36  surya poondla <suryapoondla4@gmail.com>
  parent: Joao Rito <joaovictor.rito@gmail.com>
  0 siblings, 2 replies; 26+ messages in thread

From: surya poondla @ 2026-11-17 05:36 UTC (permalink / raw)
  To: Joao Rito <joaovictor.rito@gmail.com>; Yugo Nagata <nagata@sraoss.co.jp>; Robert Haas <robertmhaas@gmail.com>; +Cc: Rafia Sabih <rafia.pghackers@gmail.com>; cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>; pgsql-bugs@lists.postgresql.org

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

Attachments:

v8-0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-with-d.patch (application/octet-stream, 15.8K) download From 0cac154aca4dca2c5128cc37b3dc7b17e7b363f1 Mon Sep 17 00:00:00 2001 From: spoondla <s_poondla@apple.com> Date: Mon, 16 Nov 2026 14:43:44 -0800 Subject: [PATCH v8] Fix REFRESH MATERIALIZED VIEW CONCURRENTLY with duplicate rows containing NULLs refresh_by_match_merge() computes the difference between the old and new contents of the materialized view with a full outer join. The join matches rows that are identical (*=) and are also equal, using the index's equality operator, in every column covered by a usable unique index. The result is only correct if every new row matches at most one old row. Duplicate new rows break that if they can match at all, which is the case when none of their unique-key columns is NULL. However, the pre-check for such duplicates skipped every row that had a NULL in any column. Duplicate new rows whose NULLs were all outside the unique-key columns therefore passed the check and both matched the same old row, so the refresh silently left one of them out. For example: CREATE TABLE t (a text, b text); INSERT INTO t VALUES ('test', NULL); CREATE MATERIALIZED VIEW m AS SELECT * FROM t; CREATE UNIQUE INDEX ON m (a); INSERT INTO t VALUES ('test', NULL); REFRESH MATERIALIZED VIEW CONCURRENTLY m; -- m still has one row To fix, make the pre-check skip only rows that have a NULL in a unique-key column, since those are the rows the join can never match. Duplicates of that kind are still allowed, as before: all of them are inserted, and the unique index decides whether that is acceptable. The check tests the unique-key columns for NULL explicitly. It also compares them with the index's equality operators, which doesn't change the result but lets the planner use a hash join, since "*=" is not hashable. Also rewrite the function's header comment. It claimed that the join works for any row with at least one NULL value, which is what led to the bug. The new comment explains why the join gives correct results. Reported-by: Giuliano Gagliardi <gogi@gogi.tv> Author: Surya Poondla <suryapoondla4@gmail.com> Author: Rafia Sabih <rafia.pghackers@gmail.com> Reviewed-by: ChangAo Chen <cca5507@qq.com> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Yugo Nagata <nagata@sraoss.co.jp> Reviewed-by: Joao Rito <joaovictor.rito@gmail.com> Discussion: https://postgr.es/m/40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv --- src/backend/commands/matview.c | 153 ++++++++++++++++++-------- src/test/regress/expected/matview.out | 52 +++++++++ src/test/regress/sql/matview.sql | 31 ++++++ 3 files changed, 188 insertions(+), 48 deletions(-) diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 40748958eaf..b157593a89b 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -564,14 +564,29 @@ transientrel_destroy(DestReceiver *self) * * This is called after a new version of the data has been created in a * temporary table. It performs a full outer join against the old version of - * the data, producing "diff" results. This join cannot work if there are any - * duplicated rows in either the old or new versions, in the sense that every - * column would compare as equal between the two rows. It does work correctly - * in the face of rows which have at least one NULL value, with all non-NULL - * columns equal. The behavior of NULLs on equality tests and on UNIQUE - * indexes turns out to be quite convenient here; the tests we need to make - * are consistent with default behavior. If there is at least one UNIQUE - * index on the materialized view, we have exactly the guarantee we need. + * the data, producing "diff" results. A new row is matched to an old row only + * if the two rows are identical (record image equality, "*=") and, in + * addition, every column covered by a usable unique index compares equal + * using that index's equality operator. Since those operators do not + * consider NULL equal to NULL, two rows that are identical but have a NULL in + * some unique-key column never match; the old row is deleted and the new one + * inserted, even though nothing has changed. That is inefficient if there are + * many such rows, but it gives the right answer. + * + * The diff is only correct if every row matches at most one row on the other + * side of the join. For the old rows, the unique index guarantees this: two + * old rows matching the same new row would have to be equal, and non-NULL, in + * every unique-key column, which the index does not allow. The new rows have + * not been checked against the unique indexes yet, so there is no such + * guarantee for them. If two new rows are identical and have no NULL in any + * unique-key column, both match the same old row, and the join concludes that + * nothing needs to be inserted. We therefore check the new data for such rows + * before populating the diff table, and raise an error if any are found. + * Identical new rows that do have a NULL in a unique-key column are harmless: + * they cannot match any old row, so all of them are inserted, and the unique + * index then decides whether they are allowed. A NULL in a column that no + * usable unique index covers does not prevent a match, so it gives no such + * protection. * * The temporary table used to hold the diff results contains just the TID of * the old record (if matched) and the ROW from the new table as a single @@ -607,8 +622,12 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, ListCell *indexoidscan; int16 relnatts; Oid *opUsedForQual; + StringInfoData keynotnullbuf; + StringInfoData keyeqbuf; initStringInfo(&querybuf); + initStringInfo(&keynotnullbuf); + initStringInfo(&keyeqbuf); matviewRel = table_open(matviewOid, NoLock); matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)), RelationGetRelationName(matviewRel)); @@ -635,45 +654,6 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) 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. - * - * Note: here and below, we use "tablename.*::tablerowtype" as a hack to - * keep ".*" from being expanded into multiple columns in a SELECT list. - * Compare ruleutils.c's get_variable(). - */ - 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.* " - "AND newdata2.ctid OPERATOR(pg_catalog.<>) " - "newdata.ctid)", - tempname, tempname, tempname); - if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT) - elog(ERROR, "SPI_exec failed: %s", querybuf.data); - if (SPI_processed > 0) - { - /* - * Note that this ereport() is returning data to the user. Generally, - * we would want to make sure that the user has been granted access to - * this data. However, REFRESH MAT VIEW is only able to be run by the - * owner of the mat view (or a superuser) and therefore there is no - * need to check for access to data in the mat view. - */ - ereport(ERROR, - (errcode(ERRCODE_CARDINALITY_VIOLATION), - errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns", - RelationGetRelationName(matviewRel)), - errdetail("Row: %s", - SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); - } - /* * Create the temporary "diff" table. * @@ -699,7 +679,13 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY) elog(ERROR, "SPI_exec failed: %s", querybuf.data); - /* Start building the query for populating the diff table. */ + /* + * Start building the query for populating the diff table. + * + * Note: here and below, we use "tablename.*::tablerowtype" as a hack to + * keep ".*" from being expanded into multiple columns in a SELECT list. + * Compare ruleutils.c's get_variable(). + */ resetStringInfo(&querybuf); appendStringInfo(&querybuf, "INSERT INTO %s " @@ -802,6 +788,26 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, op, rightop, attrtype); + /* + * Also build the conditions for the duplicate check below: + * this column must be non-NULL for a new row to be able to + * match an old one, and the same equality clause, applied + * between two new rows, lets that check use a hash join. + */ + if (foundUniqueIndex) + { + appendStringInfoString(&keynotnullbuf, " AND "); + appendStringInfoString(&keyeqbuf, " AND "); + } + appendStringInfo(&keynotnullbuf, "%s IS NOT NULL", leftop); + + rightop = quote_qualified_identifier("newdata2", + NameStr(attr->attname)); + generate_operator_clause(&keyeqbuf, + leftop, attrtype, + op, + rightop, attrtype); + foundUniqueIndex = true; } } @@ -832,6 +838,57 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner, "WHERE newdata.* IS NULL OR mv.* IS NULL " "ORDER BY tid"); + /* + * Before we can count on the "diff" results, we must make sure that the + * new data has no duplicate rows that could match the same old row; see + * the function header comment. Those are rows that are identical and + * have no NULL in any unique-key column. Since the rows are identical, + * it is enough to test one of them for NULLs. 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. + * + * The equality conditions on the unique-key columns don't change the + * result, since identical rows with non-NULL keys always satisfy them. + * They are there because "*=" is not hashable, and without them the check + * would have to sort the whole new data set twice. + */ + { + StringInfoData dupcheckbuf; + + initStringInfo(&dupcheckbuf); + appendStringInfo(&dupcheckbuf, + "SELECT newdata.*::%s FROM %s newdata " + "WHERE %s AND EXISTS " + "(SELECT 1 FROM %s newdata2 " + "WHERE %s " + "AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* " + "AND newdata2.ctid OPERATOR(pg_catalog.<>) " + "newdata.ctid)", + tempname, tempname, keynotnullbuf.data, tempname, + keyeqbuf.data); + if (SPI_execute(dupcheckbuf.data, false, 1) != SPI_OK_SELECT) + elog(ERROR, "SPI_exec failed: %s", dupcheckbuf.data); + if (SPI_processed > 0) + { + /* + * Note that this ereport() is returning data to the user. + * Generally, we would want to make sure that the user has been + * granted access to this data. However, REFRESH MAT VIEW is only + * able to be run by the owner of the mat view (or a superuser) + * and therefore there is no need to check for access to data in + * the mat view. + */ + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns", + RelationGetRelationName(matviewRel)), + errdetail("Row: %s", + SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)))); + } + pfree(dupcheckbuf.data); + } + /* Populate the temporary "diff" table. */ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 0355720dfc6..6887ae131bc 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -398,6 +398,58 @@ 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 DETAIL: Row: (1,10) +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- duplicate rows must be detected even if they have NULLs outside the +-- unique-key columns +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES ('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES ('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +ERROR: could not create unique index "mvtest_mv_a_idx" +DETAIL: Key (a)=(test) is duplicated. +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +ERROR: new data for materialized view "mvtest_mv" contains duplicate rows without any null columns +DETAIL: Row: (test,) +SELECT * FROM mvtest_mv; + a | b +------+--- + test | +(1 row) + +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- but duplicate rows with a NULL in a unique-key column are allowed +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES (NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES (NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT * FROM mvtest_mv; + a | b +---+--- + | + | +(2 rows) + +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES ('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a, b); +INSERT INTO mvtest_foo VALUES ('test', NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT * FROM mvtest_mv; + a | b +------+--- + test | + test | +(2 rows) + DROP TABLE mvtest_foo CASCADE; NOTICE: drop cascades to materialized view mvtest_mv -- make sure that all columns covered by unique indexes works diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 934426b9ae8..8c72aaaec17 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -135,6 +135,37 @@ REFRESH MATERIALIZED VIEW mvtest_mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; DROP TABLE mvtest_foo CASCADE; +-- duplicate rows must be detected even if they have NULLs outside the +-- unique-key columns +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES ('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES ('test', NULL); +REFRESH MATERIALIZED VIEW mvtest_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT * FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + +-- but duplicate rows with a NULL in a unique-key column are allowed +CREATE TABLE mvtest_foo(a int, b int); +INSERT INTO mvtest_foo VALUES (NULL, NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo VALUES (NULL, NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT * FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + +CREATE TABLE mvtest_foo(a text, b text); +INSERT INTO mvtest_foo VALUES ('test', NULL); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a, b); +INSERT INTO mvtest_foo VALUES ('test', NULL); +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +SELECT * FROM mvtest_mv; +DROP TABLE mvtest_foo CASCADE; + -- make sure that all columns covered by unique indexes works CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; -- 2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-11-17 21:37  Zsolt Parragi <zsolt.parragi@percona.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  1 sibling, 0 replies; 26+ messages in thread

From: Zsolt Parragi @ 2026-11-17 21:37 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Joao Rito <joaovictor.rito@gmail.com>; Yugo Nagata <nagata@sraoss.co.jp>; Robert Haas <robertmhaas@gmail.com>; Rafia Sabih <rafia.pghackers@gmail.com>; cca5507 <cca5507@qq.com>; Giuliano Gagliardi <gogi@gogi.tv>

Hello

On Tue, 17 Nov 2026, surya poondla <suryapoondla4@gmail.com> wrote:
> 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.

CREATE TYPE pair AS (x int, y int);
CREATE TABLE t (k pair, b text);
INSERT INTO t VALUES (ROW(1, NULL), 'x');
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
CREATE UNIQUE INDEX ON m (k);
INSERT INTO t VALUES (ROW(1, NULL), 'x');
REFRESH MATERIALIZED VIEW CONCURRENTLY m;
-- master: ERROR: new data for materialized view "m" contains
duplicate rows without any null columns
-- v8: succeeds, m has 1 row while t has 2

newdata.k IS NOT NULL uses row semantics, so it is false for
ROW(1,NULL) and the pre-check skips the row, but the join compares k
with record_eq, which treats NULL fields as equal, so both new rows
still match the same old row. The same happens with a domain over a
composite type, and with ROW(NULL,NULL). Maybe it could use IS
DISTINCT FROM NULL instead?

> It could
> say "...without any null key columns", maybe on master only.

I would simply stop at "contains duplicate rows", and remove the rest
of the message.






^ permalink  raw  reply  [nested|flat] 26+ messages in thread

* Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
@ 2026-11-18 19:03  João Rito <joaovictor.rito@gmail.com>
  parent: surya poondla <suryapoondla4@gmail.com>
  1 sibling, 0 replies; 26+ messages in thread

From: João Rito @ 2026-11-18 19:03 UTC (permalink / raw)
  To: suryapoondla4@gmail.com; +Cc: nagata@sraoss.co.jp; robertmhaas@gmail.com; rafia.pghackers@gmail.com; cca5507@qq.com; gogi@gogi.tv; pgsql-bugs@lists.postgresql.org

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@gmail.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
>

^ permalink  raw  reply  [nested|flat] 26+ messages in thread


end of thread, other threads:[~2026-11-18 19:03 UTC | newest]

Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-09 06:49 Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY Giuliano Gagliardi <gogi@gogi.tv>
2026-02-11 19:48 ` Rafia Sabih <rafia.pghackers@gmail.com>
2026-02-11 19:56 ` surya poondla <suryapoondla4@gmail.com>
2026-02-12 00:34   ` surya poondla <suryapoondla4@gmail.com>
2026-02-12 15:47     ` surya poondla <suryapoondla4@gmail.com>
2026-02-13 02:08       ` surya poondla <suryapoondla4@gmail.com>
2026-02-13 23:37         ` Rafia Sabih <rafia.pghackers@gmail.com>
2026-02-18 22:32           ` surya poondla <suryapoondla4@gmail.com>
2026-03-02 22:45             ` surya poondla <suryapoondla4@gmail.com>
2026-03-05 01:01               ` Rafia Sabih <rafia.pghackers@gmail.com>
2026-03-09 13:57                 ` Rafia Sabih <rafia.pghackers@gmail.com>
2026-03-12 00:45                   ` surya poondla <suryapoondla4@gmail.com>
2026-03-12 11:33                     ` cca5507 <cca5507@qq.com>
2026-03-13 07:20                       ` cca5507 <cca5507@qq.com>
2026-03-20 18:01                         ` surya poondla <suryapoondla4@gmail.com>
2026-03-21 06:19                           ` cca5507 <cca5507@qq.com>
2026-03-25 22:45                             ` surya poondla <suryapoondla4@gmail.com>
2026-05-18 16:48                               ` surya poondla <suryapoondla4@gmail.com>
2026-07-16 09:19                                 ` Rafia Sabih <rafia.pghackers@gmail.com>
2026-07-21 18:55                                   ` surya poondla <suryapoondla4@gmail.com>
2026-07-27 20:28                                     ` Robert Haas <robertmhaas@gmail.com>
2026-07-31 07:44                                       ` Yugo Nagata <nagata@sraoss.co.jp>
2026-11-14 21:33                                         ` Joao Rito <joaovictor.rito@gmail.com>
2026-11-17 05:36                                           ` surya poondla <suryapoondla4@gmail.com>
2026-11-17 21:37                                             ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-11-18 19:03                                             ` João Rito <joaovictor.rito@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox