From: | rotten(at)windfish(dot)net |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14290: materialized view refresh doesn't use temp_tablespace |
Date: | 2016-08-18 19:06:23 |
Message-ID: | 20160818190623.1529.12440@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14290
Logged by: Rick Otten
Email address: rotten(at)windfish(dot)net
PostgreSQL version: 9.5.4
Operating system: Mac or Ubuntu
Description:
I've recently observed that if while refreshing a materialized view, temp
space is required, it will not use the default temp_tablespaces defined in
postgresql.conf. Instead it uses the tablespace where the materialized view
lives.
This surprised me the other day when my production database ran out of disk
space. I was able to confirm the behavior in my development environment.
To reproduce:
1) Set up a temp tablespace.
2) Configure temp_tablespaces in postgrsql.conf to use that tablespace.
3) Turn on logging of tablespace names.
4) Reduce work_mem (so that temp space is more likely to be required).
5) Bounce the DB, or reload the postgresql.conf
6) Refresh a fairly large materialized view.
7) Observe in the logs where the temp tablespace was created.
You can do it concurrently, or not. In both cases the temporary tables end
up in the same tablespace as the materialized view, not in the
temp_tablespace.
If this can't be easily changed to use the default temp tablespaces, it
would be good if the documentation were updated to alert materialized view
users to this caveat.
Refreshing a materialized view may use as much temporary space, or more,
than its current size. For example, I have a 100G materialized view.
Refreshing it uses almost 300G additional disk while the refresh is running.
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2016-08-18 19:46:39 | Re: BUG #14150: Attempted to delete invisible tuple |
Previous Message | Tom Lane | 2016-08-18 18:51:57 | Re: BUG #14289: Potential bug: "invalid attribute number" when dblink result is assigned in PL/PGSQL |