Re: BUG #14290: materialized view refresh doesn't usetemp_tablespace - Mailing list pgsql-bugs

From Rick Otten
Subject Re: BUG #14290: materialized view refresh doesn't usetemp_tablespace
Date
Msg-id 763fbff79441cfde6dc68b0ad2877c21@www.windfish.net
Whole thread Raw
In response to Re: BUG #14290: materialized view refresh doesn't use temp_tablespace  (Michael Paquier <michael.paquier@gmail.com>)
List pgsql-bugs

Refreshing materialized views can definitely create temporary objects.  They even get logged when I turn on logging of temporary objects.

 

On 2016-08-24 03:47, Michael Paquier wrote:

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

 

pgsql-bugs by date:

Previous
From: Michael Paquier
Date:
Subject: Re: BUG #14290: materialized view refresh doesn't use temp_tablespace
Next
From: kashyap.nagendra@xerox.com
Date:
Subject: BUG #14292: Error in installing the postgresql 9.3