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

On Thu, Aug 18, 2016 at 2:06 PM, <rotten(at)windfish(dot)net> wrote:

> 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
> lives.
>
> 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
> temp_tablespace.

You are confusing two completely different things: temporary
objects (created by statements starting with CREATE TEMPORARY) and
temporary files (created when data spills to disk during, for
example, a sort, hash, or materialization of data internal to
processing some statement). The former are placed based on
temp_tablespaces; the latter are normally placed in the
base/pgsql_tmp/ subdirectory.

It would be possible to place them underneath a tablespace
specified by temp_tablespaces, and it might even be a good
enhancement to implement, but that is not the normal or default
location for temporary files.

Out of curiosity, and to help justify this as a feature request
worth pursuing, can you explain why you want to do this? For
example, have you placed your temporary tablespace on a faster
medium?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company