Hi. The user in question is using psycopg2, which he uses
psycopg2:
> 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()
Note that
(1) he said that he does not set an isolation level, and
(2) he does not close the database connection after the
fetchall - instead he has a Python sleep command, so
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 the analysis pipeline - we process the
image data and load it into the database, and other
groups fetch the data from the database and do some
analyses.)
Yes, it is the case that the user's process shows up in
ps aux as "idle in transaction".
What would you recommend in this case? Should the
user set the isolation_level for psycopg, and if so to what?
Is there any Postgres configuration parameter that I
should set?
Should the user close the database connection after
every fetchall?
Thank you for any help you can give.
Janet
Marcin Stępnicki wrote
> On Fri, Jun 26, 2009 at 9:34 AM, Janet Jacobsen<jsjacobsen@lbl.gov> wrote:
>
>
>> I assume that killing the user's process released the lock on the
>> table. This user has only SELECT privileges. Under what
>> conditions would a SELECT lock a table. The user connects
>> to the database via a (Python?) script that runs on another
>> machine. Would this way of connecting to the database result
>> in a lock?
>>
>
> Was this process 'idle in transaction' perhaps? Does this Python
> script use any ORM, like SQLAlchemy? If not, which library does it use
> to connect? If it's psycopg2, which isolation level (autocommit, read
> committed, serializable) is set?
>
> Regards,
> Marcin
>
>
