title: CLUSTER not shrinking table size while running other transactions using Psycopg2
body: <p>We are running a python process which runs this stored procedure, which import files from a certain directory to the postgres database. These files are first get imported to a in-memory table and then to the disk table. The actual size of the in-memory table should never really grow beyond 30 MB. As this table is constantly updated, the size of the table grows (because of dead tuples). To keep things in check, we need to perform CLUSTER operation on the table. I am using psycopg2 module to run stored procedure adn CLUSTER the table, but if the import process is running the size of the table never goes down. But If I stop the import process and run CLUSTER then the size of the table goes down. Because of the performance reason, I should be able to run CLUSTER command without stopping the import procedure.</p>
  
  <p>I tried manual commit, ISOLATION_LEVEL_AUTOCOMMIT but none of this has worked.
  Below is the sample code of the process - </p>
  
  <pre><code>while True:
      -get the filenames in directory
      for filpath in  filenames:
          conn = psycopg2.connect("dbname='dbname' user='user' password='password'")
          cursor = conn.cursor()
          # Calls a postgresql function that reads a file and imports it into 
          # a table via INSERT statements and DELETEs any records that have the 
          # same unique key as any of the records in the file.
          cursor.execute("SELECT import('%s', '%s');" % (filepath, str(db_timestamp))
          conn.commit()
          cursor.close()
          conn.close()
          os.remove(get_media_path(fname))
  </code></pre>
  
  <p>With the similar conn object, I want to run CLUSTER command once an hour - </p>
  
  <pre><code>conn = psycopg2.connect("dbname='dbname' user='user' password='password'")
  cursor = conn.cursor()
  cursor.execute("CLUSTER table_name")
  conn.commit()
  cursor.close()
  conn.close()
  </code></pre>
  
  <p>Also, I tried setting - </p>
  
  <pre><code>conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
  </code></pre>
  
  <p>Another piece of information - 
  I have all this running inside django environment. I could not use django connection objects to do the task because - django could not  release connections with my threading code and soon the database stopped accepting connections.Does this mixed environment might have an effect on psycopg?</p>
  
  <p>Few observations -     </p>
  
  <ol>
  <li>Running the CLUSTER command when import process is running - size doesn't go down</li>
  <li>When I stop the import process and then run CLUSTER - size does go down</li>
  <li>When I stop the import process and start import process back, and after that run CLUSTER command - size does go down</li>
  </ol>
  
  <p>Any thoughts on the problem?</p>