Postgresql – Postgres Error: could not read block 167593 in file… SQL state: XX001

corruptionpostgresql

When creating a new column on a table, I'm getting the following error:

ERROR: could not read block 167593 in file "pg_tblspc/16384/PG_9.4_201409291/16386/157223695.1": read only 0 of 8192 bytes
SQL state: XX001

Obviously not good, as SQL state XX001 means corrupt data. I'm not too bothered as this is not critical data and can relatively easily be re-loaded from the source.

However, after searching for similar issues, all I can see from other people is this error for 'of relation base', not 'in file'. How is 'in file' different, and what does this mean? Is it more likely that I have a hardware issue?

Also, how can I check to see for certain that the data is corrupted?

Best Answer

However, after searching for similar issues, all I can see from other people is this error for 'of relation base', not 'in file'. How is 'in file' different, and what does this mean? Is it more likely that I have a hardware issue?

No, it's the same issue. The error message used to refer to the relation in older versions of PostgreSQL, but it was changed at some point (version 9.0, probably) to refer to the corresponding file instead.

I think the switch to the new message format happened here:

commit 23dc89d2c385e8e362cb4b8186b4d4ad02242ac0
Author: Heikki Linnakangas
Date: Wed Aug 5 18:01:54 2009 +0000 

Improve error messages in md.c. When a filesystem operation like open() or
fsync() fails, say "file" rather than "relation" when printing the filename.

This makes messages that display block numbers a bit confusing. For example,
in message 'could not read block 150000 of file "base/1234/5678.1"', 150000
is the block number from the beginning of the relation, ie. segment 0, not
150000th block within that segment. Per discussion, users aren't usually
interested in the exact location within the file, so we can live with that.

To ease constructing error messages, add FilePathName(File) function to
return the pathname of a virtual fd.
Related Question