Re: user's process shows as "idle in transaction"

Hi.  I wanted to let you know that the user added a rollback() to his
code, and now things are working much better.

Prior to his adding the rollback(), deleting as few as 60 rows from
the table (which has 12 M rows) that his script queries took as long
as 4 minutes.  If I tried to create an index on that table, ps aux showed
me that the process was always "waiting".  The only way I could
create indexes - or delete more than 60 rows at a time - was to kill
the user's postgres process.

Now that he has added the rollback() to his code, his postgres
process per "ps aux" no longer shows that it is "idle in transaction",
but instead just "idle".

And I just tried creating an index on the table with 12.5M rows, and
it took less than one minute.  Much, much better.

Thank you for your help,
Janet


Federico Di Gregorio wrote:
> Il giorno lun, 29/06/2009 alle 12.26 -0700, Janet Jacobsen ha scritto:
> [snip]
>   
>> The user told me that he does not close the database connection
>> after the fetchall - instead he has a Python sleep command, so that 
>> he is checking the database every 60 s to see whether new entries
>> have been added to a given table
>> His code is part of an analysis pipeline, whereas the part of the
>> database that I work on is loading processed data into the
>> database.
>> Is there something missing from his code sample, like a commit or
>> a set_isolation_level, that if added would prevent the "idle in
>> transaction" from happening?  
>>     
>
> The user is wrong and you're right, the "idle in transaction" can be
> avoided by both a commit() (or rollback()) before going to sleep or by
> setting the transaction mode to "autocommit":
>
> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>
> Hope this helps,
> federico
>
>