Thread: how to find a tablespace for the table?

hello,

I'm looking into pg_tables view and only one tablespace is displayed is
pg_global.
All my tables are created in my custom tablespace and that column is empty
for them.

select * from pg_tablespace  show my tablespace, pgAdmin shows that
tablespace for each table. I need to query the data dictionary to find the
tablespace for the table in my proc.

    select t.spcname, c.relname AS tablename from pg_class c , pg_tablespace
t
where t.oid = c.reltablespace

does not show my tables, only the dd tables.

SELECT COALESCE(tbs.spcname, '*') AS tbsname
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_namespace AS s
ON (s.oid = t.relnamespace)
LEFT OUTER JOIN pg_catalog.pg_tablespace AS tbs
ON (tbs.oid = t.reltablespace)
WHERE t.relname like 'summ%' AND s.nspname = 'public';

returns *

please help.

thank you.

Helen


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4500200.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 06/17/2011 06:50 PM, hyelluas wrote:
> I'm looking into pg_tables view and only one tablespace is displayed is
> pg_global.
> All my tables are created in my custom tablespace and that column is empty
> for them.
>

I'm not sure what's wrong here, but the query you are trying to use to
decode this information doesn't look quite right.  pg_tables is just a
regular query; here is its source code:

CREATE VIEW pg_tables AS
     SELECT
         N.nspname AS schemaname,
         C.relname AS tablename,
         pg_get_userbyid(C.relowner) AS tableowner,
         T.spcname AS tablespace,
         C.relhasindex AS hasindexes,
         C.relhasrules AS hasrules,
         C.relhastriggers AS hastriggers
     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
          LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
     WHERE C.relkind = 'r';

I think that if you start with this and try to experiment from there,
you may be able to figure out what's going on here a little better.
This connects up the main relevant tables in the right way.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

thank you Greg,

here is what I get, I createed view as you suggested.
I'm not sure why tablespace column is empty

profiler1=# select * from pg_tables where schemaname ='public' limit 10;
 schemaname |        tablename        | tableowner | tablespace | hasindexes
| hasrules | hastri
ers
------------+-------------------------+------------+------------+------------+----------+-------
----
 public          | ttt                          | postgres   |
| f          | f        | f
 public          | summ_hrly_1514609   | postgres   |                 | t
| f        | f
 public          | summ_5min_1514610  | postgres   |                 | t
| f        | f
 public          | exp_cnt                   | postgres   |
| f          | f        | f
 public          | auth_type                | postgres   |                 |
t          | f        | f
 public          | druid_mapping           | postgres   |                 |
t          | f        | f
 public          | application_category  | postgres   |                 | t
| f        | f
 public          | application_risk          | postgres   |
| t          | f        | f
 public          | policy_history            | postgres   |
| t          | f        | f
 public          | datasource               | postgres   |                 |
t          | f        | f
(10 rows)


thank you.
Helen


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507266.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

well,

here is the query :

profiler1=# select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
 relname | reltablespace
---------+---------------


why it shows no records?

 profiler1=#  select * from pg_catalog.pg_tables where
tablename='application_category';
 schemaname |      tablename       | tableowner | tablespace | hasindexes |
hasrules | hastrigge
rs
------------+----------------------+------------+------------+------------+----------+----------
---
 public     | application_category | postgres   |            | t          |
f        | f
(1 row)


and that query show empty for the tablespace...

thank you
Helen

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

here it is :


profiler1=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner |     spclocation      | spcacl
-------+------------+----------+----------------------+--------
  1663 | pg_default |       10 |                      |
  1664 | pg_global  |       10 |                      |
 19955 | profiler1  |       10 | /data/psql/profiler1 |
(3 rows)


profiler1=# select relname,reltablespace from pg_class where
reltablespace=19955;
 relname | reltablespace
---------+---------------
(0 rows)


thanks
Helen

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508020.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

profiler1=#  select * from pg_catalog.pg_tables where
tablename='application_category';
 schemaname |      tablename       | tableowner | tablespace | hasindexes |
hasrules | hastrigge
rs
------------+----------------------+------------+------------+------------+----------+----------
---
 public     | application_category | postgres   |            | t          |
f        | f
(1 row)

profiler1=# select relname,reltablespace from pg_class where
relname='application_category';
       relname        | reltablespace
----------------------+---------------
 application_category |             0
(1 row)


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

that is exactly the case - application_category table is in "profiler1"
tablespace as well as all tables in my "profilre1" database.

I'm not sure how to "update catalog"... vacuum ?




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508315.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

I've got it now - it will be 0/empty for a table in "default tablespace", if
the table has been created in a different tablespace - it will show the
name.

thank you!.

Helen

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508750.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.