错误信息产生:
下面的报错一般为有坏块的产生。
test=# select max(create_time) from public.tbl_table where create_time>='2010-11-08';
ERROR: could not read block 365 of relation 16779/5348/2486: read only 0 of 8192 bytes
看到这个错误信息,首先想到的是表 tbl_table 上有坏块。
--查看执行计划
test=# \d tbl_table;
Table "public.tbl_table"
Column | Type | Modifiers
----------------+-----------------------------+------------------------
total | integer |
logined | integer |
logining | integer |
http | integer |
rawtcp | integer |
create_time | timestamp without time zone | not null default now()
logincountdesc | character varying |
logincountaddr | character varying | not null
Indexes:
"tbl_table_pkey" PRIMARY KEY, btree (create_time, logincountaddr)
"index_tes" btree (create_time)
test=# select max(create_time) from public.tbl_table where create_time>='2010-11-08';
ERROR: could not read block 365 of relation 16779/5348/2486: read only 0 of 8192 bytes
test=# explain select max(create_time) from public.tbl_table where create_time>='2010-11-08';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.04..0.05 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.04 rows=1 width=8)
-> Index Scan testward using index_tes on tbl_table (cost=0.00..66.28 rows=1507 width=8)
Index Cond: (create_time >= '2010-11-08 00:00:00'::timestamp without time zone)
Filter: (create_time IS NOT NULL)
(6 rows)
发现上面的查询走的索引 index_tes,所以很可能索引有问题。
--根据报错信息,从relation后面的数字分析
test=# select oid,relname from pg_class where oid=2486;
oid | relname
-------+-----------------------------------------
2486 | index_tes
(1 row)
Time: 0.596 ms
可以看到 2486正好是表上的索引 index_tes。
select relname,relkind from pg_class where relfilenode = '2486';
如果查询结果中relkind = i,表示损坏的是一个索引
--查看索引状态
test=# select * from pg_index where indexrelid=2486;
indexrelid | indrelid | indnatts | indisunique | indisprimary | indisclustered | indisvalid | indcheckxmin | indisready | indkey | indclass | indoption | indexprs | indpred
------------+----------+----------+-------------+--------------+----------------+------------+--------------+------------+--------+----------+-----------+----------+---------
2486 | 24823 | 1 | f | f | f | t | f | t | 6 | 10053 | 0 | |
(1 row)
indisvalid=t 表示索引处于可用状态。
--尝试下重建索引
test=# \timing
Timing is on.
test=# reindex index concurrently index_tes; 注:REINDEX 命令和 CREATE INDEX 持有表级锁为 SHARE 锁,会阻塞表上的 DML 操作。
REINDEX
Time: 107796.232 ms
--索引重建后,查询恢复正常
test=# select pg_size_pretty(pg_relation_size('index_tes'));
pg_size_pretty
----------------
31 MB
(1 row)
另一种情况,主键损坏:
需要注意,损坏的可能是普通索引,也可能是主键或唯一键。如果索引的名称中有“_pkey”等很可能属于主键,而名称中含有 “key”则很可能属于唯一键。
如果表名是“pg”开头的,则说明它是系统表。
pgclass.relkind 的值的几种:
r: 表示ordinary table(普通表);
i: 表示index(索引);
S: 表示sequence(序列);
V: 表示view(视图);
m: 表示materialized view(物化视图);
c: 表示composite type(复合类型);
t: 表示TOAST table(TOAST 表);
f: 表示foreign table(外部表)
如果损坏的是主键或唯一键,首先要找到它所在的表,
例如:
select tablename,indexname from pg_indexes where indexname = 'tb_pkey';
查询结果:
t1, tb_pkey
获取索引的定义:
select pg_get_constraintdef((select oid from pg_constraint where conname = ' tb_pkey '));
查询结果:PRIMARY KEY (id)
重建主键约束:
Alter table t1 drop constriant tb_pkey;
Alter table t1 add constraint tb_pkey PRIMARY KEY (id);