[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
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 USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
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 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] how to find a tablespace for the table?
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
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@enterprisedb.com> wrote:
> hmmmStrange..
>
> 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 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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
Re: [GENERAL] how to find a tablespace for the table?
hmmmStrange..
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 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] how to find a tablespace for the table?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
> > 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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] how to find a tablespace for the table?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] how to find a tablespace for the table?
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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
Re: [GENERAL] how to find a tablespace for the table?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find a tablespace for the table?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
