Re: Subject: bool / vacuum full bug followup

On Thu, May 02, 2002 at 04:30:15PM -0600, Scott Marlowe wrote:
> OK, this is just beyond weird. I swear to GOD that my table size was
> growing. I'm on a workstation that doesn't accept connections from
> anybody but me.
>
> I was running 7.2 for weeks.
> I created a simple table:
> create table test (buf text, yn bool);
> I run an explain:
> explain select * from test where yn=true;
> I run an analyze:
> analyze;
> I update the table:
> update test set yn=true where yn=true;
> update test set yn=true where yn=true;
> update test set yn=true where yn=true;
> I do a REGULAR vacuum:
> vacuum;
>
> And after that, the table started growing after all vacuum fulls. I can't
> reproduce it now. I blew away the table, recreated it, and now it doesn't
> do that. I blew away the database, and installed 7.2.1 and it won't do
> it. I reinstalled 7.2 and blew away the database and it still won't do
> it.
>
> So, I don't know why it WAS doing it, but it isn't now. And of course,
> it's my personal workstation, so no log files. :-( I'll see if I can
> reproduce this though, as I've seen similar problems pop up once in the
> past with a seldom vacuumed database that reached a point where it stopped
> freeing dead tuples. Very bothersome.

Well, if the table has an associated TOAST table, it is possible
that it will suddenly start growing out of control, and nothing you
can do with VACUUM will help. The TOAST system has a free space map
(FSM) with some default number of pages it can track. If you exceed
this number, it will become completely unable to reclaim space. If
this happens you will have to dump and reload the database or take
other drastic action.

Check the VACUUM VERBOSE output to see the pages in your TOAST
tables.

-jwb