vacuum error "left link changed unexpectedly"

Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:

vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link
changed unexpectedly

Hm, is this repeatable? When I wrote the code I thought it was
a can't-happen case, which is why the error message is so terse
(it was only pure paranoia that made me put in the check at all).
I suppose it must indicate a corrupted index, but I'm not clear
on exactly what the nature of the corruption is.

You might proceed by fixing the error message to be a little more
helpful, say

if (opaque->btpo_prev != leftsib)
elog(ERROR, "left link changed unexpectedly in block %u of index %s",
target, RelationGetRelationName(rel));

(this is in src/backend/access/nbtree/nbtpage.c, about line 863 in
current sources). Once you know which index has the problem,
I would like to see the output of pg_filedump on that index.
After you've got the dump, a REINDEX should fix it.

BTW, which Postgres version is this exactly?

regards, tom lane

Hello Tom,

thanks for your fast answer. And yes it is reproducible. It started
during my vacation (of course!!!) and I get the message ever since
(approx. 6 weeks, vacuum daily).

We use

Fedora Linux Core 2
PostgreSQL 7.4.2

I'll try to get the information you asked for over the weekend.

Ulrich

Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:

vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link
changed unexpectedly

Hm, is this repeatable? When I wrote the code I thought it was
a can't-happen case, which is why the error message is so terse
(it was only pure paranoia that made me put in the check at all).
I suppose it must indicate a corrupted index, but I'm not clear
on exactly what the nature of the corruption is.

You might proceed by fixing the error message to be a little more
helpful, say

if (opaque->btpo_prev != leftsib)
elog(ERROR, "left link changed unexpectedly in block %u of index %s",
target, RelationGetRelationName(rel));

(this is in src/backend/access/nbtree/nbtpage.c, about line 863 in
current sources). Once you know which index has the problem,
I would like to see the output of pg_filedump on that index.
After you've got the dump, a REINDEX should fix it.

BTW, which Postgres version is this exactly?

regards, tom lane

--
Ulrich Wisser / System Developer

RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
________________________________________________________________
http://www.relevanttraffic.com

Hi Tom,

I did run vacuum verbose".

INFO: vacuuming "public.userclick"
INFO: index "userclick_i01" now contains 13715747 row versions in 60640
pages
DETAIL: 0 index row versions were removed.
14209 index pages have been deleted, 14209 are currently reusable.
CPU 2.46s/6.06u sec elapsed 186.45 sec.
ERROR: left link changed unexpectedly

To me this looks as the index userclick_i01 is corrupted.
I can not recompile PG, this is a production system. Is there anything
else I could do to find out which file is affected?

Ulrich

Lane wrote:

Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:

vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link
changed unexpectedly

Hm, is this repeatable? When I wrote the code I thought it was
a can't-happen case, which is why the error message is so terse
(it was only pure paranoia that made me put in the check at all).
I suppose it must indicate a corrupted index, but I'm not clear
on exactly what the nature of the corruption is.

You might proceed by fixing the error message to be a little more
helpful, say

if (opaque->btpo_prev != leftsib)
elog(ERROR, "left link changed unexpectedly in block %u of index %s",
target, RelationGetRelationName(rel));

(this is in src/backend/access/nbtree/nbtpage.c, about line 863 in
current sources). Once you know which index has the problem,
I would like to see the output of pg_filedump on that index.
After you've got the dump, a REINDEX should fix it.

BTW, which Postgres version is this exactly?

regards, tom lane

--
Ulrich Wisser / System Developer

RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
________________________________________________________________
http://www.relevanttraffic.com

Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:

INFO: vacuuming "public.userclick"
INFO: index "userclick_i01" now contains 13715747 row versions in 60640
pages
DETAIL: 0 index row versions were removed.
14209 index pages have been deleted, 14209 are currently reusable.
CPU 2.46s/6.06u sec elapsed 186.45 sec.
ERROR: left link changed unexpectedly

To me this looks as the index userclick_i01 is corrupted.

No, it would be the one next to be processed. VACUUM does them in OID
order, so try something like

select indexrelid::regclass from pg_index
where indrelid = 'public.userclick'::regclass
order by indexrelid;

and look to see which index comes after userclick_i01.

regards, tom lane

Hi Tom,

No, it would be the one next to be processed. VACUUM does them in OID
order, so try something like

select indexrelid::regclass from pg_index
where indrelid = 'public.userclick'::regclass
order by indexrelid;

indexrelid
---------------
userclick_i01
userclick_i02
userclick_i03
userclick_i04
userclick_i05
userclick_i06
userclick_i07
(7 rows)

How do I proceed? How can I tell which files are involved?

Regards,

Ulrich

--
Ulrich Wisser / System Developer

RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
________________________________________________________________
http://www.relevanttraffic.com

Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:

indexrelid
---------------
userclick_i01
userclick_i02
userclick_i03
userclick_i04
userclick_i05
userclick_i06
userclick_i07
(7 rows)

OK, so userclick_i02 appears to be the broken index.

How do I proceed? How can I tell which files are involved?

See contrib/oid2name, or read
http://developer.postgresql.org/docs/postgres/storage.html
and work out the index file name for yourself.

If you don't already have a copy of pg_filedump, see
http://sources.redhat.com/rhdb/utilities.html

regards, tom lane