BUG #14290: materialized view refresh doesn't use temp_tablespace

From: rotten(at)windfish(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14290: materialized view refresh doesn't use temp_tablespace
Date: 2016-08-18 19:06:23
Message-ID: 20160818190623.1529.12440@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14290
Logged by: Rick Otten
Email address: rotten(at)windfish(dot)net
PostgreSQL version: 9.5.4
Operating system: Mac or Ubuntu

I've recently observed that if while refreshing a materialized view, temp
space is required, it will not use the default temp_tablespaces defined in
postgresql.conf. Instead it uses the tablespace where the materialized view

This surprised me the other day when my production database ran out of disk
space. I was able to confirm the behavior in my development environment.

To reproduce:
1) Set up a temp tablespace.
2) Configure temp_tablespaces in postgrsql.conf to use that tablespace.
3) Turn on logging of tablespace names.
4) Reduce work_mem (so that temp space is more likely to be required).
5) Bounce the DB, or reload the postgresql.conf
6) Refresh a fairly large materialized view.
7) Observe in the logs where the temp tablespace was created.

You can do it concurrently, or not. In both cases the temporary tables end
up in the same tablespace as the materialized view, not in the

If this can't be easily changed to use the default temp tablespaces, it
would be good if the documentation were updated to alert materialized view
users to this caveat.

Refreshing a materialized view may use as much temporary space, or more,
than its current size. For example, I have a 100G materialized view.
Refreshing it uses almost 300G additional disk while the refresh is running.


Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2016-08-18 19:46:39 Re: BUG #14150: Attempted to delete invisible tuple
Previous Message Tom Lane 2016-08-18 18:51:57 Re: BUG #14289: Potential bug: "invalid attribute number" when dblink result is assigned in PL/PGSQL