Clever Geek Handbook

(Postgre) SQL Update request not returning

I have a simple update query (on two large tables) that never ends.

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
);

If I only execute the inner query with the given ID, I get the correct result.

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

The number of tables is quite large:

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

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

I also updated postgresql.conf to increase memory usage.

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

# - Memory -

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

I ran an internal request with a limit of 100 and got the following error message

ERROR:  invalid memory alloc request size 1073741824

Any help is kindly appreciated! Daniel

+3


source to share


1 answer


Try using something like:



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
);
+1


source






More articles:


All Articles