Bug #782: clog files disappear after 'vacuum;'

Steven Wilton (steven(dot)wilton(at)team(dot)eftel(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
clog files disappear after 'vacuum;'

Long Description
We are having a problem with our pg_clog files disappearing after a vacuum command is run. We have a database called "traffic" which has tables created every half hour, and after each table is older than 8 hours, we drop them. The 'postgres' user does not have any access to the traffic database, and the 'collector' user only has access to the traffic database.

If either the 'collector' or 'postgres' user runs a vacuum command, most of the files in the pg_clog directory disappear. After the vacuum, if you try to drop certain tables, you get the following error:

traffic=> drop table raw_traf_573808;
FATAL 2: open of /var/lib/postgres/data/pg_clog/0005 failed: No such file or directory
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

If I disconnect from postgres, recreate the pg_clog file (from /dev/zero), I get the following error:

traffic=> drop table raw_traf_573808;
ERROR: DeleteTypeTuple: type "raw_traf_573808" does not exist

traffic=> \d raw_traf_573808
FATAL 2: open of /var/lib/postgres/data/pg_clog/0000 failed: No such file or directory
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

At this stage, I need to create all clog files (ie from 0000 to 0005). Once I create the clog files, I get the following output:

traffic=> \d raw_traf_573808
Table "raw_traf_573808"
Column | Type | Modifiers
-----------------------+----------+----------------------
start_time | integer | not null
stop_time | integer | not null
source_ip | integer | not null
dest_ip | integer | not null
source_port | smallint | not null
dest_port | smallint | not null
router_ip | integer | not null
input_interface_type | smallint | not null
output_interface_type | smallint | not null
bytes | integer | not null
swap | boolean | not null default '0'
flags | smallint | not null

traffic=> drop table raw_traf_573808;
ERROR: DeleteTypeTuple: type "raw_traf_573808" does not exist

If I delete the postgres installation, and re-create all tables, the system works fine for a while (ie the tables get created, dropped without a problem). After the postgres system has been running for a while, we will eventually lose one of the clog files, and end up with tables in the database that we can not drop.

We are running postgres 7.2.1 under a debian 3.0(woody) linux system with a 2.4.18 kernel.

any help would be appreaciated.

thanks

Steven

Sample Code

No file was uploaded with this report