user's process shows as "idle in transaction"

Hi.  I have set up a Postgres database that is being queried
by a handful of users on a daily basis.

One user is using psycopg to connect to the database.  When
I run "ps aux | grep postgres", this user's process shows as
"idle in transaction".  According to the postgres-performance
list, this means that the user has a lock on the table he is querying,
and in fact, I cannot do things like drop index or alter table drop
constraint unless I kill the user's process.  After I kill the user's
process, the drop index and alter table commands execute
immediately.

I've had an email exchange with the user who said that he is
using psycopg2 as follows:
> > import psycopg2
> > conn = psycopg2.connect("dbname=%s  user=%s host=%s password=%s port=%s" ...)
> > pg_cursor = conn.cursor()
> > pg_cursor.execute(<select string>)
> > rows = pg_cursor.fetchall()
>   
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? 

Thanks,
Janet

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg