Why did pg_relation_filepath does not give a correct path ?

On Fri, 2012-04-20 at 11:35 +0200, F. BROUARD / SQLpro wrote:

Hi,

according to the documentation, the function pg_relation_filepath
"returns the entire file path name (relative to the database cluster's
data directory PGDATA) of the relation"

When my table are located in the pg_default tablespace, the gievn
relative path is correct

When my table are located on a specific tablespace, this function
returns incorrect dats such as :

pg_tblspc/25310/PG_9.1_201105231/16594/25311

only the "PG_9.1_201105231/16594/25311" is correct.

What does the "pg_tblspc/25310" do ???

How can I obtain the correct relative path ?

It is the correct relative path. It's relative to $PGDATA. Your
tablespace has the OID 25310. So, you have a symbolic link (or junction
if you are on Windows) named 25310 in your $PGDATA/pg_tblspc directory.
And PostgreSQL will access this table via the symbolic link. It really
uses this path: pg_tblspc/25310/PG_9.1_201105231/16594/25311

There's nothing wrong here.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Le 20/04/2012 12:05, Guillaume Lelarge a écrit :

On Fri, 2012-04-20 at 11:35 +0200, F. BROUARD / SQLpro wrote:

Hi,

according to the documentation, the function pg_relation_filepath
"returns the entire file path name (relative to the database cluster's
data directory PGDATA) of the relation"

When my table are located in the pg_default tablespace, the gievn
relative path is correct

When my table are located on a specific tablespace, this function
returns incorrect dats such as :

pg_tblspc/25310/PG_9.1_201105231/16594/25311

only the "PG_9.1_201105231/16594/25311" is correct.

What does the "pg_tblspc/25310" do ???

How can I obtain the correct relative path ?

It is the correct relative path. It's relative to $PGDATA. Your
tablespace has the OID 25310. So, you have a symbolic link (or junction
if you are on Windows) named 25310 in your $PGDATA/pg_tblspc directory.
And PostgreSQL will access this table via the symbolic link. It really
uses this path: pg_tblspc/25310/PG_9.1_201105231/16594/25311

There's nothing wrong here.

yes, but I want to have the real path, not the symbolic PG path...

Do you know haw can I obtain it by a calssical SQL Query ?

A +

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

On Fri, 2012-04-20 at 14:47 +0200, F. BROUARD / SQLpro wrote:

Le 20/04/2012 12:05, Guillaume Lelarge a écrit :

On Fri, 2012-04-20 at 11:35 +0200, F. BROUARD / SQLpro wrote:

Hi,

according to the documentation, the function pg_relation_filepath
"returns the entire file path name (relative to the database cluster's
data directory PGDATA) of the relation"

When my table are located in the pg_default tablespace, the gievn
relative path is correct

When my table are located on a specific tablespace, this function
returns incorrect dats such as :

pg_tblspc/25310/PG_9.1_201105231/16594/25311

only the "PG_9.1_201105231/16594/25311" is correct.

What does the "pg_tblspc/25310" do ???

How can I obtain the correct relative path ?

It is the correct relative path. It's relative to $PGDATA. Your
tablespace has the OID 25310. So, you have a symbolic link (or junction
if you are on Windows) named 25310 in your $PGDATA/pg_tblspc directory.
And PostgreSQL will access this table via the symbolic link. It really
uses this path: pg_tblspc/25310/PG_9.1_201105231/16594/25311

yes, but I want to have the real path, not the symbolic PG path...

Do you know haw can I obtain it by a calssical SQL Query ?

Something like this might do the trick:

SELECT
CASE
WHEN coalesce(t.spclocation, '') = ''
THEN
current_setting('data_directory')||'/'||pg_relation_filepath(c.oid)
ELSE replace(pg_relation_filepath(c.oid),
'pg_tblspc/'||t.oid::text,
t.spclocation)
END AS filepath
FROM pg_class AS c
LEFT JOIN pg_tablespace AS t ON c.reltablespace=t.oid
WHERE
c.relname='pg_class';

At least, it works on my 9.1.

BTW, please, keep the list posted.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Le 20/04/2012 16:18, Guillaume Lelarge a écrit :

SELECT
CASE
WHEN coalesce(t.spclocation, '') = ''
THEN
current_setting('data_directory')||'/'||pg_relation_filepath(c.oid)
ELSE replace(pg_relation_filepath(c.oid),
'pg_tblspc/'||t.oid::text,
t.spclocation)
END AS filepath
FROM pg_class AS c
LEFT JOIN pg_tablespace AS t ON c.reltablespace=t.oid
WHERE
c.relname='pg_class';

I was at the same point, doing another way :

COALESCE(spclocation, setting) ||
CASE
WHEN T.spcname <> 'pg_global'
THEN SUBSTRING(REPLACE(pg_relation_filepath(C.oid),
'pg_tblspc/', ''),
POSITION('/' IN
REPLACE(pg_relation_filepath(C.oid), 'pg_tblspc/', '')),

CHARACTER_LENGTH(REPLACE(pg_relation_filepath(C.oid), 'pg_tblspc/', '')))
WHEN T.spcname = 'pg_global'
THEN (SELECT setting FROM pg_settings WHERE name =
'data_directory')
|| '/' || pg_relation_filepath(C.oid)
ELSE '/' || pg_relation_filepath(C.oid) END AS location

... using pg_setting

This returns the same datas

A +

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************