Re: BUG #14290: materialized view refresh doesn't use temp_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(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)