Re: BUG #14290: materialized view refresh doesn't usetemp_tablespace

Is t1 also the tablespace for your materialized view?  (It isn't clear from what you've posted below.)

In my environment my default tablespace for the database is different than the temp_tablespace.

On 2016-08-24 14:58, Andrew Gierth wrote:

"rotten" == rotten <rotten@windfish.net> writes:

 rotten> I've recently observed that if while refreshing a materializedrotten> view, temp space is required, it will not use the defaultrotten> temp_tablespaces defined in postgresql.conf.  Instead it usesrotten> the tablespace where the materialized view lives.
rotten> This surprised me the other day when my production database ranrotten> out of disk space.  I was able to confirm the behavior in myrotten> 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.confrotten> 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)