Skip site navigation (1) Skip section navigation (2)

Tablespaces oddity?

From: Philip Yarra <philip(at)utiba(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Tablespaces oddity?
Date: 2006-03-28 06:12:26
Message-ID: 200603281612.26679.philip@utiba.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi folks after discussing this on IRC today (thanks G_SabinoMullane!), I'm 
still surprised by this behaviour on 8.1.3:

pyarra=# create TABLESPACE spctables location '/mnt/pg_tables/data';
CREATE TABLESPACE
pyarra=# create table foo(id int) tablespace spctables;
CREATE TABLE
pyarra=# \d foo
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Tablespace: "spctables"

So far, so good...

pyarra=# CREATE DATABASE spctest TABLESPACE spctables;
CREATE DATABASE
pyarra=# \c spctest;
You are now connected to database "spctest".
spctest=# create table foo(id int) tablespace spctables;
CREATE TABLE
spctest=# create table bar(id int);
CREATE TABLE
spctest=# \d foo
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |

spctest=# \d bar
      Table "public.bar"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |

I hoped that these last two tables would also be listed as being on spctables.

I think the issue is that pg_class.reltablespace = 0 where these objects are 
created on the default tablespace for this database. I can find out which 
tablespace the objects are really on with:

select relname, COALESCE(t.spcname,(select spcname from pg_tablespace where 
oid = (select dattablespace from pg_database where datname 
=current_database()))) as tablespace from pg_class c left join pg_tablespace 
t on (t.oid = c.reltablespace)

Someone else might be able to see a better way to write this query, but I 
think it would be good if \d could show this information, when you really 
want to know which tablespace an object is on. 

Note also that \l won't show you the tablespace for a DB, so you need to query 
pg_database to even know which is the default tablespace for a DB. It's not 
impossible, just harder than it needs to be, I reckon.

Any thoughts?

Regards, Philip.

-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-----------------
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.


Responses

pgsql-hackers by date

Next:From: lmyhoDate: 2006-03-28 08:17:47
Subject: Please help, pgAdmin3 on Debian!
Previous:From: Tom LaneDate: 2006-03-28 03:03:59
Subject: Re: [GENERAL] PANIC: heap_update_redo: no block

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group