I proved that you can reclaim on disk space after a DROP COLUMN with toast
tables:

test=# create table toast_test(a text, b text);
CREATE TABLE
test=# insert into toast_test values (repeat('XXXXXXXXXX', 1000000),
repeat('XXXXXXXXXX', 1000000));
INSERT 246368 1
test=# insert into toast_test values (repeat('XXXXXXXXXX', 1000000),
repeat('XXXXXXXXXX', 1000000));
INSERT 246371 1

Gives:

-rw-------  1 chriskl  users     8192 Aug 30 15:46 246363
-rw-------  1 chriskl  users   475136 Aug 30 15:47 246365
-rw-------  1 chriskl  users    16384 Aug 30 15:46 246367

test=# alter table toast_test drop a;
ALTER TABLE
test=# update toast_test set b = b;
UPDATE 2

Gives:

-rw-------  1 chriskl  users     8192 Aug 30 15:46 246363
-rw-------  1 chriskl  users   475136 Aug 30 15:48 246365
-rw-------  1 chriskl  users    16384 Aug 30 15:46 246367

test=# vacuum full toast_test;
VACUUM
test=# checkpoint;
CHECKPOINT

Gives:

-rw-------  1 chriskl  users     8192 Aug 30 15:48 246363
-rw-------  1 chriskl  users   237568 Aug 30 15:48 246365
-rw-------  1 chriskl  users    16384 Aug 30 15:48 246367

Seems to halve the space used which is what you'd expect.

Chris


Fri, 30 Aug 2002 15:52:15 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
DROP COLUMN & TOASTED DATA