On 6/26/09 6:36 PM, "Robert Haas" <robertmhaas@xxxxxxxxx> wrote:
> 2009/6/26 Janet Jacobsen <jsjacobsen@xxxxxxx>:
>> 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?
>
> You need to COMMIT or ROLLBACK the in-process transaction and then not
> start a new transaction until you're ready to execute the next query.
> Possibly calling .commit() after executing your query might be all you
> need to do, but never having used psycopg2 I couldn't say. You might
> try asking on the psycopg mailing list.
>
> ...Robert
>
In addition to the above, note that long lived transactions cause all sorts
of other problems in the database. In particular, table and index bloat can
become severe due to this sort of poor client behavior if there is a lot of
update or delete activity. You can find out with "vacuum analyze verbose"
on tables of interest whether there are a high ratio of dead tuples in the
tables and indexes.
> --
> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance
