Refreshing a materialized view (not concurrently) ends with 'No space left' error

Question

There is a question with a similar title here: https://dba.stackexchange.com/questions/271237/postgresql-refreshing-materialized-view-fails-with-no-space-left-on-device-an

However, the symptoms I'm experiencing seem different:

• when refreshing with REFRESH MATERIALIZED VIEW mySchema.myView from within a Spring Boot application, PostgresSQLwrites tmp files as long as there is space on the device, currently 128gb.
  • SELECT * FROM pg_stat_activity shows that only this query is currently being executed.
• when executing the very same refresh query via any SQL console (like PgAdmin), all is well. The view is refreshed within a few seconds, writing no more than a few 100mb of tmp data.

We currently use PostgreSQL 13.3, but 11.11 showed the same behaviour.

JDBC Driver versions that were used:

• 42.2.14
• 42.2.18

So, I have no idea where this is coming from. Since refreshing using some SQL console works as intended, I don't see optimizing the query as the first thing to do. Also logging all SQL generated by the application shows nothing suspicious.

If it helps, here is the query for the view. It depends on other views (*_c), which all refresh fine:

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

Any ideas on how I could debug this?

update

The refresh is part of a batch job within a Spring Boot application. It's part of a bigger project where everything is new and shiny. The application and PostgreSQL are to be deployed within OpenShift. Just to add some more context.

The same behavior occurs in a locally installed PostgreSQL (13.2, Windows), as well as in the containerized version (13.3, Linux).

The original developer put the refresh logic inside an @AfterStep listener. I have now moved it to a separate Spring Batch tasklet in order to have it in a separate transaction context (see In Spring Batch, can I insert data in a beforeStep implementation). Also, we separated the WITH part into it's own view. Still the same, OK from console, NOK from app.

I will check to see if anything from @borchvm 's link will help. And also, I have an older version of the view which produces the same data, but is much much slower. So, at least, I still have something to hold on to. Also, the original developer who knows the data structure like his pocket will try to find another, more efficient way.

Answer 1

You do not have enough free temp space. I guess tables have too much data. Try to re-write your select more efficiently. Or the worst solution is seperate your code in different materialized views then merge them into a single materialized view :)

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