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

On Fri, Aug 19, 2016 at 4:06 AM, <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.
>
> 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.

Per the docs:
https://www.postgresql.org/docs/9.6/static/runtime-config-client.html
This variable specifies tablespaces in which to create temporary
objects (temp tables and indexes on temp tables) when a CREATE command
does not explicitly specify a tablespace. Temporary files for purposes
such as sorting large data sets are also created in these tablespaces.

And as far as I know, there is no concept of temporary object for
matviews, so the documentation is correct IMO, and the behavior you
are seeing expected.
--
Michael