sql - (Postgresql更新查询不返回

Question

我有一个简单的更新查询(在两个大表上)，它永远不会完成。

UPDATE transit_edge te1 SET dates_to_add =
(   SELECT ARRAY_AGG(date)
    FROM transit_edge te2 LEFT OUTER JOIN calendar_dates cd2 ON (te2.service_id = cd2.service_id AND cd2.exception_type = 1)
    WHERE te2.transit_edge_id = te1.transit_edge_id
);

如果我只运行具有给定 ID 的内部查询，我会得到正确的结果。

SELECT ARRAY_AGG(date) 
FROM transit_edge te2 LEFT OUTER JOIN calendar_dates cd2 ON (te2.service_id = cd2.service_id AND cd2.exception_type = 1) 
WHERE te2.transit_edge_id = te1.transit_edge_id AND te1.transit_edge_id = 282956

表数相当多:

select count(*) from transit_edge;
count
---------
9187885

select count(*) from calendar_dates;
count
----------
10025969

我还更新了 postgresql.conf 以启用更大的内存使用量。

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 2GB   
work_mem = 200MB   
checkpoint_segments = 3
max_connections = 100 
maintenance_work_mem = 64MB

我运行限制为 100 的内部查询并收到以下错误消息

ERROR:  invalid memory alloc request size 1073741824

如有任何帮助，我们将不胜感激!丹尼尔

Answer 1

尝试使用类似的东西:

UPDATE transit_edge te1 SET dates_to_add =
(   SELECT ARRAY_AGG(date)
    FROM calendar_dates cd2
    WHERE te1.service_id = cd2.service_id AND cd2.exception_type = 1
);