"Bjoern Metzdorf" <bm@turtle-entertainment.de> writes:
> If I do the same "select where ctid"-test on all ctid's of neighboured
> tuples ("select ctid from players order by id limit 20,81270;") it is the
> same (works).
Um, the "order by id" is not accomplishing much here except to confuse
the issue. Tuples adjacent in id order are not necessarily anywhere
near each other physically. Why don't you just do
select ctid,* from players limit N
Since this is a heapscan it will return the results in ctid order.
An offset isn't going to do much except confuse the issue either,
because the tuples skipped by the offset are still fetched and might
still provoke a crash.
Don't forget also that you *must* select the columns. "select ctid from..."
alone will probably not crash no matter how badly the body of the tuple
is mangled, 'cause it ain't gonna look at the body.
What I'd do to locate the bad tuple(s) is
1. Home in on an approximate value of N that separates where
select * from players limit N
crashes vs. where it doesn't crash.
2. Get the ctid's of the tuples around this point, eg
select ctid from players offset N-100 limit 200
As indicated, you can probably retrieve the ctids even if the tuples
are busted.
3. Probe these tuples individually using
select * from players where ctid = '(m,n)';
Or you could just set up a script that does
select ctid from players
and then tries to retrieve each one individually by ctid. A tad tedious
but easily automated. If you suspect there might be corruption in
several different areas of the table then you'll end up doing this anyway.
regards, tom lane
