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?

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. — pirho, Oct 27 '17 at 20:13
Get the underlying connection and fall back to raw JDBC? See here for mechanism to get a connection: https://stackoverflow.com/questions/3493495/getting-database-connection-in-pure-jpa-setup — Alan Hay, Oct 28 '17 at 09:11

score 4 · Accepted Answer · edited Nov 11 '21 at 07:41

4

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

edited Nov 11 '21 at 07:41

gawi

2,843
4
29
44

answered Oct 30 '17 at 10:25

Marcel

4,054
5
36
50

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. – Marcel Mar 01 '18 at 15:09

score 1 · Answer 2 · answered Nov 11 '21 at 07:57

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

Perform VACUUM FULL with JPA

2 Answers2

Linked