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;
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+)'))
),
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".
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.
Anything wrong with the tablespace setup
Can you try break down the query? That way it would be easier to debug.
The first part
Btw, which version of PostgreSQL is it and is it native PostgreSQL?
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+)'))
),
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)
Okay. Can you run
This postgresql.org/docs/10/functions-a.... seems to be the issue but I believe you are using superuser.
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".
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=#
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.
REF: postgresql.org/docs/release/11.0/#...