今天开发人员反映数据库异常，报错信息 “right sibling’s left-link doesn’t match: block 817 links..”，对生产环境产生一定影响，于是迅速查看数据库日志，发现有大量以下错误，见 CSV 日志。

数据库日志

1

2011-08-21 13:11:04.607 CST,"skytf","skytf",3905,"192.168.170.38:12421",4e50930a.f41,2,"UPDATE",2011-08-21 13:09:30 CST,174/119132,175573360,ERROR,XX000,right sibling's left-link doesn't match: block 817 links to 45366 instead of expected 70930 in index ""t_tmp_win_date_win_modify_time_idx""",,,,,,"

备注： CSV 日志里有大量以上日志, 猜想索引 “t_tmp_win_date_win_modify_time_idx” 可能异常。

查看手册PostgreSQL 错误代码表

1
2

Class XX ― Internal Error  
XX000 INTERNAL ERROR internal_error

备注：查看手册 PostgreSQL 错误代码表，是内部错误。

异常 SQL

出错时，应用发起的语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

update t_tmp  
 set win = win + 1,  
 date_win = (select CASE date(date_win_modify_time)  
 WHEN CURRENT_DATE THEN  
 date_win + 1  
 ELSE  
 1  
 END  
 from t_tmp  
 where appid = $1  
 and skyid = $2),  
 week_win = (select CASE  
 date_trunc('week', week_win_modify_time)  
 WHEN date_trunc('week', now()) THEN  
 week_win + 1  
 ELSE  
 1  
 END  
 from t_tmp  
 where appid = $3  
 and skyid = $4),  
 date_win_modify_time = now(),  
 week_win_modify_time = now(),  
 strength_value = strength_value + $5  
WHERE appid = $6  
 and skyid = $7

备注： 这个语句是 update 语句，更新非常频繁，且业务逻辑复杂。

报错代码含义

What does the above mean?
It means you have got a corrupted index. REINDEX will probably fix it.
You should try to figure out what caused the problem though …

备注：说是索引被损坏，需要重建索引。

查询异常索引信息

1
2
3
4
5
6
7
8
9
10
11
12
13

skytf=> /dt+ t_tmp  
 List of relations  
Schema | Name | Type | Owner | Size | Description  
--------+-------------+-------+-------+---------+-------------  
skytf | t_tmp | table | skytf | 2207 MB | 角色信息表  
(1 row)

skytf=> /di+ t_tmp_win_date_win_modify_time_idx  
 List of relations  
Schema | Name | Type | Owner | Table | Size | Description  
--------+------------------------------------------+-------+-------+-------------+---------+-------------  
skytf | t_tmp_win_date_win_modify_time_idx | index | skytf | t_tmp | 1016 MB |  
(1 row)

备注：表 t_tmp 大小为 2G,而索引 t_tmp_win_date_win_modify_time_idx 就有 1个G，索引大得出奇，估计是膨胀太厉害了。

索引重建

1
2
3
4
5
6
7
8
9
10
11
12

skytf=> drop index t_tmp_win_date_win_modify_time_idx;  
DROP INDEX  

skytf=> CREATE INDEX concurrently t_tmp_win_date_win_modify_time_idx ON t_tmp USING btree (win DESC, date_win_modify_time);  
CREATE INDEX  

skytf=> /di+ t_tmp_win_date_win_modify_time_idx  
 List of relations  
Schema | Name | Type | Owner | Table | Size | Description  
--------+------------------------------------------+-------+-------+-------------+-------+-------------  
skytf | t_tmp_win_date_win_modify_time_idx | index | skytf | t_tmp | 36 MB |  
(1 row)

备注：索引 t_tmp_win_date_win_modify_time_idx 重建后，大小迅速降为 36 MB, 可见索引膨胀太厉害了。继续观察，索引重建后，上面错误信息不再抛出。

总结

生产环境，对于更新非常频繁的表，需要定期重建索引, 以保证索引不会迅速膨胀， 保证索引性能。