Re: HELP! Cannot access table!!!!

On Fri, 2002-06-07 at 15:20, Wim wrote:
> Hello,
>
> I have a big problem when I try to access a table.
> When I do:
>
> ux5_billing=# SELECT COUNT(*) FROM billing;
>
> I get the output:
>
> FATAL 2: open of /pgdata//pg_clog/0FF0 failed: No such file or directory
> FATAL 2: open of /pgdata//pg_clog/0FF0 failed: No such file or directory

Unfortunately, this message probably indicates some data corruption in
the billing table. Whether this is caused by a hardware fault or a
software bug is not possible to determine without further investigation.

If any of the following seems too involved, just ask for further help.

Firstly, does the /pgdata/pg_clog directory contain any other numbered
files? I assume that the numbers (in hexadecimal form) don't go up as
far as 0FF0?

If this is the case, it suggests that some of the rows in billing have
become corrupted. For example, you should find that if you add a where
clause to your count, that you only get a crash when certain rows are
selected.

Technical explanation:
The details of this problem are that the system columns xmin and xmax,
(which contain the creation and deletion transaction IDs of a row) are
referring to non-existent transactions - the pg_clog files contain
records of which transactions have been committed or aborted, and when
an illegal transaction number is looked up, this causes the fatal error.

See
http://archives.postgresql.org/pgsql-general/2002-03/msg00196.php

for a previous reply by Tom Lane on this error message.

>
> I Use the latest version 7.2.1, running on a HP-9000 with Debian.
> Indeed, the file /pgdata//pg_clog/0FF0 doesn't exist!
>
> Is it normal that there are 2 backslashes in /pgdata//pg_clog/0FF0 ?
>
>

I don't think this should matter (if there was a general problem with
your pg_clog directory, you'd not be able to do anything very much -I
take it you can work with tables other than billing?)

Tom's suggestion in the above-mentioned message is to attempt to dump
(using pg_dump) and reload just the affected table if you want to get
going again (and if the dump doesn't work, and you have a suitable
backup, to drop the "billing" table and reload from your backup).

Other people may be interested in looking at the table file concerned in
order to determine whether it is a bug or a hardware failure. Is this a
production system i.e. are you keen to get it running again ASAP?

Hope this helps

Regards

John

--
John Gray
Azuli IT
www.azuli.co.uk