CODEWITHSUNDEEP
javapostgresqljpavacuum
Marcel
Marcel

Reputation: 4402

Perform VACUUM FULL with JPA

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?

Upvotes: 6

Views: 3406

Answers (3)

Jin Kwon
Jin Kwon

Reputation: 21996

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);
                }
            }
        });
    }

Upvotes: 0

gawi
gawi

Reputation: 2952

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);
  }
});

Upvotes: 2

Marcel
Marcel

Reputation: 4402

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();
}

Upvotes: 5

Related Questions