27

I have a PostgreSQL DB, where I use materialized views. The problem occurs when I try to refresh these materialized views. 

REFRESH MATERIALIZED VIEW product_cat_mview;
REFRESH MATERIALIZED VIEW productsforproject;

My solution is, when the user want to see updated data, he should click a "refresh button" on the web page, but this takes about 50s (on a local connection and about 2 minutes from the application server) and all this time the user has to wait, which is not good.

Now I should create a solution to automatically refresh these materialized views every 10 minutes. I have created a Java solution with multithreading. But I have one problem.

The first query

REFRESH MATERIALIZED VIEW CONCURRENTLY product_cat_mview;

works correct, but the second

REFRESH MATERIALIZED VIEW CONCURRENTLY productsforproject;

complains that I need to create a unique index. I tried create index, unique index etc. that I found in google, but I still get the message to "Create unique index".

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
Volodymyr Zavada
  • 579
  • 2
  • 8
  • 18

1 Answers1

43

You will have to create a unique index on the materialized view itself.

This would look like this:

CREATE UNIQUE INDEX ON productsforproject (id);

Replace id with a suitable unique key column or a (comma separated) combination of such columns.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I have created this index. "CREATE UNIQUE INDEX ON productsforproject (barcode, materialdescription ASC);" But I have message : ERROR: cannot refresh materialized view "public.productsforproject" concurrently Hint: Create a unique index with no WHERE clause on one or more columns of the materialized view. – Volodymyr Zavada Jan 23 '17 at 10:53
  • Maybe you need to specify the correct schema, like `CREATE UNIQUE INDEX ON public.productsforproject ...`. Or you created it in the wrong database by mistake. – Laurenz Albe Jan 23 '17 at 10:54
  • @LaurenzAlbe I added a unique index but when I try to refresh concurrently and query the materialized view - the query is stuck until the refresh ends. any idea? – Amityo Oct 02 '19 at 14:57
  • 1
    Did you use `CONCURRENTLY`? Maybe open your own question for that and show the query, its execution plan and the contents of `pg_locks` while the refresh is running. – Laurenz Albe Oct 02 '19 at 15:03
  • 1
    @LaurenzAlbe: If I undersand the refresh concurrency mechanism correctly, the data is recovered into a temporary table then old and new table are switched. Why is it necessary to create a unique index? – Fabrice Chapuis Feb 25 '22 at 12:54
  • @FabriceChapuis That's not what happens with a concurrent refresh. The data in the materialized view are actually updated with the query results; hence the unique index. – Laurenz Albe Feb 25 '22 at 13:04