Re: ALTER TABLE SET TABLESPACE and pg_toast - Mailing list pgsql-performance

From Tom Lane
Subject Re: ALTER TABLE SET TABLESPACE and pg_toast
Date
Msg-id 26244.1134746601@sss.pgh.pa.us
Whole thread Raw
In response to ALTER TABLE SET TABLESPACE and pg_toast  ("PostgreSQL" <martin@portant.com>)
List pgsql-performance
"PostgreSQL" <martin@portant.com> writes:
> We're storing tif images in a table as bytea.  We were running low on our
> primary space and moved several tables, including the one with the images,
> to a second tablespace using ALTER TABLE SET TABLESPACE.
> This moved quite cleaned out quite a bit of space on the original
> tablespace, but not as much as it should have.  It does not appear that the
> corresponding pg_toast tables were moved.

I think you're mistaken; at least, the SET TABLESPACE code certainly
intends to move a table's toast table and index along with the table.
What's your evidence for saying it didn't happen, and which PG version
are you using exactly?

> 2) Also, is there a good way to determine which pg_toast tables are
> associated with any particular table and column?

pg_class.reltoastrelid and reltoastidxid.  See
http://www.postgresql.org/docs/8.1/static/storage.html
http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html

            regards, tom lane

pgsql-performance by date:

Previous
From: Tomasz Rybak
Date:
Subject: Re: Overriding the optimizer
Next
From: "Craig A. James"
Date:
Subject: Re: Overriding the optimizer