一.报错内容:
ERROR: invalid memory alloc request size 18446744073709551613
二.可能原因:
数据库产生坏行,可能是硬件损坏,可能是一个bug,也可能是不正确的硬件配置。
首先考虑将自带的zero_damaged_pages参数设置为true,这种方法不会对物理文件做修改,只是把内存上损坏页面的缓存变为0。
如果此方法解决了报错,请将这张表备份出来重新恢复,或者select到另一张表。
三.排查方法:
1.创建扩展:create extension hstore;
2.创建函数:
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.查询报错表的坏行:select find_bad_row('public.pinyin');