Lists: | pgsql-bugs |
---|
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 |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Wojciech Strzałka <wstrzalka(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Problem with TOAST tables when removing TOASTable column |
Date: | 2008-05-14 15:11:21 |
Message-ID: | 19781.1210777881@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
=?iso-8859-2?Q?Wojciech_Strza=B3ka?= <wstrzalka(at)gmail(dot)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
From: | Wojciech Strzałka <wstrzalka(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Problem with TOAST tables when removing TOASTable column |
Date: | 2008-05-14 15:21:05 |
Message-ID: | 323199382.20080514172105@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Wojciech Strzałka <wstrzalka(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Problem with TOAST tables when removing TOASTable column |
Date: | 2008-05-14 16:22:29 |
Message-ID: | 20859.1210782149@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
=?iso-8859-2?Q?Wojciech_Strza=B3ka?= <wstrzalka(at)gmail(dot)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