并发刷新物化视图 [英] Refresh materialized views with concurrency

问题描述

我有一个PostgreSQL数据库，在其中使用实例化视图。
当我尝试刷新这些实例化视图时出现问题。

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;

我的解决方案是，当用户想要查看更新的数据时，他应该单击刷新按钮在网页上，但这大约需要50秒钟（在本地连接上，距应用程序服务器大约2分钟），并且所有这些时间用户都必须等待，这是不好的。

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.

现在，我应该创建一个解决方案以每10分钟自动刷新这些实例化视图。
我创建了一个具有多线程的Java解决方案。但是我有一个问题。

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.

第一个查询

REFRESH MATERIALIZED VIEW CONCURRENTLY product_cat_mview;

工作正常，但第二个

REFRESH MATERIALIZED VIEW CONCURRENTLY productsforproject;

我需要创建一个唯一索引的抱怨。我尝试在Google中找到创建索引，唯一索引等，但仍然收到创建唯一索引的消息。

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".

推荐答案

您将必须在实例化视图本身上创建一个唯一索引。

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

这看起来像这样：

CREATE UNIQUE INDEX ON productsforproject (id);

用合适的唯一键列替换 id 或此类列的（逗号分隔）组合。

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

这篇关于并发刷新物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！