Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why did pg_relation_filepath does not give a correct path ?

Why did pg_relation_filepath does not give a correct path ?

From
"F. BROUARD / SQLpro"
Date:
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 ?

Thanks

--
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 *************************

Re: Why did pg_relation_filepath does not give a correct path ?

From
Guillaume Lelarge
Date:
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

Re: Why did pg_relation_filepath does not give a correct path ?

From
"F. BROUARD / SQLpro"
Date:
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 *************************

Re: Why did pg_relation_filepath does not give a correct path ?

From
Guillaume Lelarge
Date:
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

Re: Why did pg_relation_filepath does not give a correct path ?

From
"F. BROUARD / SQLpro"
Date:
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 *************************