PostgreSQL 9.4 突然无效的内存分配请求大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32687602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL 9.4 suddenly invalid memory alloc request size
提问by Aldibe
I'm building a website which will be used to handle excel files from stores and manipulate them (merging, view, etc.). I'm using PostgreSQL 9.4 for the database, running on Centos 6.6 VM with 4GB RAM. It has 3 databases as follow:
我正在构建一个网站，该网站将用于处理来自商店的 excel 文件并对其进行操作（合并、查看等）。我使用 PostgreSQL 9.4 作为数据库，在 Centos 6.6 VM 上运行，内存为 4GB。它有 3 个数据库，如下所示：
- postgres database
- db_raw, which is used as a placeholder for the data. The excel which are uploaded from the website will be parsed, and the data will be stored here. The database consists of few tables used to keep the data required to process the excel, and a huge table for storing the excel data with currently >140 column and almost 1 million row
- db_processed, which is the main database for the website. It has few small tables for the operational of the website (user table, access list, logging, etc), and 8 tables to store the processed excel data from db_raw. Each of the 8 tables have around 40 column and about a million row.
- postgres 数据库
- db_raw，用作数据的占位符。从网站上传的excel将被解析，数据将存储在这里。数据库由几个表组成，用于保存处理excel所需的数据，以及一个巨大的表，用于存储当前>140列和近100万行的excel数据
- db_processed，这是网站的主要数据库。它有几个用于网站操作的小表（用户表、访问列表、日志记录等），还有 8 个表用于存储来自 db_raw 的处理过的 excel 数据。8 个表中的每一个都有大约 40 列和大约一百万行。
The databases were running fine until this morning. I tried connecting to the db_processed through pgAdmin and PuTTY, and PostgreSQL gave me this message
直到今天早上，数据库都运行良好。我尝试通过 pgAdmin 和 PuTTY 连接到 db_processed，PostgreSQL 给了我这个消息
FATAL: invalid memory alloc request size 144115188075856068
db_raw works fine, and nothing has been changed since 3 days ago as far as I know. What should I do so I can connect to the database again?
db_raw 工作正常，据我所知，自 3 天前以来没有任何变化。我应该怎么做才能再次连接到数据库？
update : I did what @CraigRinger said and restarted the service. I manage to connect to the database, but all the tables are gone :| now this keeps appearing in the log
更新：我做了@CraigRinger 所说的并重新启动了服务。我设法连接到数据库，但所有的表都不见了：| 现在这一直出现在日志中
< 2015-09-21 12:27:22.155 WIB >DEBUG: performing replication slot checkpoint
< 2015-09-21 12:27:22.158 WIB >LOG: request to flush past end of generated WAL; request 46/9E0981D8, currpos 46/771C69B0
< 2015-09-21 12:27:22.158 WIB >CONTEXT: writing block 2 of relation base/18774/12766
< 2015-09-21 12:27:22.158 WIB >ERROR: xlog flush request 46/9E0981D8 is not satisfied --- flushed only to 46/771C69B0
< 2015-09-21 12:27:22.158 WIB >CONTEXT: writing block 2 of relation base/18774/12766
< 2015-09-21 12:27:22.158 WIB >WARNING: could not write block 2 of base/18774/12766
< 2015-09-21 12:27:22.158 WIB >DETAIL: Multiple failures --- write error might be permanent.
回答by Anton Zvonovsky
This generally indicates corrupted data. I faced this problem too and discovered at least too ways of how to solve it.
这通常表示数据损坏。我也遇到了这个问题，并且至少发现了太多的解决方法。
The first way is described here: https://confluence.atlassian.com/jirakb/invalid-memory-alloc-request-size-440107132.html
The second is described here: http://blog.dob.sk/2012/05/19/fixing-pg_dump-invalid-memory-alloc-request-size/
第一种方式在这里描述：https
: //confluence.atlassian.com/jirakb/invalid-memory-alloc-request-size-440107132.html第二种方式在这里描述：http: //blog.dob.sk/2012/ 05/19/fixing-pg_dump-invalid-memory-alloc-request-size/
It would be very cool to improve the function of the second approach to make it fully automatic, though.
不过，改进第二种方法的功能以使其完全自动化会非常酷。
回答by Christian
It is caused by corrupted rows.
它是由损坏的行引起的。
Create a function do "detect" the rows that are corrupted:
创建一个函数来“检测”损坏的行：
CREATE OR REPLACE FUNCTION is_bad_row(tableName TEXT, tabName TEXT, tidInitial tid)
RETURNS integer
as $find_bad_row$
BEGIN
EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM ' || tableName || ' WHERE ctid = ' USING tidInitial;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '% = %: %', tidInitial, SQLSTATE, SQLERRM;
RETURN 1;
END
$find_bad_row$
LANGUAGE plpgsql;
... and then create a "temp table" to store the ctid of the bad rows:
...然后创建一个“临时表”来存储坏行的 ctid：
create table bad_rows as
SELECT ctid as row_tid
FROM your_schema.your_table
where is_bad_row('your_schema.your_table', 'your_table', ctid) = 1
... and after that you just need to delete those rows:
...之后你只需要删除这些行：
delete from your_schema.your_table where ctid in (select row_tid from bad_rows)
... and remove the "temp table":
...并删除“临时表”：
drop table bad_rows
