biotext.org.uk

I hit upon a snag today — while PostgreSQL requires that certain maintenance commands (e.g. vacuum analyze) are executed outside of a transactional context, it’s actually quite hard to get at Hibernate’s underlying database connection directly. Each Session object has a connection() method which returns a JDBC connection object, but this actually turns out to be a Hibernate-generated proxy for the real connection object, which refuses to work outside of a transaction.

You can probably get around this by using autocommit mode instead of explicit transactions, but this is a configuration property that affects your whole application, and is considered harmful.

Eventually I hit on a really dodgy workaround. You can manually rollback the transaction at the start of your SQL statement the old-fashioned way:

<sql-query name="nasty.hack">
<![CDATA[
rollback transaction; vacuum analyze;
]]>
</sql-query>

This also works when used with the prepareStatement() method as shown here.

Frightening but effective. If you know of a better way, let me know!

Andrew.

When was my database last vacuumed or analyzed? This took me a while to figure out from googling around so I’ll put it here for posterity, short and sweet.

select relname, last_vacuum, last_analyze
from pg_stat_all_tables
where schemaname = 'public'

Add the columns last_autovacuum or last_autoanalyze if you use those features.

Andrew.