问题概述
应用在做查询操作时报错
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对应的数据块