在日常数据库运维过程中，我们可能会遇到类似以下的错误：
test=# create table t2_text as select * from t1_text; ERROR: unexpected chunk size -4 (expected 1996) in chunk 0 of 4 for toast value 61962 in pg_toast_61919
该错误信息是由于部分tuple的 toast 字段丢失，导致数据无法访问。需要通过游标方式，将可以访问的数据备份出来。
注意：设置 zero_damaged_pages = on 无法解决该问题。
以下例子模拟 t1_text表部分tuple 的 text 字段出现损坏，需要通过游标将数据从 t1_text 迁移至 t2_text 。游标如下：
create table ctid_done_tmp123(rid tid,result varchar(9)); create table t2_text as select * from t1_text where 1=2; create or replace procedure process_error_ctid as v_tid tid; cursor cur_t1_text is select ctid from t1_text where ctid not in (select rid from ctid_done_tmp123); begin open cur_t1_text; loop fetch cur_t1_text into v_tid; exit when cur_t1_text%NOTFOUND; begin insert into t2_text select * from t1_text where ctid=v_tid; insert into ctid_done_tmp123 values(v_tid,‘SUCCESS‘); exception when others then insert into ctid_done_tmp123 values(v_tid,‘ERROR‘); commit; exit; end; end loop; end; / declare v_oldcnt integer; v_newcnt integer; begin select count(*) into v_oldcnt from ctid_done_tmp123; v_newcnt := 0; while (true) loop call process_error_ctid(); select count(*) into v_newcnt from ctid_done_tmp123; if v_oldcnt = v_newcnt then exit; end if; end loop; end;
注意：以上的例子通过不停的调用函数process_error_ctid来实现，这是由于我们当前的游标不支持跨事务，后续可以修改该脚本。
这里有几个问题需要注意：
R3 版本在遇到异常时，事务操作都被回退，因此，只能取到 Error 记录的 ctid。 由于需要重复执行，效率不高。
create table ctid_done_tmp123(rid tid,result varchar(9)); create table t2_text as select * from t1_text where 1=2; create or replace procedure process_error_ctid as v_tid tid; v_name text; cursor cur_t1_text is select ctid from t1_text where ctid not in (select rid from ctid_done_tmp123); begin open cur_t1_text; loop fetch cur_t1_text into v_tid; exit when cur_t1_text%NOTFOUND; begin select name into v_name from t1_text where ctid=v_tid; --只需访问lob 字段 exception when others then insert into ctid_done_tmp123 values(v_tid,‘ERROR‘); commit; exit; end; end loop; end; / declare v_oldcnt integer; v_newcnt integer; begin select count(*) into v_oldcnt from ctid_done_tmp123; v_newcnt := 0; while (true) loop call process_error_ctid(); select count(*) into v_newcnt from ctid_done_tmp123; if v_oldcnt = v_newcnt then exit; end if; end loop; end; / insert into t2_text select * from t1_text where ctid not in (select rid from ctid_done_tmp123);
原文：https://www.cnblogs.com/kingbase/p/14899213.html