Обсуждение: vacuum error "left link changed unexpectedly"
Hello, after months of flawless operations suddenly an error occurred. The database is vacuumed every night. Beside the vacuum error message the database works fine. Here the vacuum output: vacuumdb: vacuuming database "CLIX2" vacuumdb: vacuuming of database "CLIX2" failed: ERROR: left link changed unexpectedly Any ideas what is wrong? Or how to fix it? 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:
> 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