DEV Community

PostgreSQL temp files usage

Bolaji Wahab on January 22, 2022

Certain query operations such as sort or hash table require some memory facility. This memory is provided by a runtime config work_mem. From the o...

Read full post

dinesh reddy • Jul 26 '22

path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))

what exactly is this part doing in the query especially FROM '^(?:\d.\d\d?|\d+)')??? query is failing with msg "ERROR: absolute path not allowed"

Bolaji Wahab • Jul 26 '22 • Edited

For this part of the query

path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))

When you create a tablespace, a directory is created in format PG_server_version_some_other_digit.
We check all tablespaces except pg_global for temp files.

For the error "ERROR: absolute path not allowed".

I cannot figure where the error seems to be coming from currently but it seems to be related to tablespace setup.
Are you using tablespaces? And if so, can you confirm the setup of the tablespaces?

Thank you.

dinesh reddy • Jul 27 '22

Anything wrong with the tablespace setup

Bolaji Wahab • Jul 27 '22

Can you try break down the query? That way it would be easier to debug.
The first part

WITH tablespaces AS (
    SELECT
        spcname AS tbl_name,
        coalesce(nullif(pg_tablespace_location(oid), ''), (current_setting('data_directory') || '/base')) AS tbl_location
    FROM pg_tablespace
),
tablespace_suffix AS (
    SELECT
        tbl_name,
        tbl_location || '/pgsql_tmp' AS path
    FROM tablespaces
    WHERE tbl_name = 'pg_default'
    UNION ALL
    SELECT
        tbl_name,
        tbl_location || '/' || path || '/pgsql_tmp'
    FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
    WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d\.\d\d?|\d+)'))
)
SELECT * FROM tablespace_suffix;

Bolaji Wahab • Jul 27 '22

Btw, which version of PostgreSQL is it and is it native PostgreSQL?

dinesh reddy • Jul 27 '22

Yes native postgresql version is 10.7 on oracle el 6 and query is failing here and when i comment second part after union all error not getting reported then

tablespace_suffix AS (
SELECT
tbl_name,
tbl_location || '/pgsql_tmp' AS path
FROM tablespaces
WHERE tbl_name = 'pg_default'
UNION ALL
SELECT
tbl_name,
tbl_location || '/' || path || '/pgsql_tmp'
FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))
),

dinesh reddy • Jul 27 '22

after commenting the second part of union all , am i missing anything

postgres=# WITH tablespaces AS (
postgres(# SELECT
postgres(# spcname AS tbl_name,
postgres(# coalesce(nullif(pg_tablespace_location(oid), ''), (current_setting('data_directory') || '/base')) AS tbl_location
postgres(# FROM pg_tablespace
postgres(# ),
postgres-# tablespace_suffix AS (
postgres(# SELECT
postgres(# tbl_name,
postgres(# tbl_location || '/pgsql_tmp' AS path
postgres(# FROM tablespaces
postgres(# WHERE tbl_name = 'pg_default'
postgres(# --UNION ALL
postgres(# -- SELECT
postgres(# -- tbl_name,
postgres(# -- tbl_location || '/' || path || '/pgsql_tmp'
postgres(# -- FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS path
postgres(# -- WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d.\d\d?|\d+)'))
postgres(# )
postgres-# SELECT * FROM tablespace_suffix;
tbl_name | path

------------+---------------------------------------
pg_default | /nfs-mnt/postgres/data/base/pgsql_tmp
(1 row)

Bolaji Wahab • Jul 27 '22

Okay. Can you run

SELECT pg_ls_dir('/nfs-mnt/postgres/tablespaces/omnia');

This postgresql.org/docs/10/functions-a.... seems to be the issue but I believe you are using superuser.

dinesh reddy • Jul 28 '22

Yes iam using postgres as login user

postgres=# SELECT pg_ls_dir('/nfs-mnt/postgres/tablespaces/omnia');
ERROR: absolute path not allowed
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

dinesh reddy • Jul 28 '22

looks like pg_ls_dir works only on postgresql datadirectory $PGDATA

postgres=# SELECT pg_ls_dir('/nfs-mnt/postgres/data/base');

pg_ls_dir

13457
1
147972689
13456
124079171
(5 rows)

postgres=# SELECT pg_ls_dir('/nfs-mnt/postgres/tablespaces/omnia');
ERROR: absolute path not allowed
postgres=#

Bolaji Wahab • Jul 28 '22

Oh yeah. I just confirmed the functions were limited to only the cluster directory and log directory in 9.6, 10. And they were only changed to support files outside cluster directory later from 11.

Bolaji Wahab • Jul 28 '22 • Edited

REF: postgresql.org/docs/release/11.0/#...

KMohsoe • Aug 23 '22

If the temporary file size is coming greater, please could you tell me how could I delete the temporary file in postgreSQL with the command?

Bolaji Wahab • Aug 23 '22 • Edited

Temporary files are cleaned up automatically once the queries using them are done processing or canceled. Deleting an in-use temporary files can lead to backend crashes or even server crashes.
Why do you want to delete the files? Are they stale/orphaned? You should only have orphaned/stale temporary files when there are crashes.

Stephen Price • Apr 15 '22

You mentioned that you avoided using "pg_ls_tmpdir" in the query since it's new as of PG12, but the query is using it. Is there an earlier version of the query that does not use it?
I ask because I'm trying to wrap my head around which queries are eating up my temp space, and due to the fact that I'm running Aurora PostgreSQL in AWS RDS, I cannot use "pg_ls_tmpdir" (the "rds_superuser" permissions explicitly deny it).

Bolaji Wahab • Apr 15 '22 • Edited

Hi, I believe you mean pg_ls_dir and pg_stat_file? Both functions are restricted to only superusers by default but execute can be granted to any user by a superuser.
I am not totally conversant with rds_superuser so you might not be able to call these functions.

JT • Mar 5

One simple solution if resource are available is to create a ram based file system and use that for your temp file destination.