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

>>>>> "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)