JDBC postgres vacuum timeout

Question

I'm trying to vacuum my Postgres database by running the following SQL instruction within Java:

vacuum verbose analyze

Sometimes it just seems to "hang" is there any clean way to abort the process? I have tried

SET statement_timeout TO XXXX

But I get the error message "VACCUM cannot run inside the transaction block"

Answer 1

I don't think there's a good (i.e. safe) way to kill the process other than re-starting the database. I'm not aware of any transaction timeout option either.

The best solution is to figure out what's causing the hang and fixing that problem. It's likely that vacuum is waiting for a transaction lock to be release. Use the pg_locks view to see if this is the case. If you can see what resource is being locked, you can begin to address that issue.

Answer 2

I've just tested and "vacuum" does honor "statement_timeout". Example program:

import java.sql.*;

class test
{
        public static void main(String[] args) {
                try {
                        Class.forName("org.postgresql.Driver");
                        Connection connection =
                                DriverManager.getConnection(
                                        "jdbc:postgresql://hostname/dbname",
                                        "username",
                                        "password"
                                );
                        connection.createStatement().executeUpdate(
                                "set statement_timeout to 500"
                        );
                        connection.createStatement().executeUpdate(
                                "vacuum analyze"
                        ); 
                } catch (Exception ex) {
                        ex.printStackTrace();
                }
        }
}

I get the following error:

org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299)
    at test.main(test.java:14)

Maybe you have to (temporary) enable autocommit on your connection.