| From: | surya poondla <suryapoondla4(at)gmail(dot)com> |
|---|---|
| To: | Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> |
| Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org, Joao Rito <joaovictor(dot)rito(at)gmail(dot)com>, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, cca5507 <cca5507(at)qq(dot)com>, Giuliano Gagliardi <gogi(at)gogi(dot)tv> |
| Subject: | Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY |
| Date: | 2026-11-20 23:47:16 |
| Message-ID: | CAOVWO5rHtPaji_o0bo83KV3Vq-7g_B+NFgsvAg-xxoM_CFL8ig@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi Zsolt,
Thank you for the review and the test case.
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?
>
>
You're right. I reproduced it on v8 with ROW(1,NULL), ROW(NULL,NULL)
and a domain over the composite type, in all three cases the refresh
succeeds and the view keeps one row, while master raises the error. It
also means my statement that v8 reports the same rows as v7 was wrong
for composite keys, sorry about that.
The attached v9 uses IS DISTINCT FROM NULL, as you suggested. It is
true exactly when the key value itself is not NULL, which is when the
equality operator in the join can match, including for composite values
with NULL fields. v9 also adds a regression test with a composite-type
key.
Regards,
Surya Poondla
| Attachment | Content-Type | Size |
|---|---|---|
| v9-0001-Fix-REFRESH-MATERIALIZED-VIEW-CONCURRENTLY-with-d.patch | application/octet-stream | 17.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Christophe Pettus | 2026-11-21 00:16:37 | Re: BUG #19927: PostgreSQL 16: SQL dump adds experimental/incompatible options like \restrict |
| Previous Message | Zsolt Parragi | 2026-11-20 22:59:32 | Re: Timing issue in pg_basebackup |