HikariCP w/ PostgreSQL

[ppuffi...@gmail.com]

Hello!   I came across this while working on one of my company's projects which is Spring Boot based.   We use Flyway to do database updates on PostgreSQL and one the scripts does a VACUUM on different tables.  Flyway supports this by not running the script within a transaction block, so I thought I was okay.   When the script ran it threw the exception "org.postgresql.util.PSQLException: ERROR: VACUUM cannot run inside a transaction block".   After some debugging it seemed like an issue with HikariCP.   Spring Boot's Flyway integration allowed me to set up a secondary DataSource for Flyway, so I did that to get around the issue for now.

I created a simple app to demonstrate and try different things.

public class HikariTester {

public static void main(String[] args) throws InterruptedException {

new HikariTester().run();

}

private void run() throws InterruptedException {

executeVacuum(getHikariDataSource());

Thread.sleep(1000);

executeVacuum(getPGSimpleDataSource());

}

private void executeVacuum(DataSource dataSource) {

try (   Connection connection = dataSource.getConnection();

PreparedStatement statement = connection.prepareStatement("VACUUM FULL")) {

System.out.println("Executing VACUUM for " + dataSource.getClass().getSimpleName());

statement.execute();

System.out.println("Completed VACUUM For " + dataSource.getClass().getSimpleName());

} catch (Exception e) {

e.printStackTrace();

}

}

private DataSource getHikariDataSource() {

HikariConfig config = new HikariConfig();

config.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource");

config.setAutoCommit(false);

config.setUsername("user");

config.setPassword("password");

config.addDataSourceProperty("databaseName", "test");

config.addDataSourceProperty("serverName", "localhost");

return new HikariDataSource(config);

}

private DataSource getPGSimpleDataSource() {

PGSimpleDataSource dataSource = new PGSimpleDataSource();

dataSource.setDatabaseName("test");

dataSource.setServerName("localhost");

dataSource.setUser("user");

dataSource.setPassword("password");

return dataSource;

}

}

and the output is

Executing VACUUM for HikariDataSource

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

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)

at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)

at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

at HikariTester.executeVacuum(HikariTester.java:24)

at HikariTester.run(HikariTester.java:15)

at HikariTester.main(HikariTester.java:11)

Executing VACUUM for PGSimpleDataSource

Completed VACUUM For PGSimpleDataSource

So this is without Spring, Hibernate, etc being in the way.   Is this normal for a DataSource to start a transaction?   I don't remember BoneCP or C3P0 (previous pools we've used) having problems running VACUUM and other commands.   Is there a configuration that I'm missing for HikariCP to allow me to run PostgreSQL commands that can't be ran inside a transaction?    I'm okay with the secondary DataSource just used for Flyway in our Spring apps (we really don't need a pool when running updates), but not all our applications are capable of that and want to know if we should not look at upgrading the connection pool in those.

Thanks

[Brett Wooldridge]

What you want is the isolateInternalQueries property. Also, if you happen to be using a validation query, you can remove it -- the PostgreSQL driver supports the isValid() API.

-Brett

[ppuffi...@gmail.com]

Thanks.  I've modified the above code and set this property to true and still get "VACUUM cannot run inside a transaction block" exception.   We're not using a validation query.

[Brett Wooldridge]

The difference between the two in your tests is autoCommit.  The PGSimpleDataSource defaults to autoCommit=true, while you are configuring HikariCP for autoCommit=false.  If you change the HikariCP config to be autoCommit=true as well (or simply don't set it), your test case also succeeds.

In short, yes it is common for DataSources to start a transaction when a statement is prepared.  It also makes sense that isolateInternalQueries=true does no help in this case.  It is useful in cases where SQL is executed for connection validation that has the chance of initiating a transaction.

According to the Flyway site, the limitations for PostgreSQL include:

• No support for psql meta-commands with no Jdbc equivalent like \set

I don't know if this includes VACUUM or not.

This stackoverflow.com post seems to address your exact issue.  While it is about "Redshift", "Redshift" is really just PostgreSQL.

-Brett

[ppuffi...@gmail.com]

Thanks.  That was it.   I'm not sure of the history on why autoCommit is set to false.    Removing that line lets the Flyway script run with VACUUM run successfully.    I'll run through our integration tests and see if anything bad happens.   I don't think so, because I'm pretty sure if autoCommit is true then Spring will set it to false when it needs to.