6.4.2 - VACUUM

To Whome It May Concern:

I tried to email the following question to the general and admin groups, but it was rejected because I am not a member
ofthe list. After examing the problem even more, I am convinced that it is an actual bug. The following problem is
causingus enough trouble that ( and very sadly ) are considering changing database platforms. If you can provide any
informationon how to fix this, it would be greatly appreciated! 

Sincerely,
Cullin J Wible

------------------

We are using version 6.4.2 on RedHat Linux 5.1. We are using the Perl Interface for most applications. We have a search
indexthat we build from many different data sources. It is as follows: 

Table    = search
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | text not null                    |   var |
| group_id                         | text                             |   var |
| date_created                     | date                             |     4 |
| time_created                     | time                             |     8 |
| word                             | text                             |   var |
| count                            | int4                             |     4 |
| sort                             | text                             |   var |
| type                             | char()                           |     1 |
| type_data                        | text                             |   var |
+----------------------------------+----------------------------------+-------+
Indices:  search_pkey
          search_type_key
          search_word_key

From time to time, we must empty the table and do a full rebuild. We do this as follows:

=> delete from search;
=> vacuum verbose analyze search;

However the vacuum normally hangs. The process state changes to 'Resident' and it has to be manually killed from a
shell.The only way to fix the problem is to drop and re create the search table. The table size is only about 2.5MB.
Belowis the vacuum output. Any suggestions would be greatly appreciated. 

After doing some more experimenting, we tried adding the new records, using a different group_id from the old records,
wewere then able to delete the old records, leaving only the new. We still have the same problem.  

Furthermore, when the vacuum process hangs, all of the other incoming requests stack up and cannot continue until the
vacuumprocess is killed. We then have to remove the pg_vlock file and then try again.  

NOTICE:  --Relation search--
NOTICE:  Pages 291: Changed 160, Reapped 132, Empty 0, New 0; Tup 6644: Vac 5532, Crash 0, UnUsed 0, MinLen 52, MaxLen
222;Re-using: Free/Avail. Space 1052572/ 
1052572; EndEmpty/Avail. Pages 0/132. Elapsed 0/0 sec.
NOTICE:  Index search_word_key: Pages 35; Tuples 6644: Deleted 0. Elapsed 0/0 sec.
NOTICE:  Index search_type_key: Pages 71; Tuples 6644: Deleted 0. Elapsed 0/0 sec.

Please include me in any replies as I am not currently a member of the mailing lists.

Thanks,
Cullin J Wible