Thread: HELP! Cannot access table!!!!
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
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: DEBUG: server
process (pid 325) exited with exit code 2
DEBUG: terminating any other active server processes
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
DEBUG: all server processes terminated; reinitializing shared memory
and semaphores
Failed.
!# DEBUG: database system was interrupted at 2002-06-07 15:16:42 CEST
DEBUG: checkpoint record is at 0/2863DF58
DEBUG: redo record is at 0/2863DF58; undo record is at 0/0; shutdown FALSE
DEBUG: next transaction id: 1353431; next oid: 1443677
DEBUG: database system was not properly shut down; automatic recovery
in progress
DEBUG: ReadRecord: record with zero length at 0/2863DF98
DEBUG: redo is not required
DEBUG: database system is ready
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 ?
Does anyone of you know what's going on?
Thanx!
Wim
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
John Gray <jgray@azuli.co.uk> writes: > See > http://archives.postgresql.org/pgsql-general/2002-03/msg00196.php > for a previous reply by Tom Lane on this error message. That would still be my advice. >> 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 think this just indicates that $PGDATA was specified with a trailing slash in the name, eg you said postmaster -D /pgdata/ AFAIK the doubled slash will not matter on any Unix system. I did recently change the code to remove the redundant slash from the -D argument if given --- but that was strictly to forestall future questions like this one, not in the expectation that it would fix any problems. regards, tom lane