Hi Tom,
> One thing to watch out for is that I believe "select ... limit" fetches
> one more tuple than it actually needs to return. Normally this doesn't
> matter but with a crashing issue it might. I would recommend using
Good to know. In this case tuple N+1 is in the very same page as N.
> select ctid,* from table where ctid = '(page,item)'
>
> to make absolutely sure that you know which ctid is the bad tuple (if no
> crash, you got the wrong one), and then delete by ctid to get rid of it.
This is something I don't understand:
A "select ctid,* from table order by id limit 81273;" works,
a "select ctid,* from players order by id limit 81274;" crashes,
a "select ctid,* from players order by id limit 1,81274;" crashes,
a "select ctid,oid,* from players order by id limit 1,81274;" works (gives
back 5864,12 for ctid),
and a "select ctid,* from players where ctid = '(5864,12)';" works also.
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). (btw, if I do "select ctid from players order by id limit
20,81270;", I get the desired output BUT the postmaster crashes (status
139)).
The only way to reproduce the crash is by using limit. I'm doing these tests
now again without the "order by id".
> It's entirely possible that several tuples on the same page are all
> corrupted, which is the main reason why ctid is so helpful in this
> situation --- you can see directly whether the tuples are in the same
> page or not. But I wouldn't delete stuff at random. If you can select
> it without a crash then it's OK.
>
> I would only consider using dd if it looked like the page header is
> broken (symptom: page appears to contain many more tuples than could
> possibly fit, and all but the first few are obviously completely
> garbage).
Greetings,
Bjoern
