(Postgre)SQL Update query does not return

Question

I've a simple update query (on two large tables) which does never finish.

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 run the inner query with a 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 table count is rather high:

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

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

I also updated the postgresql.conf to enable larger 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 the inner query with a limit of 100 and got the following error message

ERROR:  invalid memory alloc request size 1073741824

Any help is kindly appreciated! Daniel

No output for EXPLAIN? Without ANALYZE? That suggests it's getting stuck in planning, and that shouldn't happen. Exact PostgreSQL version from SELECT version()? — Craig Ringer
– Craig Ringer, Commented Sep 29, 2014 at 8:39
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit — Daniel Gerber
– Daniel Gerber, Commented Sep 29, 2014 at 8:57
... and a simple EXPLAIN UPDATE ... hangs indefinitely? Is there anything relevant in pg_locks? Is the postgres backend (identified by running SELECT pg_backend_pid() before running the EXPLAIN UPDATE ...) using 100% CPU? — Craig Ringer
– Craig Ringer, Commented Sep 29, 2014 at 8:59

Ihor Romanchenko · Accepted Answer · 2014-09-29 08:30:38Z

1

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

answered Sep 29, 2014 at 8:30

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel Gerber Over a year ago

Is there any way to see if something actually happens, or do I have to wait till the complete query is finished?

Ihor Romanchenko Over a year ago

@DanielGerber You can limit the outer (UPDATE) query to a small subset of id's (WHERE te1.transit_edge_id in (1,2,3, etc)) to check the results. If the results are OK - proceed with full query.

Ihor Romanchenko Over a year ago

@CraigRinger It may not help if the query is doing something stupid (like a cartesian product because of a missing join condition)

Daniel Gerber Over a year ago

This query seems to return with results. But is this really the same query with respect to the left outer join?

Collectives™ on Stack Overflow

(Postgre)SQL Update query does not return

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related