Re: What could cause CREATE TEMP... "could not read block" error?

Chris Richards wrote:
> Adrian is correct. This worked by itself whereas using it in the creation of the temporary table
> failed.
> mdb-> SELECT pq.* FROM policyqueue AS pq
> mdb-> JOIN seed_progress AS sp ON pq.id <http://pq.id/> =sp.polidx;
>
> I checked the query Albe suggested; there were two `relfilenode`s (11936 and 11937) that exhibited the
> error. Respectively, they were pg_depend_depender_index and pg_depend_reference_index.
>
> Unfortunately, I didn't disable the nightly processes and something must  have(?) fixed the glitch; at
> midnight GMT the query ran successfully. Ugh.
>
> If it crops up again, I have some tools to try and capture data immediately, and the suggested REINDEX
> since both appear to be indices.

These indexes are both on the system table "pg_depend".

That explains why the query ran without problems but the CREATE TABLE didn't:
Creating an object creates dependencies, and you have data corruption in the
system table that tracks dependencies.

I would be pretty worried in your place if I had a corrupted catalog, even if
it seems to have "fixed itself".  There might be other tables with corruption.

I would do two things:
1) Wait for a moment when there is little database traffic and
   run "REINDEX TABLE pg_depend;" to rebuild these indexes.

2) As soon as you can schedule some downtime, pg_dumpall the database
   cluster, stop the server, throw away the database cluster, create a new
   one with "initdb" and restore the dump into that.
   That will get rid of any lurking data corruption.
   Watch out for error messages during the pg_dumpall!


The main question is of course how you got the corruption in the first place.
Are you running the latest minor release for your PostgreSQL version?
Were there any crashes recently?
Do you have "fsync = on"?
Did you make sure that you have a reliable storage system?

Yours,
Laurenz Albe