Обсуждение: Why did pg_relation_filepath does not give a correct path ?
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 *************************
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 *************************