PostgreSQL坏块问题

问题概述

应用在做查询操作时报错

ERROR: invalid memory alloc request size 18446744073709551613

问题原因

数据中有物理坏块

解决方案

删除损坏的行

1.创建扩展

create extension hstore;

2.创建function

CREATE OR REPLACE FUNCTION
  find_bad_row(tableName TEXT)
  RETURNS tid
  as $find_bad_row$
DECLARE
  result tid;
  curs REFCURSOR;
  row1 RECORD;
  row2 RECORD;
  tabName TEXT;
  count BIGINT := 0;
BEGIN
  SELECT reverse(split_part(reverse($1), '.', 1)) INTO tabName;
  OPEN curs FOR EXECUTE 'SELECT ctid FROM ' || tableName;
  count := 1;
  FETCH curs INTO row1;
  WHILE row1.ctid IS NOT NULL LOOP
    result = row1.ctid;
    count := count + 1;
    FETCH curs INTO row1;
    EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM '
         || tableName || ' WHERE ctid = $1' INTO row2
         USING row1.ctid;
    IF count % 100000 = 0 THEN
      RAISE NOTICE 'rows processed: %', count;
    END IF;
  END LOOP;
  CLOSE curs;
  RETURN row1.ctid;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'LAST CTID: %', result;
      RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
  RETURN result;
END
$find_bad_row$
LANGUAGE plpgsql;

3.通过函数查找问题行

pepsidb=# select find_bad_row('pepsi.t1');
NOTICE: LAST CTID: (6112,6)
NOTICE: XX000: invalid memory alloc request size 18446744073709551613
find_bad_row
--------------
(6112,6)
(1 row)

4.查看问题ctid

pepsidb=#  select * from pepsi.t1 where ctid = '(6112,6)';    #查找有问题的ctid，若能查出
的数据可保存下来
ERROR: invalid memory alloc request size 18446744073709551613


pepsidb=#  delete from pepsi.t1 where ctid = '(6112,6)';   #删除有问题的ctid对应的数据块

5.重复执行第3,4步，直到全部有问题的记录被清除。