Re: slow DELETE on 12 M row table

2009/6/26 Janet Jacobsen <jsjacobsen@lbl.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