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.
