PostgreSQL:如何将大数据集插入表中？

Question

我在 PostgreSQL 数据库中有一个表，我需要向其中添加 200 万行。当我尝试使用 INSERT INTO 查询时，它会抛出

ERROR:  canceling statement due to statement timeout
CONTEXT:  PL/pgSQL function cdb_checkquota() line 11 at IF

我已经尝试重新配置 postgresql.conf 文件(扩大 work_mem 和 statement_timeout)但它没有用。

谁能告诉我我想念这里的是什么？顺便说一句，我正在使用 INSERT INTO 查询，因为我需要复制表数据，所以我在没有主键的情况下处理表数据，以防止重复键。

Answer 1

据我所知，错误仅在设置 statement_timeout 时触发:

denis=# select pg_sleep(2);
 pg_sleep 
----------
 
(1 row)
denis=# set statement_timeout to 1;
SET
denis=# select pg_sleep(2);
ERROR:  canceling statement due to statement timeout

您是否尝试过完全禁用它？

set statement_timeout to 0

顺便说一句，这应该是默认设置。并根据 docs (强调我的):

statement_timeout (integer)Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.