Thread: Re: [HACKERS] Backend crashes (6.5.2 linux)

Hi

Thanks to tom and vadim for the ideas so far. Here is my responses to
these:

Tom wrote:
> Hmm.  That error is coming out of the btree index code.  Vadim knows
> that code better than anyone else, so he might have something to say
> here, but my past-midnight recollection is that we've seen that error
> being triggered when there are oversize entries in the index (where
> "oversize" = "more than half a disk page").  It's a bug, for sure,
> but what you probably want right now is a workaround.  Do you have any
> entries in indexed columns that are over 4K, and can you get rid of them?

Okee, Ive looked at all of the text lengths (I didnt bother with the ints)
and got the following

bestadssearch=> select length(url)+length(hostname)+length(title)+length(brief)+length(lowerurl)  as length from
search_urlwhere title notnull and brief notnull order by length desc;
 

length
------  841  827  826  825
...

So my maximum length is probably under 1K

> Huh?  PQgetResult does not call pgresStatus ... not least because the
> latter is an array, not a function.  Your gdb is lying to you.  Maybe
> you have a problem with gdb looking at a different version of the
> library than what's actually executing?

Nope, I just checked, I only have one version of the libraries.

Vadim Wrote:

> This FATAL means that index is broken (some prev insertion
> was interrupted by elog(ERROR) or backend crash) - try to rebuild...
> WAL should fix this bug.
>
> Vadim

Thats curious cos look at this explain...

bestadssearch=> explain update search_url set stale=941424005 where lowerurl='http://criswell.bizland.com';
NOTICE:  QUERY PLAN:

Seq Scan on search_url  (cost=1546.06 rows=2 width=122)

EXPLAIN

That does a seq scan not an index scan.

This came to light when I realised my initialisation script for the database
added a bogus index (oops). However, that is probably not a good sign if
the seq scan fails for this reason.

The only index associated with this table is the one created with a serial
type.

I am going to rebuild the table (dump and reload) but before I do,
does anyone want me to try anything on it to see if you can get any
more information you may need for debugging? If so, let me know asap
{:-)
                    ~Michael

Michael Simms <grim@argh.demon.co.uk> writes:
> Vadim Wrote:
>> This FATAL means that index is broken (some prev insertion
>> was interrupted by elog(ERROR) or backend crash) - try to rebuild...

> Thats curious cos look at this explain...

> bestadssearch=> explain update search_url set stale=941424005 where lowerurl='http://criswell.bizland.com';
> NOTICE:  QUERY PLAN:

> Seq Scan on search_url  (cost=1546.06 rows=2 width=122)

> That does a seq scan not an index scan.

No, the elog message is coming out of btree index *update*, not
btree scan.  Since you're doing an update, new index entries have
to be made for the tuple being updated, regardless of what kind
of scan was used to find it.  And the message comes out because
the attempt to insert an index entry is finding that the index is
already corrupt.

Vadim's advice is probably the best: drop and recreate the index(es)
on that table.  You shouldn't need to dump the table itself, unless
there's more going on than is apparent from the info so far.

> I am going to rebuild the table (dump and reload) but before I do,
> does anyone want me to try anything on it to see if you can get any
> more information you may need for debugging? If so, let me know asap

If you can reproduce the sequence of events that led to the index
becoming corrupted, that'd be *really* useful debugging info.  But
it's probably too late to reconstruct anything very helpful from
the current state of the index.
        regards, tom lane