public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rafia Sabih <[email protected]>
To: Giuliano Gagliardi <[email protected]>
Cc: [email protected]
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: Wed, 11 Feb 2026 20:48:04 +0100
Message-ID: <CA+FpmFedy3XWRhn8_T6y=K9cfRQE98hAQs-2BM9riWQz8oURuQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Sun, 8 Feb 2026 at 22:49, Giuliano Gagliardi <[email protected]> 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:
[application/octet-stream] 0001-Check-for-duplicate-rows-with-NULLs.patch (1.2K, 3-0001-Check-for-duplicate-rows-with-NULLs.patch)
download | inline diff:
From 0ca4b1214c86f1b959e4339cc9b8e0a1c6aa0fd3 Mon Sep 17 00:00:00 2001
From: Rafia Sabih <[email protected]>
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)
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
In-Reply-To: <CA+FpmFedy3XWRhn8_T6y=K9cfRQE98hAQs-2BM9riWQz8oURuQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox