I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on.
The query is an aggregation on a table that is simultaneously getting inserted, which I'm guessing somehow relates to the issue. (The aggregation is on the previous days data, so the insertions shouldn't affect results.)
Clues
I'm running this inside a python script using sqlalchemy. However, I've set transaction level to autocommit, so I don't think things are getting wrapped inside a transaction. On the other hand, I don't see the query hang when I run it manually in sql terminal.
By querying
pg_stat_activity
, the query initially comes into the database asstate='active'
. After maybe 15 seconds, the state changes to 'idle' and additionally, thexact_start
is set toNULL
. The waiting flag is never set to true.Before I figured out the transaction level autocommit for sqlalchemy, it would instead hang in state
'idle in transaction'
rather than'idle'
. And it possibly hangs slightly less frequently since making that change?
I feel like I'm not equipped to dig any deeper than I have on this. Any feedback, even explaining more about different states and relevant postgres internals without giving a definite answer, would be greatly appreciated.
COMMIT
has been issued to end the transaction. It sounds like your issue may be elsewhere than the db.. – Joishi Bodio Feb 25 '15 at 17:41