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 of the list. After examing the problem even more, I am convinced that it is an actual bug. The following problem is causing us enough trouble that ( and very sadly ) are considering changing database platforms. If you can provide any information on 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 index that 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. Below is 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, we were 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 vacuum process 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