public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: surya poondla <suryapoondla4@gmail.com>
To: Giuliano Gagliardi <gogi@gogi.tv>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: Wed, 11 Feb 2026 11:56:12 -0800
Message-ID: <CAOVWO5p7PieGTm3GJC8NAYgSEQLoBdZw9Se4u=EbPrr1mW7d5Q@mail.gmail.com> (raw)
In-Reply-To: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv>
References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: suryapoondla4@gmail.com, gogi@gogi.tv, pgsql-bugs@lists.postgresql.org
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
In-Reply-To: <CAOVWO5p7PieGTm3GJC8NAYgSEQLoBdZw9Se4u=EbPrr1mW7d5Q@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