Re: Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 4:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> > There is some funny results:
>
> > hh=# VACUUM verbose agency_statistics_old;
> > INFO: vacuuming "public.agency_statistics_old"
> > INFO: index "agency_statistics_pkey" now contains 0 row versions in 605
> > pages
> > DETAIL: 0 index row versions were removed.
>
> Wow. That seems to blow my theory to small pieces. If the index
> contains no entries then it shouldn't be causing any uniqueness check
> probes. But at the same time, if the index is empty then how come
> pgstatindex showed avg_leaf_density = 0.45 ?
>
> > May be I should use pageinspect addon to see an actual index pages
> content?
>
> That or pg_filedump would be interesting. But your experiments with
> adding data from the other table will probably have produced some new
> index entries, which will confuse the situation. Did you save a
> physical copy of the index before that?
>
> Another idea is to attach to the backend with gdb, set a breakpoint at
> errfinish, and get a stack trace from the point of the "could not read
> block" error. That would show definitively if this is coming from a
> uniqueness check or something else entirely.
>
> regards, tom lane
>

Yes I had saved a physical copy of the file before start playing with it.

Unfortunately that is a production server with no gcc and gdb available so
pg_filedump or gdb yet (but I going to work on it).

While I waiting for gdb/gcc on that server I had built pg_filedump on the
development server using same postgresql version and created pg_filedump of
the index file.
It can be downloaded there:
http://maximboguk.com/static/etc/agency_statistics_pkey.pg_filedump.gz

I have not enough knowledge of the b-tree index structure to extract any
useful information from that file.

Kind Regards,
Maksym