Thread: Problem with TOAST tables when removing TOASTable column

I've found some strange behavoiur of TOAST'able tables.

1. Lets create table with toastable column

CREATE table toastable (
x int ,
y text
);

2. Check toast size - as the table is empty it's size  0 - OK

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r'
andrelname = 'toastable'); 

3. Insert some large record into toastable

INSERT into toastable values (1, pg_read_file('r.txt', 0, 100000));

4. Again check toast size - it's > 0 now - and it's OK as there are some data

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r'
andrelname = 'toastable'); 

5. Drop the only toastable column

ALTER TABLE toastable DROP COLUMN y;

6. To be sure - vacuum

VACUUM FULL;

7. Check toast size. OH NO - IT"S THE SAME AS IN POINT 4 - WHERE IS MY STORAGE??

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r'
andrelname = 'toastable'); 

8. Make some MVCC noise.

UPDATE toastable SET x=x;

9. And vacuum

VACUUM FULL;

10. Here it is - my storage is back (toast size 0)

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r'
andrelname = 'toastable'); 


In my opinion the fact that dropping column doesn't release it's toastable resources is a bug.
I think it would be good if the toast table would be deleted also in such a case (now I have table with
no toastable columns so I don't need it anymore), but that's not so
important as releasing the free space.

                                                   Best regards
WojtekStrzalka 

Wojciech Strzałka <wstrzalka@gmail.com> writes:
> In my opinion the fact that dropping column doesn't release it's toastable resources is a bug.

To make that happen would require (at least) a full table scan.  I think
most people are more interested in DROP COLUMN being a cheap operation
than in having the space be reclaimed quickly.

For a comparison point: large field values that don't happen to get
toasted don't vanish immediately, either.
        regards, tom lane

> To make that happen would require (at least) a full table scan.  I think
> most people are more interested in DROP COLUMN being a cheap operation
> than in having the space be reclaimed quickly.

> For a comparison point: large field values that don't happen to get
> toasted don't vanish immediately, either.

I agree DROP COLUMN should be cheap and I don't really expect it to happend immediately,
but shouldn't VACUUM FULL clean it up?

                                   Wojtek Strzalka

Wojciech Strzałka <wstrzalka@gmail.com> writes:
>> To make that happen would require (at least) a full table scan.  I think
>> most people are more interested in DROP COLUMN being a cheap operation
>> than in having the space be reclaimed quickly.

>> For a comparison point: large field values that don't happen to get
>> toasted don't vanish immediately, either.

> I agree DROP COLUMN should be cheap and I don't really expect it to happend immediately,
> but shouldn't VACUUM FULL clean it up?

No, changing the content of existing tuples is outside VACUUM's purview.

FWIW, I believe that a CLUSTER would clean it up, and would likely be
faster than a VACUUM FULL anyway.
        regards, tom lane