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)
