Обсуждение: Bug #782: clog files disappear after 'vacuum;'

Steven Wilton (steven.wilton@team.eftel.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
trafficdatabase. 

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
followingoutput: 

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
oneof 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

pgsql-bugs@postgresql.org writes:
> We are having a problem with our pg_clog files disappearing after a
> vacuum command is run.

VACUUM is supposed to remove no-longer-needed pg_clog files; space
reclamation is what it's all about, after all.  Your problem is more
correctly thought of as "there shouldn't be any more references in my
database to old transaction numbers, once VACUUM is done".

We just today identified and fixed a problem that might allow old
transaction references to escape, but that bug could only be tickled if
a database crash occurs shortly after a VACUUM FULL.  Unless you are
crashing your server on a routine basis, it seems like that doesn't
describe your problem.

Can you provide a self-contained script to provoke the problem you are
seeing?  Or let someone into your system to examine things in detail?

            regards, tom lane