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.

Search Discussions

  • Tom Lane at Mar 28, 2006 at 3:36 pm

    Philip Yarra writes:
    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.
    If \d doesn't say anything then the table is in the database's default
    tablespace. I see nothing wrong with that, and I do object to
    cluttering \d output with information that will be of no interest to
    people not using tablespaces.
    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.
    I wouldn't object to adding default tablespace to \l output, or maybe \l+.

    regards, tom lane
  • Philip Yarra at Mar 28, 2006 at 10:47 pm

    On Wed, 29 Mar 2006 01:36 am, Tom Lane wrote:
    Philip Yarra <philip@utiba.com> writes:
    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.
    If \d doesn't say anything then the table is in the database's default
    tablespace. I see nothing wrong with that, and I do object to
    cluttering \d output with information that will be of no interest to
    people not using tablespaces.
    OK, how about on \d+, if the object is not on pg_default or pg_global, print
    the tablespace that this object is on? That way, people not using tablespaces
    won't ever see it.
    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.
    I wouldn't object to adding default tablespace to \l output, or maybe \l+.
    OK, not fussed which one it's on, so long as it's there - this should do it
    for \l+

    SELECT d.datname as "Name",
    r.rolname as "Owner",
    pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
    pg_catalog.obj_description(d.oid, 'pg_database') as "Description",
    t.spcname as "Tablespace"
    FROM pg_catalog.pg_database d
    LEFT JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
    LEFT JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid;

    On a related note: is there a simple way to show all objects on a given
    tablespace? If not, would other people also see this as useful?

    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.
  • Philip Yarra at Mar 30, 2006 at 12:46 am

    On Wed, 29 Mar 2006 08:46 am, Philip Yarra wrote:
    OK, how about on \d+, if the object is not on pg_default or pg_global,
    print the tablespace that this object is on? That way, people not using
    tablespaces won't ever see it.
    Tom, does this answer your objection? If so, I'll produce a patch for it.

    Regards, Philip.
  • Philip Yarra at Mar 30, 2006 at 12:52 am

    On Wed, 29 Mar 2006 08:46 am, Philip Yarra wrote:
    OK, how about on \d+, if the object is not on pg_default or pg_global,
    print the tablespace that this object is on? That way, people not using
    tablespaces won't ever see it.
    Tom, does this answer your objection? If so, I'll produce a patch for it.

    Regards, Philip.

    PS: sorry about duplicate post, accidentally sent from other email account.

    --

    "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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 28, '06 at 6:12a
activeMar 30, '06 at 12:52a
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase