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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
