CREATE TABLE fails with "ERROR: could not create temporary file" in Pivotal HDB

Products

• Services Suite

Issue/Introduction

Symptoms:
When trying to run SQL in Pivotal HDB, the following error message is displayed:

gpadmin=# create table test (id integer);
ERROR: could not create temporary file "pgsql_tmp/pgsql_tmp_sess37_w241398_combocid_map.0001": No such file or directory (fd.c:1221)

Environment

Cause

The issue is caused by either:

• The user running the SQL does not have access to the temporary working directory on the master.
• The directory does not exist.  

Resolution

Follow the steps below to resolve the issue: 

1. Log into Ambari. 
 

2. Locate the configuration setting hawq_master_temp_directory and take note of the value.

For example, in this case, the value is /tmp/hawq/master:

3. Log into the HAWQ master through SSH.
 

4. Confirm that:

• The directory exists
• The permissions are correct, and
• The user running the SQL has read and write access to the directory:

[gpadmin@hawq20 ~]$ ls -ltrd /tmp/hawq/master/
drwxr-xr-x. 3 gpadmin gpadmin 4096 Jun 4 01:37 /tmp/hawq/master/
[gpadmin@hawq20 ~]$

Note: To create the directory and set the correct permissions, the following commands can be run:

mkdir -p /tmp/hawq/master
chown gpadmin:gpadmin /tmp/hawq/master
chmod 755 /tmp/hawq/master/