Thread: 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 ?). 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;
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 #
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;
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 #
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
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;
>>>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
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
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 #
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, Pennsylvania19073
> 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
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 #
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 #
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
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 #
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