I get the following error when vacuuming a db or inserting
a big value in a column of a toastable datatype (GEOMETRY).

	ERROR:  Index pg_toast_8443892_index is not a btree

My last action has been killing a psql that was getting
mad about receiving too much input and beeping as hell
(readline issue ?).

After that, I put the insert query I was trying to feed to
psql in a file and sourced it... Bump! that error appeared.

	psql:B:477: ERROR:  Index pg_toast_8443892_index is not a btree

Line 477 is EOF...

Vacuum does not solve this (as you can see in the first error message)

Do you have any hint about how to fix this ? 


TIA

--strk;

Mon, 8 Dec 2003 10:40:09 +0100
strk <strk@keybit.net>
ERROR:  Index pg_toast_8443892_index is not a btree
strk wrote:

> I get the following error when vacuuming a db or inserting
> a big value in a column of a toastable datatype (GEOMETRY).
> 
> 	ERROR:  Index pg_toast_8443892_index is not a btree
> 
> My last action has been killing a psql that was getting
> mad about receiving too much input and beeping as hell
> (readline issue ?).

You must have killed a lot more than your psql frontend to get that as a 
result.

> 
> After that, I put the insert query I was trying to feed to
> psql in a file and sourced it... Bump! that error appeared.
> 
> 	psql:B:477: ERROR:  Index pg_toast_8443892_index is not a btree
> 
> Line 477 is EOF...
> 
> Vacuum does not solve this (as you can see in the first error message)
> 
> Do you have any hint about how to fix this ? 

Try "reindex table <tablename>". If you really only lost that btree 
index, that should do.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Mon, 08 Dec 2003 09:38:45 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: ERROR:  Index pg_toast_8443892_index is not a btree
strk wrote:

> JanWieck wrote:
>> strk wrote:
>> 
>> > I get the following error when vacuuming a db or inserting
>> > a big value in a column of a toastable datatype (GEOMETRY).
>> > 
>> > 	ERROR:  Index pg_toast_8443892_index is not a btree
>> > 
>> > My last action has been killing a psql that was getting
>> > mad about receiving too much input and beeping as hell
>> > (readline issue ?).
>> 
>> You must have killed a lot more than your psql frontend to get that as a 
>> result.
> 
> really... I hit ^C at the psql terminal and
> kill -9 <psql_pid>
> 
> The only other reason I can thing about is the data type text
> input function screwing pg internal pointers...

You mean the text input function did stomp over shared memory of the 
buffer cache? That would be the first time I hear of this.

Could you please do

select oid as datoid from pg_database where datname = '<dbname>';
select A.relfilenode from pg_class A, pg_class B, pg_class C
     where C.relname = '<tablename>'
     and B.oid = C.reltoastrelid
     and A.oid = B.reltoastidxid;

With that information, give us an

     ls -l $PGDATA/base/<datoid>/<relfilenode>

This file is the toast tables index.

> 
>> 
>> > 
>> > After that, I put the insert query I was trying to feed to
>> > psql in a file and sourced it... Bump! that error appeared.
>> > 
>> > 	psql:B:477: ERROR:  Index pg_toast_8443892_index is not a btree
>> > 
>> > Line 477 is EOF...
>> > 
>> > Vacuum does not solve this (as you can see in the first error message)
>> > 
>> > Do you have any hint about how to fix this ? 
>> 
>> Try "reindex table <tablename>". If you really only lost that btree 
>> index, that should do.
> 
> gis=# reindex table test; -- this is the table I was trying to insert into
> WARNING:  table "test" wasn't reindexed

Is there more information about why it wasn't reindexed in the 
postmaster log?

> REINDEX
> gis=# reindex table pg_toast_8443892; -- this was an assuption I made
> ERROR:  Relation "pg_toast_8443892" does not exist
> gis=# vacuum;
> ERROR:  Index pg_toast_8443892_index is not a btree
> gis=#
> 
> Where could this pg_toast_8443892_index reference be found ?

As you might guess from the second select above ...


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Mon, 08 Dec 2003 11:03:09 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: ERROR:  Index pg_toast_8443892_index is not a btree
JanWieck wrote:
> strk wrote:
> 
> > I get the following error when vacuuming a db or inserting
> > a big value in a column of a toastable datatype (GEOMETRY).
> > 
> > 	ERROR:  Index pg_toast_8443892_index is not a btree
> > 
> > My last action has been killing a psql that was getting
> > mad about receiving too much input and beeping as hell
> > (readline issue ?).
> 
> You must have killed a lot more than your psql frontend to get that as a 
> result.

really... I hit ^C at the psql terminal and
kill -9 <psql_pid>

The only other reason I can thing about is the data type text
input function screwing pg internal pointers...

> 
> > 
> > After that, I put the insert query I was trying to feed to
> > psql in a file and sourced it... Bump! that error appeared.
> > 
> > 	psql:B:477: ERROR:  Index pg_toast_8443892_index is not a btree
> > 
> > Line 477 is EOF...
> > 
> > Vacuum does not solve this (as you can see in the first error message)
> > 
> > Do you have any hint about how to fix this ? 
> 
> Try "reindex table <tablename>". If you really only lost that btree 
> index, that should do.

gis=# reindex table test; -- this is the table I was trying to insert into
WARNING:  table "test" wasn't reindexed
REINDEX
gis=# reindex table pg_toast_8443892; -- this was an assuption I made
ERROR:  Relation "pg_toast_8443892" does not exist
gis=# vacuum;
ERROR:  Index pg_toast_8443892_index is not a btree
gis=#

Where could this pg_toast_8443892_index reference be found ?

--strk;

Mon, 8 Dec 2003 17:23:03 +0100
strk <strk@keybit.net>
Re: ERROR:  Index pg_toast_8443892_index is not a btree
strk <strk@keybit.net> writes:
> gis=# reindex table pg_toast_8443892; -- this was an assuption I made
> ERROR:  Relation "pg_toast_8443892" does not exist

If it's 7.3 or later you need to say

reindex table pg_toast.pg_toast_8443892;

			regards, tom lane

Mon, 08 Dec 2003 12:13:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ERROR: Index pg_toast_8443892_index is not a btree 
tgl wrote:
> strk <strk@keybit.net> writes:
> > gis=# reindex table pg_toast_8443892; -- this was an assuption I made
> > ERROR:  Relation "pg_toast_8443892" does not exist
> 
> If it's 7.3 or later you need to say
> 
> reindex table pg_toast.pg_toast_8443892;
> 
> 			regards, tom lane

It worked!
Thank you very much.
--strk;

Mon, 8 Dec 2003 18:55:47 +0100
strk <strk@keybit.net>
Re: ERROR: Index pg_toast_8443892_index is not a btree
>>>I get the following error when vacuuming a db or inserting
>>>a big value in a column of a toastable datatype (GEOMETRY).
>>>
>>>	ERROR:  Index pg_toast_8443892_index is not a btree
>>>
>>>My last action has been killing a psql that was getting
>>>mad about receiving too much input and beeping as hell
>>>(readline issue ?).

Is there anything stopping us going through the code and finding all 
ereports that can be fixed by a REINDEX, and issue a HINT with all of 
them saying that they should REINDEX the broken index?

That would seem to me to be really helpful for people.

Chris


Tue, 09 Dec 2003 09:50:50 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ERROR:  Index pg_toast_8443892_index is not a btree
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Is there anything stopping us going through the code and finding all 
> ereports that can be fixed by a REINDEX, and issue a HINT with all of 
> them saying that they should REINDEX the broken index?

How would you know which ones correspond to REINDEX-fixable conditions?

I generally dislike hints that tell people their first action should be
to destroy the evidence, anyway.  If they had an index problem, REINDEX
will guarantee there is no chance of learning anything about it.

			regards, tom lane

Mon, 08 Dec 2003 21:05:37 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ERROR: Index pg_toast_8443892_index is not a btree 
Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Is there anything stopping us going through the code and finding all 
>> ereports that can be fixed by a REINDEX, and issue a HINT with all of 
>> them saying that they should REINDEX the broken index?
> 
> How would you know which ones correspond to REINDEX-fixable conditions?
> 
> I generally dislike hints that tell people their first action should be
> to destroy the evidence, anyway.  If they had an index problem, REINDEX
> will guarantee there is no chance of learning anything about it.

I couldn't agree more. Look at this very instance. He now found the 
right reindex command and the corrupted file is gone. We don't have the 
slightest clue what happened to that file. Was it truncated? Did some 
other process scribble around in the shared memory? How do you tell now?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Tue, 09 Dec 2003 06:35:46 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: ERROR: Index pg_toast_8443892_index is not a btree
Jan Wieck wrote:
> Tom Lane wrote:
> > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> >> Is there anything stopping us going through the code and finding all 
> >> ereports that can be fixed by a REINDEX, and issue a HINT with all of 
> >> them saying that they should REINDEX the broken index?
> > 
> > How would you know which ones correspond to REINDEX-fixable conditions?
> > 
> > I generally dislike hints that tell people their first action should be
> > to destroy the evidence, anyway.  If they had an index problem, REINDEX
> > will guarantee there is no chance of learning anything about it.
> 
> I couldn't agree more. Look at this very instance. He now found the 
> right reindex command and the corrupted file is gone. We don't have the 
> slightest clue what happened to that file. Was it truncated? Did some 
> other process scribble around in the shared memory? How do you tell now?

Does he have bad RAM?  Good point.  Should we give a hint to report it
to us?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 9 Dec 2003 18:50:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ERROR: Index pg_toast_8443892_index is not a btree
Christopher Kings-Lynne wrote:

>> I couldn't agree more. Look at this very instance. He now found the 
>> right reindex command and the corrupted file is gone. We don't have the 
>> slightest clue what happened to that file. Was it truncated? Did some 
>> other process scribble around in the shared memory? How do you tell now?
> 
> The end user just could not care less.  They want their machine running 
> again as soon as is humanly possible without going through a back and 
> forth process of subscribing to some lists they don't care about, etc.

I know, that's (unfortunately) true. Although it's not very farsighted 
because better bug reports usually lead to better software in the next 
release.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Tue, 09 Dec 2003 20:19:39 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: ERROR: Index pg_toast_8443892_index is not a btree
> I couldn't agree more. Look at this very instance. He now found the 
> right reindex command and the corrupted file is gone. We don't have the 
> slightest clue what happened to that file. Was it truncated? Did some 
> other process scribble around in the shared memory? How do you tell now?

The end user just could not care less.  They want their machine running 
again as soon as is humanly possible without going through a back and 
forth process of subscribing to some lists they don't care about, etc.

Chris


Wed, 10 Dec 2003 10:07:33 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ERROR: Index pg_toast_8443892_index is not a btree
I agree about keeping it simple for the users. Anyway if that 
shows up a bad problems with either the implementation or the
operating system of the users it would be nice to know how 
to inspect it further. In my case this could also help
debugging a postgres extension (postgis) which is involved in
text->internal conversion and is showing heap corruption problems.

The question now is: what does that message mean ? Did a routine
try to create an index and left its work before finishing it ?

--strk;

JanWieck wrote:
> Christopher Kings-Lynne wrote:
> 
> >> I couldn't agree more. Look at this very instance. He now found the 
> >> right reindex command and the corrupted file is gone. We don't have the 
> >> slightest clue what happened to that file. Was it truncated? Did some 
> >> other process scribble around in the shared memory? How do you tell now?
> > 
> > The end user just could not care less.  They want their machine running 
> > again as soon as is humanly possible without going through a back and 
> > forth process of subscribing to some lists they don't care about, etc.
> 
> I know, that's (unfortunately) true. Although it's not very farsighted 
> because better bug reports usually lead to better software in the next 
> release.
> 
> 
> Jan
> 
> -- 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #

Wed, 10 Dec 2003 12:36:02 +0100
strk <strk@keybit.net>
Re: ERROR: Index pg_toast_8443892_index is not a btree
Tom Lane wrote:

> strk <strk@keybit.net> writes:
>> The question now is: what does that message mean ?
> 
> It means that the "magic number" that should be on the first page of the
> btree index isn't right.  We can deduce that something has clobbered the
> first page of the index, but guessing what and how requires much more
> information.

Clobbered or truncated. A zero size index file causes the same message.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Wed, 10 Dec 2003 10:30:22 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: ERROR: Index pg_toast_8443892_index is not a btree
strk <strk@keybit.net> writes:
> The question now is: what does that message mean ?

It means that the "magic number" that should be on the first page of the
btree index isn't right.  We can deduce that something has clobbered the
first page of the index, but guessing what and how requires much more
information.

			regards, tom lane

Wed, 10 Dec 2003 11:07:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ERROR: Index pg_toast_8443892_index is not a btree 
On Tue, 2003-12-09 at 20:19, Jan Wieck wrote:
> Christopher Kings-Lynne wrote:
> 
> >> I couldn't agree more. Look at this very instance. He now found the 
> >> right reindex command and the corrupted file is gone. We don't have the 
> >> slightest clue what happened to that file. Was it truncated? Did some 
> >> other process scribble around in the shared memory? How do you tell now?
> > 
> > The end user just could not care less.  They want their machine running 
> > again as soon as is humanly possible without going through a back and 
> > forth process of subscribing to some lists they don't care about, etc.
> 
> I know, that's (unfortunately) true. Although it's not very farsighted 
> because better bug reports usually lead to better software in the next 
> release.
> 

HINT::  You might be able to solve this problem by running the REINDEX
command. Of course if you do that you'll destroy all evidence of what
caused the problem, possibly forcing this problem on other users in the
future because you were unwilling to help us to improve the software.
But we understand, it's not like we wrote an entire database system for
you... oh wait we did.

:-)

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


10 Dec 2003 15:15:36 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: ERROR: Index pg_toast_8443892_index is not a btree