Discussion on: PostgreSQL temp files usage

[dinesh reddy]

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]

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]

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]

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]

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]

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