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

>>>>> "rotten" == rotten <rotten(at)windfish(dot)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)