Database Reference
In-Depth Information
You may also want to look at columns
spcowner
,
relowner
,
relacl
, and
spcacl
to
determine who owns what, and what they're allowed to do. The
relacl
and
spcacl
columns refer to the "access control list" that details the privileges available on those
objects. The
spcowner
and
relowner
columns record the owners of the tablespace
and tables/indexes, respectively.
How it works...
A tablespace is just a directory where we store PostgreSQL data files. We use symbolic links
from the data directory to the tablespace.
We exclude toast tables because they are always in the same tablespace as their parent
tables, though remember toast tables are always in a separate schema. You can exclude toast
tables using the
relkind
column, but that would still include the indexes on the toast tables.
Toast tables and toast indexes both start with
pg_toast
, so we can exclude those easily from
our queries.
The preceding query needs to be complex, because
pg_class
entry for an object will show
reltablespace
=
0
when an object is created in the database's default tablespace. So, if
you directly join
pg_class
and
pg_tablespace
, you end up losing rows.
Note that we can see a temporary object exists and which tablespace in which it is created,
even though we cannot refer to a temp object in another user's session.
There's more...
Some further notes on best practices follows.
A tablespace can contain objects from multiple databases, so it's possible to be in a position
where there are no objects visible in the current database. The tablespace just refuses to go
away, giving the following error:
ERROR: tablespace "old_tablespace" is not empty
You are strongly advised to make a separate tablespace for each database, to avoid
confusion. This can be especially confusing if you have the same schema names and table
names in the separate databases.
How to avoid this? If you just created a new tablespace directory, you might want to
create subdirectories within that for each database that needs space, and then make the
subdirectories into tablespaces instead.
You may also wish to consider giving each tablespace a specific owner, using the following query:
ALTER TABLESPACE new_tablespace OWNER TO eliza;
if that helps smooth administration.
