Skip site navigation (1) Skip section navigation (2)

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

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
To: "F(dot) BROUARD / SQLpro" <sqlpro(at)club-internet(dot)fr>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Why did pg_relation_filepath does not give a correct path ?
Date: 2012-04-20 14:18:23
Message-ID: 1334931503.4142.35.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: F. BROUARD / SQLproDate: 2012-04-20 14:43:09
Subject: Re: Why did pg_relation_filepath does not give a correct path ?
Previous:From: F. BROUARD / SQLproDate: 2012-04-20 14:15:37
Subject: Re: Why did pg_relation_filepath does not give a correct path ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group