How to vacuum database tables in a Spring Boot application?

Question

My current attempt (according to this answer) looks as follows:

@Service
class VacuumDatabaseService(
        private val entityManager: EntityManager
) {
    fun vacuumAllTables() {
        val session = entityManager.unwrap(org.hibernate.Session::class.java)
        val sessionImpl = session as org.hibernate.internal.SessionImpl
        val connection = sessionImpl.connection()
        connection.prepareStatement("VACUUM FULL").execute()
    }
}

But it throws:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.IllegalStateException: No transactional EntityManager available

Annotating the function with @Transactional results in:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.reflect.UndeclaredThrowableException

Caused by: org.postgresql.util.PSQLException: ERROR: VACUUM cannot run inside a transaction block

The following works, but feels dangerously wrong:

    @Transactional
    fun vacuumAllTables() {
        val session = entityManager.unwrap(org.hibernate.Session::class.java)
        val sessionImpl = session as org.hibernate.internal.SessionImpl
        val connection = sessionImpl.connection()
        connection.prepareStatement("END TRANSACTION; VACUUM FULL;").execute()
    }

What is the correct way?

Why go through an EntityManager and a Hibernate Session just to get you a JDBC connection? Why don't you simply inject the Jdbc DataSource, get a connection out of it, do your work and close it? — JB Nizet, Apr 07 '19 at 08:20
@JBNizet Because I did not know that this is possible. :D Thanks a lot for the remark. The following works fine: `class VacuumDatabaseService(private val dataSource: DataSource) { fun vacuumAllTables() { dataSource.connection.prepareStatement("VACUUM FULL;").execute() } }` — Tobias Hermann, Apr 07 '19 at 08:26
Would you like to post this solution as an answer, so I can accept it? — Tobias Hermann, Apr 07 '19 at 08:27
You really really need to close the connection: `dataSource.connection.use { it.prepareStatement()... }` — JB Nizet, Apr 07 '19 at 08:27

score 7 · Accepted Answer · edited Apr 07 '19 at 19:42

You simply need to inject the DataSource, get a connection out of it, execute your job, and close the connection.

@Service
class VacuumDatabaseService(
        private val dataSource: DataSource
) {

    fun vacuumAllTables() {
        dataSource.connection.use {
            it.prepareStatement("VACUUM FULL").execute()
        }
    }
}

Note the usage of use, which closes the connection once the block is executed.

How to vacuum database tables in a Spring Boot application?

1 Answers1