13

I am executing PostgreSQL functions to update a table which has a huge amount of data and the update happens for about 100000 records everyday. During the update I get an error saying:

"could not write to hash-join temporary file: No space left on device"

I have not really been able to get something useful to overcome this error. I got something where it says to SET a temporary table spaces. But I was not able to find how do I create a temporary table space where the data will be stored during the executing of the update procedure.

halfer
  • 19,824
  • 17
  • 99
  • 186
Yousuf Sultan
  • 3,055
  • 8
  • 35
  • 63

2 Answers2

11
  1. SQL> create tablespace temp_tbs location '/some/big/disk';
  2. change temp_tablespaces = 'temp_tbs' in postgresql.conf.
  3. select pg_reload_conf();
  4. enjoy
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 2
    `SET temp_tablespaces = temp_tbs;` can be also used – Vivek S. Apr 13 '15 at 11:07
  • How can we know that the function being executed will use this particular temp tablespaces? – Yousuf Sultan Apr 13 '15 at 16:02
  • @YousufSultan it uses any of mentioned in temp_tablespaces... so if you specify severeal within a comma, it uses any of those in order you provided. If you specify only one - it uses it, if none set, it uses pg_default – Vao Tsun Apr 14 '15 at 06:31
  • Is there is another way because i don't have permission to postgres configuration as well as unable to create temp table space ? – Rishikesh Teke May 28 '18 at 10:29
  • no - If you have no permissions to use your big disk, you can't use it... – Vao Tsun May 28 '18 at 10:33
  • after `1` I get: `directory "/pg" does not exist`, but I have `drwxr-xr-x 2 postgres postgres 4096 Jul 6 22:04 pg` at root. Why this error happen? – Eugen Konkov Jul 06 '19 at 19:07
  • I'm about 90% sure that using `SET temp_tablespaces` will only last for your session. – mlissner Oct 09 '21 at 04:13
  • Big omission here. You have to run this too: `grant create on tablespace temp_space to public;`. Otherwise only the `postgres` user can use the tablespace. – mlissner Oct 09 '21 at 04:45
2

I got the same issue, but I was working with docker.

Just in case if you are working with docker too, go to:

Docker Preferences > Disk Panel > Disk image size and increase it.

It solved my problem.

alxlives
  • 5,084
  • 4
  • 28
  • 50
pruthvi nath
  • 21
  • 2