spring - 刷新物化视图（非同时）以“没有剩余空间”错误结束

Question

这里有一个类似标题的问题：https ://dba.stackexchange.com/questions/271237/postgresql-refreshing-materialized-view-fails-with-no-space-left-on-device-an

但是，我遇到的症状似乎有所不同：

• 从 Spring Boot 应用程序中刷新时REFRESH MATERIALIZED VIEW mySchema.myView，只要设备上有空间（当前为 128gb），PostgresSQL 就会写入 tmp 文件。
  • SELECT * FROM pg_stat_activity表明当前只有这个查询正在执行。
• 当通过任何 SQL 控制台（如 PgAdmin）执行相同的刷新查询时，一切都很好。视图会在几秒钟内刷新，写入不超过 100mb 的 tmp 数据。

我们目前使用 PostgreSQL 13.3，但 11.11 表现出相同的行为。

使用的 JDBC 驱动程序版本：

• 42.2.14
• 42.2.18

所以，我不知道这是从哪里来的。由于使用某些 SQL 控制台进行刷新可以按预期工作，因此我不认为优化查询是第一件事。此外，记录应用程序生成的所有 SQL 也没有任何可疑之处。

如果有帮助，这里是视图的查询。这取决于其他视图 ( *_c)，它们都可以正常刷新：

WITH RECURSIVE chapters (code_id, chapter_id) AS (
            SELECT Ziffer.tael_id AS code_id, Ziffer.tael_id as chapter_id
            FROM tael_c AS Ziffer
            INNER JOIN tael_c AS Tariff ON Ziffer.tael_idt = Tariff.tael_id AND Ziffer.tael_typ = 25 AND Ziffer.validfrom < COALESCE(Tariff.invalidfrom, DATE '9999-12-31') AND Tariff.validfrom < COALESCE(Ziffer.invalidfrom, DATE '9999-12-31')
            UNION
            SELECT chapters.code_id, tete_c.tael_idp as chapter_id
            FROM chapters
            INNER JOIN tete_c ON chapters.chapter_id = tete_c.tael_idc
            INNER JOIN tael_c ON tete_c.tael_idc = tael_c.tael_id AND tael_c.tael_typ IN (3, 25)
        )
        SELECT Ziffer.tael_id,
               GREATEST(Ziffer.wsid, Kapitel.wsid, Conn.wsid, InExTael.wsid, InEx.wsid) AS wsid,
               GREATEST(Ziffer.validfrom, Kapitel.validfrom, Conn.validfrom, InExTael.validfrom, InEx.validfrom) AS validfrom,
               LEAST(Ziffer.invalidfrom, Kapitel.invalidfrom, Conn.invalidfrom, InExTael.invalidfrom, InEx.invalidfrom) AS invalidfrom,
               string_agg(InEx.txtx_txt, ',') FILTER (WHERE InExTael.tael_typ = 40) AS Incl,
               string_agg(InEx.txtx_txt, ',') FILTER (WHERE InExTael.tael_typ = 46) AS Excl
        FROM chapters
        INNER JOIN tael_c AS Ziffer ON Ziffer.tael_id = chapters.code_id
        INNER JOIN tael_c AS Kapitel ON chapters.chapter_id = Kapitel.tael_id AND Kapitel.validfrom < COALESCE(Ziffer.invalidfrom, DATE '9999-12-31') AND Ziffer.validfrom < COALESCE(Kapitel.invalidfrom, DATE '9999-12-31')
        INNER JOIN tete_c AS Conn ON Kapitel.tael_id = Conn.tael_idp AND Conn.validfrom < COALESCE(LEAST(Ziffer.invalidfrom, Kapitel.invalidfrom), DATE '9999-12-31') AND GREATEST(Ziffer.validfrom, Kapitel.validfrom) < COALESCE(Conn.invalidfrom, DATE '9999-12-31')
        INNER JOIN tael_c AS InExTael ON Conn.tael_idc = InExTael.tael_id AND InExTael.tael_typ IN (40, 46) AND InExTael.validfrom < COALESCE(LEAST(Ziffer.invalidfrom, Kapitel.invalidfrom, Conn.invalidfrom), DATE '9999-12-31') AND GREATEST(Ziffer.validfrom, Kapitel.validfrom, Conn.validfrom) < COALESCE(InExTael.invalidfrom, DATE '9999-12-31')
        INNER JOIN txtx_c AS InEx ON InEx.tael_id = InExTael.tael_id AND InEx.validfrom < COALESCE(LEAST(Ziffer.invalidfrom, Kapitel.invalidfrom, Conn.invalidfrom, InExTael.invalidfrom), DATE '9999-12-31') AND GREATEST(Ziffer.validfrom, Kapitel.validfrom, Conn.validfrom, InExTael.validfrom) < COALESCE(InEx.invalidfrom, DATE '9999-12-31')
        GROUP BY 1, 2, 3, 4

关于如何调试它的任何想法？

更新

刷新是 Spring Boot 应用程序中批处理作业的一部分。这是一个更大的项目的一部分，一切都是新的和闪亮的。应用程序和 PostgreSQL 将部署在 OpenShift 中。只是为了添加更多上下文。

同样的行为发生在本地安装的 PostgreSQL（13.2，Windows）以及容器化版本（13.3，Linux）中。

原始开发人员将刷新逻辑放在 @AfterStep 侦听器中。我现在已经将它移动到一个单独的 Spring Batch 小任务中，以便将它放在一个单独的事务上下文中（请参阅在 Spring Batch 中，我可以在 beforeStep 实现中插入数据）。此外，我们将 WITH 部分分离到它自己的视图中。还是一样，控制台中的 OK，应用程序中的 NOK。

我会检查@borchvm 链接中的任何内容是否有帮助。而且，我有一个旧版本的视图，它产生相同的数据，但速度要慢得多。所以，至少，我还有一些东西要坚持。此外，像口袋一样了解数据结构的原始开发人员会尝试寻找另一种更有效的方法。

标签： springpostgresqljdbcsql-viewmaterialized-views

Answer 1

您没有足够的可用临时空间。我猜表有太多数据。尝试更有效地重写您的选择。或者最糟糕的解决方案是将您的代码分离到不同的物化视图中，然后将它们合并到一个物化视图中:)

psql> SELECT datname, temp_files AS "Temporary files",temp_bytes AS "Size of temporary files" FROM pg_stat_database ;