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
