HSQLDB - Automatically perform CHECKPOINT after DELETE

Question

To avoid having the .lob file grow I understand that it's necessary to execute a CHECKPOINT after deleting rows in tables with LOB columns.

I tried to automate this in the following ways:

• Looking at the documentation for an option that does this but couldn't find any. There is SET FILES LOG SIZE but it's quite different from what I'd like to have.

• Creating a trigger like this:

  CREATE TRIGGER MY_TRIGGER AFTER DELETE ON MY_TABLE  
    BEGIN ATOMIC  
      CHECKPOINT;  
    END
  

  but HSQLDB complains that CHECKPOINT isn't a valid token.

Any suggestions?

Answer 1

HSQLDB does not allow system commands such as CHECKPOINT in trigger code.

The space for deleted LOBs is freed at checkpoint and is reused later.

You can have the checkpoint performed more frequently by reducing the log size settings. The minimum setting, 1, results in an automatic checkpoint when the log size reaches 1 MB.

SET FILES LOG SIZE 1

As 1 MB of logged DDL statements may result in a large growth in the size of the .lobs file, the alternative is to count the executed DELETE statements and issue the CHECKPOINT from your app when the count reaches a threshold.