Hi. The user in question is using psycopg2, which he uses
> 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()
(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
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 the user close the database connection after
Thank you for any help you can give.
Marcin Stępnicki wrote
> On Fri, Jun 26, 2009 at 9:34 AM, Janet Jacobsen<jsjacobsen(at)lbl(dot)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?
In response to
pgsql-performance by date
|Next:||From: Robert Haas||Date: 2009-06-27 01:36:42|
|Subject: Re: slow DELETE on 12 M row table|
|Previous:||From: Greg Smith||Date: 2009-06-26 22:34:20|
|Subject: Re: Terrible Write Performance of a Stored Procedure|