0

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

  1. 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.

  2. By querying pg_stat_activity, the query initially comes into the database as state='active'. After maybe 15 seconds, the state changes to 'idle' and additionally, the xact_start is set to NULL. The waiting flag is never set to true.

  3. 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.

Improve this question
6
  • 1
    Do you know what exactly query is running? Can you just call it by yourself to database? Are you sure that problem on python side? Commented Feb 25, 2015 at 17:35
  • I know the exact query and can successfully call from command line. Based on that, I'm inclined to think it's a python problem. But I haven't found the root problem yet. Commented Feb 25, 2015 at 17:39
  • I don't know what you used for debug, so can you run script with debugger step by step and find where query becomes idle? This is how to use pdb: import pdb; pdb.set_trace(). n - next step, s - step into, help - for help. Commented Feb 25, 2015 at 17:46
  • pdb doesn't work - it hangs in the engine.execute. Commented Feb 25, 2015 at 20:06
  • did you solve this? I am also getting a hang in engine.execute. Everything was working fine an hour ago and now, it freezes. Commented Apr 23, 2015 at 0:27
Related questions
162
Debugging (displaying) SQL command sent to the db by SQLAlchemy
26
How to set statement timeout for query execution
27
python sqlalchemy + postgresql program freezes
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.