使用并发刷新物化视图

Question

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

REFRESH MATERIALIZED VIEW product_cat_mview;
REFRESH MATERIALIZED VIEW productsforproject;

我的解决方案是，当用户想要查看更新的数据时，他应该在网页上点击“刷新按钮”，但这需要大约50秒（在本地连接上大约需要2分钟），而所有这段时间用户都必须等待，这不好。

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

第一个查询

REFRESH MATERIALIZED VIEW CONCURRENTLY product_cat_mview;

代码正确，但第二个

REFRESH MATERIALIZED VIEW CONCURRENTLY productsforproject;

抱怨需要创建唯一索引。我尝试了在谷歌上找到的create index、unique index等方法，但仍然收到“创建唯一索引”的消息。

- Volodymyr Zavada

---

当您逐个运行刷新时，是否会引发此错误？ - David Aldridge

我一个一个地运行它们，每个之间延迟30秒。 - Volodymyr Zavada

Answer 1

您需要在物化视图本身上创建唯一索引。

这将类似于以下内容：

CREATE UNIQUE INDEX ON productsforproject (id);

将 id 替换为适当的唯一键列或这些列的（逗号分隔）组合。

- Laurenz Albe

---

我已经创建了这个索引："CREATE UNIQUE INDEX ON productsforproject (barcode, materialdescription ASC);" 但是我收到了错误信息：ERROR: cannot refresh materialized view "public.productsforproject" concurrently 提示：在一个或多个材料化视图的列上创建没有WHERE子句的唯一索引。 - Volodymyr Zavada

也许您需要指定正确的模式，例如 CREATE UNIQUE INDEX ON public.productsforproject ...。或者您错误地在错误的数据库中创建了它。 - Laurenz Albe

@LaurenzAlbe 我添加了一个唯一索引，但是当我尝试并发刷新和查询物化视图时，查询会一直卡住直到刷新结束。你有什么想法吗？ - Amityo

你使用了 CONCURRENTLY 吗？也许你可以开一个新的问题，展示查询、执行计划以及在刷新时 pg_locks 的内容。 - Laurenz Albe

@LaurenzAlbe：如果我正确理解了刷新并发机制，数据会被恢复到一个临时表中，然后旧表和新表会被交换。为什么需要创建唯一索引？ - Fabrice Chapuis

@FabriceChapuis 并发刷新不会发生这种情况。物化视图中的数据实际上是通过查询结果进行更新的，因此具有唯一索引。 - Laurenz Albe