>>>>> "rotten" == rotten <rotten@windfish.net> writes:
rotten> I've recently observed that if while refreshing a materialized
rotten> view, temp space is required, it will not use the default
rotten> temp_tablespaces defined in postgresql.conf. Instead it uses
rotten> the tablespace where the materialized view lives.
rotten> This surprised me the other day when my production database ran
rotten> out of disk space. I was able to confirm the behavior in my
rotten> development environment.
rotten> To reproduce:
rotten> 1) Set up a temp tablespace.
rotten> 2) Configure temp_tablespaces in postgrsql.conf to use that tablespace.
rotten> 3) Turn on logging of tablespace names.
rotten> 4) Reduce work_mem (so that temp space is more likely to be required).
rotten> 5) Bounce the DB, or reload the postgresql.conf
rotten> 6) Refresh a fairly large materialized view.
rotten> 7) Observe in the logs where the temp tablespace was created.
I can't reproduce this.
postgres=# show server_version;
server_version
----------------
9.5.4
postgres=# create materialized view mvx1 as select * from generate_series(1,100000);
SELECT 100000
postgres=# set temp_tablespaces = 't1';
SET
postgres=# set log_temp_files = 0;
SET
postgres=# set work_mem = '64kB';
SET
postgres=# refresh materialized view mvx1;
LOG: temporary file: path "pg_tblspc/18002/PG_9.5_201510051/pgsql_tmp/pgsql_tmp91239.1", size 1400000
STATEMENT: refresh materialized view mvx1;
REFRESH MATERIALIZED VIEW
(this clearly shows that the temp file created by the FunctionScan of
generate_series is located in tablespace oid 18002, which happens to be
t1)
--
Andrew (irc:RhodiumToad)