案例说明：
数据库在日常的维护过程中，在执行表查询（select），如下图所示，出现“could not read block 0 in file "base/16385/16408": read only 512 of 8192 bytes”故障，通过对“base/16385/16408”的定位，故障和toast表有关。本案例复现了以上故障，并提供了解决方案。
适用版本：
KingbaseES V8R6
一、案例复现
TOAST存储的表不能单独创建，只有当普通表包含了main,extended或external存储格式的字段时，系统会自动创建一个和普通表关联的TOAST表。当一行记录(tuple)存储的(包括压缩后的大小)大小超过TOAST_TUPLE_THRESHOLD(默认2K)时，会存储到TOAST表。
1、创建测试表及插入数据
# 创建测试表
prod=# create table test1(id int primary key ,v_name varchar);
CREATE TABLE
#查看表结构
prod=# \d+ test1
Table "public.test1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
v_name | varchar | | | | extended | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
Access method: heap
# 插入大字段数据
prod=# insert into test1 select generate_series(1,100),'a';
INSERT 0 100
prod=# insert into test1 select generate_series(101,200),repeat(md5(random()::text),8000);
INSERT 0 100
prod=# insert into test1 select generate_series(201,300),'b';
INSERT 0 100
---当字段的storage标识为“extended“时，如果字段值默认超过2k，会对字段执行压缩，如果压缩后的size
仍大于2k，则将字段值存入toast表，在源表建立到toast表的指针。
2、查看toast表信息
#获取源表对应的toast表OID
prod=# select oid,reltoastrelid,relname from pg_class where relname='test1';
oid | reltoastrelid | relname
-------+---------------+---------
16405 | 16408 | test1
(1 row)
#获取toast表名称
prod=# select oid,relname from pg_class where oid=16408;
oid | relname
-------+----------------
16408 | pg_toast_16405
(1 row)
#获取toast表数据文件存储路径
prod=# select pg_relation_filepath('pg_toast.pg_toast_16405');
pg_relation_filepath
----------------------
base/16385/16408
(1 row)
prod=# select count(*) from pg_toast.pg_toast_16405;
count
-------
200
(1 row)
prod=# \d+ pg_toast.pg_toast_16405;
TOAST table "pg_toast.pg_toast_16405"
Column | Type | Storage
------------+---------+---------
chunk_id | oid | plain
chunk_seq | integer | plain
chunk_data | bytea | plain
二、模拟toast表数据文件故障
#模拟toast表数据文件故障
[kingbase@node102 ~]$ dd if=/dev/zero of=/data/kingbase/v8r6_c6/data/base/16385/16408 bs=512 count=1
1+0 records in
1+0 records out
512 bytes (512 B) copied, 0.000188319 s, 2.7 MB/s
[kingbase@node102 ~]$ ls -lh /data/kingbase/v8r6_c6/data/base/16385/16408
-rw------- 1 kingbase kingbase 512 Nov 3 11:21 /data/kingbase/v8r6_c6/data/base/16385/16408
#重启数据库并清理缓存
[kingbase@node102 bin]$ ./sys_ctl stop -D /data/kingbase/v8r6_c6/data
waiting for server to shut down.... done
server stopped
[root@node102 ~]# echo 3 > /proc/sys/vm/drop_caches
[kingbase@node102 bin]$ ./sys_ctl start -D /data/kingbase/v8r6_c6/data
waiting for server to start....2022-11-03 11:22:12.466 CST [15429] LOG: sepapower extension initialized
.......
server started
#查询源表数据出现块故障
prod=# select * from test1;
ERROR: could not read block 0 in file "base/16385/16408": read only 512 of 8192 bytes
# toast表数据为空
prod=# select * from pg_toast.pg_toast_16405 limit 2;
chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)
# 可以对源表做行统计查询
prod=# select count(*) from test1;
count
-------
300
(1 row)
三、解决toast表故障步骤
1、尝试重建索引并对表分析
# 尝试重建索引
prod=# reindex table test1;
REINDEX
prod=# reindex table pg_toast.pg_toast_16405;
REINDEX
# 执行表分析
prod=# vacuum analyze test1;
VACUUM
# 源表查询依然出现故障
prod=# select * from test1;
ERROR: missing chunk number 0 for toast value 16413 in pg_toast_16405
2、定位源表发生故障的记录
# 尝试通过手工方式定位
prod=# select * from test1 order by id limit 1 offset 1;
id | v_name
----+--------
2 | a
(1 row)
prod=# select * from test1 order by id limit 1 offset 100;
ERROR: missing chunk number 0 for toast value 16413 in pg_toast_16405
prod=# select * from test1 order by id limit 100 offset 0;
id | v_name
-----+--------
1 | a
2 | a
3 | a
4 | a
5 | a
6 | a
7 | a
8 | a
9 | a
........
prod=# select * from test1 order by id limit 100 offset 1;
ERROR: missing chunk number 0 for toast value 16413 in pg_toast_16405
#通过脚本定位故障记录
[kingbase@node102 ~]$ cat chk_toast.sh
#!/bin/bash
CNT=300 #300是表的总行数
CMD_DIR='/opt/Kingbase/ES/V8R6_C6/Server/bin'
PORT=54325
for ((i=1; i<=CNT;i++))
do
$CMD_DIR/ksql -U system prod -p $PORT -c "SELECT * FROM test1 order by id LIMIT 1 offset $i" >/dev/null || echo $i
done
# 执行脚本
[kingbase@node102 ~]$ sh chk_toast.sh
ERROR: missing chunk number 0 for toast value 16413 in pg_toast_16405
100
ERROR: missing chunk number 0 for toast value 16414 in pg_toast_16405
101
ERROR: missing chunk number 0 for toast value 16415 in pg_toast_16405
102
........
ERROR: missing chunk number 0 for toast value 16511 in pg_toast_16405
198
ERROR: missing chunk number 0 for toast value 16512 in pg_toast_16405
199
---如上所示，从第100行开始到199行，字段v_name的字段值存储在toast表，因toast表故障，因此无法从源表访问。
3、清理源表中故障记录
# 确定源表需清理的记录
prod=# select * from test1 order by id limit 1 offset 99;
id | v_name
-----+--------
100 | a
(1 row)
prod=# select * from test1 order by id limit 1 offset 199;
ERROR: missing chunk number 0 for toast value 16512 in pg_toast_16405
prod=# select * from test1 order by id limit 1 offset 200;
id | v_name
-----+--------
201 | b
(1 row)
#删除源表中故障记录
prod=# delete from test1 where id >100 and id <201;
DELETE 100
#清理故障记录后源表可以正常查询
prod=# select * from test1;
id | v_name
-----+--------
1 | a
2 | a
3 | a
4 | a
5 | a
6 | a
7 | a
8 | a
9 | a
10 | a
11 | a
12 | a
13 | a
14 | a
.......
prod=# select count(*) from test1;
count
-------
200
(1 row)
prod=# select * from pg_toast.pg_toast_16405;
chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)
#源表插入数据
prod=# insert into test1 select generate_series(101,200),repeat(md5(random()::text),8000);
INSERT 0 100
#toast表生成新的记录
prod=# select count(*) from pg_toast.pg_toast_16405;
count
-------
200
(1 row)
prod=# insert into test1 select generate_series(301,400),repeat(md5(random()::text),8000);
INSERT 0 100
prod=# select count(*) from pg_toast.pg_toast_16405;
count
-------
400
(1 row)
prod=# select count(distinct chunk_id) from pg_toast.pg_toast_16405 ;
count
-------
200
(1 row)
---从以上所示，toast表故障问题已经解决。
四、总结
因toast表坏块问题，将导致源表不可访问，如果有有效的备份，可以通过备份来进行故障的恢复；如果没有有效的备份，可以通过对源表故障记录的清理，完成toast表的恢复。