13

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.

2
  • 2
    If the state is idle and it is NOT waiting, then the query is complete but the connection to the db is not closed. idle in transaction, also, means that the query is complete but no 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
  • Ya, I think you're right. Issue is in how Python is handling the query, not an issue with the database. – Kurt Spindler Feb 25 '15 at 20:06
6

First thing you have to separate here are the words query, transaction and connection.

  1. Clue: your query gets executed - it is in the active state. After that the query ends but the connection stays on - the idle state. There is no transaction (it has been committed) so the xact_start is null. So you have to close the connection after the query is succesful.

  2. Clue: Before the autocommit was on the query was left in the middle of the transaction so first you would have to commit and then close connection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.