Problem with TOAST tables when removing TOASTable column

From: Wojciech Strzałka <wstrzalka(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with TOAST tables when removing TOASTable column
Date: 2008-05-14 14:45:53
Message-ID: 3110191199.20080514164553@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


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' and relname = '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' and relname = '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' and relname = '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' and relname = '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
Wojtek Strzalka

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2008-05-14 15:11:21 Re: Problem with TOAST tables when removing TOASTable column
Previous Message nicoanto 2008-05-14 14:40:18 BUG #4167: When generating UUID using UUID-OSSP module, UUIDs are not unique on Windows