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 #

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 #

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 <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, Pennsylvania 19073

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 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

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:

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 #

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

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