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

performance postgresql space tablespace

18,844

Solution 1

SQL> create tablespace temp_tbs location '/some/big/disk';
change temp_tablespaces = 'temp_tbs' in postgresql.conf.
select pg_reload_conf();
enjoy

Solution 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.

18,844

Author by

Yousuf Sultan

• Design, develop and deliver BI solutions such as reports, dashboards and, ad hoc data access solutions with consideration to best practices and reporting needs. • Understand the data model as well as become familiar with the business information in each of the source systems. • Build effective reporting dashboards. • Ability to create BI visualizations using Tableau that provides insights for our Business customers. • Extensive knowledge in various reporting objects like Calculated Fields, Groups, Sets, Hierarchies, Parameters, Filters, Actions, Formatting and sorting in Tableau. • Worked extensively on joining tables, multiple data connections using Data Blending. • Analyse requirements and develop ETL/BI with minimal supervision. • Work with analysts for technical delivery of reporting solutions. • Debug report issues by analysing the data sources and SQLs to provide quick resolution. • Extensive experience in reporting and Business Intelligence applications • Excellent data analysis and SQL skills • Ability to handle multiple tasks simultaneously, react to problems quickly and understand the complexity. • Technical documentation of all delivered artifacts. • Excellent attention to detail and organizational skills. • Mentor team members on various aspects of BI development. • Ability to work in fast-paced environment and meet tight deadlines. • Proficient in writing T-SQL queries for reporting and data analysis. • 6+ years hands-on experience with SQL/T-SQL and SSIS/SSRS • 3+ years' experience with Tableau • 1+ years’ experience with Power BI.

Updated on July 09, 2022

Comments

Yousuf Sultan almost 2 years
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.
Vivek S. about 9 years

SET temp_tablespaces = temp_tbs; can be also used
Yousuf Sultan about 9 years

How can we know that the function being executed will use this particular temp tablespaces?
Vao Tsun about 9 years

@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
Rishikesh Teke about 6 years

Is there is another way because i don't have permission to postgres configuration as well as unable to create temp table space ?
Vao Tsun about 6 years

no - If you have no permissions to use your big disk, you can't use it...
Eugen Konkov almost 5 years

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?
mlissner over 2 years

I'm about 90% sure that using SET temp_tablespaces will only last for your session.
mlissner over 2 years

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.