Perform VACUUM FULL with JPA

Question

I'm using a PostgreSQL DB and I would like to start VACUUM FULL using JPA EntityManager.

Version 1

public void doVacuum(){
  entityManager.createNativeQuery("VACUUM FULL").executeUpdate()
}

throws TransactionRequiredException

Version 2

@Transactional
public void doVacuum(){
  entityManager.createNativeQuery("VACUUM FULL").executeUpdate()
}

throws PersistenceException "VACUUM cannot run inside a transaction block"

Version 3

public void doVacuum(){
  entityManager.createNativeQuery("VACUUM FULL").getResultList()
}

vacuum is performed but after that I get PersistenceException "No results"

What is the correct way to start this sql command?

Spinose

Is this really something that should be done in JPA scope? I do not mean it is bad thing but is it not some DBA thing? Are you trying to develop DBA-tool using JPA? But still interesting aspect. – Girish

Get the underlying connection and fall back to raw JDBC? See here for mechanism to get a connection: #3493995 – Baseless

Answer 1

As Alay Hay mentioned, using the underlying connection will work:

public void doVacuum(){
  org.hibernate.Session session = entityManager.unwrap(org.hibernate.Session);
  org.hibernate.internal.SessionImpl sessionImpl = (SessionImpl) session;  // required because Session doesn't provide connection()
  java.sql.Connection connection = sessionImpl.connection();
  connection.prepareStatement("VACUUM FULL").execute();
}

Spinose

This depends on how you handle your database connection or which framework you're using. I'm currently using Spring so an annotated field "@PersistenceContext private EntityManager enitityManger" is enough to get it injected. – Spinose

Answer 2

Here is a solution that does not require cast to internal implementation of Hibernate Session. Please keep in mind that VACUUM cannot be run in transaction block, this is why you need to set autoCommit to true.

Session session = entityManager.unwrap(Session.class);
session.doWork(new Work() {
  @Override
  public void execute(Connection connection) throws SQLException {
    connection.setAutoCommit(true);
    connection.prepareStatement("VACUUM FULL").execute();
    connection.setAutoCommit(false);
  }
});

Amabil

Answer 3

I'm sharing two versions of working example based on other answers.

    static int vacuum1(final EntityManager em) throws SQLException {
        final var session = em.unwrap(Session.class);
        assert session instanceof JdbcSessionOwner;
        final var access = ((JdbcSessionOwner) session).getJdbcConnectionAccess();
        final var connection = access.obtainConnection();
        try (var statement = connection.prepareStatement("VACUUM")) {
            return statement.executeUpdate();
        }
    }

    static void vacuum2(final EntityManager em) {
        final var session = em.unwrap(Session.class);
        session.doWork(c -> {
            try (var statement = c.prepareStatement("VACUUM")) {
                try {
                    final var result = statement.executeUpdate();
                    assert result == 1;
                } catch (final SQLException sqle) {
                    throw new RuntimeException(sqle);
                }
            }
        });
    }

Slink