Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux