From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Janet Jacobsen <jsjacobsen(at)lbl(dot)gov> |
Cc: | Marcin Stępnicki <mstepnicki(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow DELETE on 12 M row table |
Date: | 2009-06-27 01:36:42 |
Message-ID: | 603c8f070906261836o6224b8a7uf304273988e978c3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2009/6/26 Janet Jacobsen <jsjacobsen(at)lbl(dot)gov>:
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2009-06-27 03:27:36 | Re: what server stats to track / monitor ? |
Previous Message | Janet Jacobsen | 2009-06-27 01:16:57 | Re: slow DELETE on 12 M row table |