Re: 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(at)2ndQuadrant(dot)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.

That's right, if the tables are in default tablespace, those columns will be
blank, if any of the table created under any of the
tablespace then it will show up.

Eg:-
postgres=# show default_tablespace ; (this would be blank becz am in
pg_default/pg_global)
default_tablespace
--------------------

(1 row)

postgres=# create table foo(id int);
CREATE TABLE
postgres=# select * from pg_tables where tablename='foo';
-[ RECORD 1 ]---------
schemaname | public
tablename | foo
tableowner | postgres
tablespace |
hasindexes | f
hasrules | f
hastriggers | f

Now I have the table in one of my tablespace.

postgres=#create table tab_test(id int) tablespace t1;
Expanded display is on.
postgres=# select * from pg_tables where tablename='tab_test';
-[ RECORD 1 ]---------
schemaname | public
tablename | tab_test
tableowner | postgres
*tablespace | t1*
hasindexes | f
hasrules | f
hastriggers | f

If you want to know the tablespace default information, you can try with
this query.

select spcname, case spcname when 'pg_default' then (select setting from
pg_settings where name = 'data_directory')||'/base' when 'pg_global' then
(select setting from pg_settings where name = 'data_directory')||'/global'
else spclocation end from pg_tablespace;

To get the exact table's and its tablespace's below query will work.

select relname,reltablespace from pg_class where reltablespace in(select
oid from pg_tablespace where spcname not in ('pg_default','pg_global'));

---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Mon, Jun 20, 2011 at 11:40 PM, hyelluas <helen_yelluas(at)mcafee(dot)com> wrote:

> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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.

hmmm....Strange..

What is the output of

select oid,* from pg_tablespace;

and

select relname,reltablespace from pg_class where relname='
application_category';

---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Tue, Jun 21, 2011 at 12:48 AM, hyelluas <helen_yelluas(at)mcafee(dot)com> wrote:

> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

let me correct on this query, it shows only those tables which wont belong
to default_tablespace...

select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));

---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Tue, Jun 21, 2011 at 12:55 AM, Raghavendra <
raghavendra(dot)rao(at)enterprisedb(dot)com> wrote:

> hmmm....Strange..
>
> What is the output of
>
> select oid,* from pg_tablespace;
>
> and
>
> select relname,reltablespace from pg_class where relname='
> application_category';
>
>
> ---
> Best Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
>
> On Tue, Jun 21, 2011 at 12:48 AM, hyelluas <helen_yelluas(at)mcafee(dot)com>wrote:
>
>> 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.
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

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)

Whats the output of this..

select relname,reltablespace from pg_class where relname='
application_category';

---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Tue, Jun 21, 2011 at 2:16 AM, hyelluas <helen_yelluas(at)mcafee(dot)com> wrote:

> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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.

Can you give a try updating the catalogs with ANALYZE command and re-check ?

---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Tue, Jun 21, 2011 at 2:22 AM, hyelluas <helen_yelluas(at)mcafee(dot)com> wrote:

> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Actually to say, everything looks ok to me, until unless you say the table *
'**application_category'* on other tablespace ... :)

profiler1=# select relname,reltablespace from pg_class where
>> relname='application_category';
>> relname | reltablespace
>> ----------------------+---------------
>> application_category | 0
>> (1 row)
>>
>>
Above result, indicates that its in default tablespace.
http://www.postgresql.org/docs/9.0/static/catalog-pg-class.html

Regards
Raghav

>
>> --
>> 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.
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

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.