Cleaning up large objects

Hi,

I have a problem with the cleanup of large objects.

My database consists of (mainly) one table that holds a date and an
associated large object id. The large object can be as big as 2 megabytes.

Every hour, I have a small method that removes all entries that are older
than a certain date:

private void cleanup(Connection dbConn, Date deleteTo) throws SQLException
{
 try {
  dbConn.setAutoCommit(false);
  dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  LargeObjectManager lobj = ((PGConnection)dbConn).getLargeObjectAPI();

  PreparedStatement queryStmt = dbConn.prepareStatement(
                "SELECT Values FROM MyTable WHERE From_Date < ?");
  queryStmt.setTimestamp(0, new Timestamp(deleteTo.getTime()));
  ResultSet rs = queryStmt.executeQuery();
  try {
   while (rs.next()) {
    int oid = rs.getInt(1);
    try {
     lobj.delete(oid);
    } catch (SQLException e) {e.printStackTrace();}
   }
  } finally {
    rs.close();
  }
  PreparedStatement deleteStmt
    = dbConn.prepareStatement("DELETE FROM MyTable WHERE From_Date < ?");
  deleteStmt.setTimestamp(1, new Timestamp(deleteTo.getTime()));
  deleteStmt.executeUpdate();
  dbConn.commit();
 } catch (SQLException e) {
  dbConn.rollback();
  throw e;
}

This program also seems to work, that means that I dont get any exceptions
from it.
Also, every night I run the "vacuum" command:

/usr/bin/vacuumdb -a -z

But: the data base keeps growing. The table MyTable seems to successfully
remove the entries, but the disk usage keeps high.

I am quit sure that the disk usage mainly comes from the LOBs since the disk
usage shrinks with exactly the speed I expect from the LOBs I put in.

What is wrong with my approach that it does not free the disk space?

Regards

Ole


--
NEU: WLAN-Router f�r 0,- EUR* - auch f�r DSL-Wechsler!
GMX DSL = superg�nstig & kabellos http://www.gmx.net/de/go/dsl