Postgres - why is query timeout ignored?-CodePudding

EDIT - this is not related to Jooq, see details in EDIT section after the original question.

I run a query against PostgreSQL DB using Jooq. Since I know this query will take a long time, I try to set the query timeout. I tried several ways, but each time the result is that the query timeout is ignored and the query fails after a minute (which is the default timeout for this DB) with the error: canceling statement due to statement timeout. Why is this, and how can I set the query timeout?

Here are the ways I tried to set the TO:

DSL.using(dataSource, SQLDialect.POSTGRES, new Settings().withQueryTimeout(600))
    .deleteFrom(...)
    ...
    .execute();

DSL.using(dataSource, SQLDialect.POSTGRES)
    .deleteFrom(...)
    ...
    .queryTimeout(6000)
    .execute();

DSLContext transactionContext = 
    DSL.using(dataSource, SQLDialect.POSTGRES, new Settings().withQueryTimeout(600));
transactionContext.transaction(configuration ->
{
    DSL.using(configuration).deleteFrom(...)
        ...
        .execute();
});

EDIT

I was told that this is not related to Jooq, so I made the following test:

import org.apache.commons.dbcp2.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
...
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbURL, username, password);
Connection connection = connectionFactory.createConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select pg_sleep(80)");
preparedStatement.setQueryTimeout(120);
preparedStatement.execute();

This fails after a minute with the same timeout error. So the problem is indeed not related to Jooq.

connection is of type org.postgresql.jdbc.PgConnection.

preparedStatement is of type org.postgresql.jdbc.PgPreparedStatement.

CodePudding user response：

Those do different things. statement_timeout causes the database server to cancel the query on its own volition based on PostgreSQL's timer, while setQueryTimeout() causes Java to initiate the cancelation based on Java's timer (by opening a separate purpose-specific connection to the database and sending a cancel request). Since they are different mechanisms, one doesn't countermand the other.

To countermand the server setting, you would need to do something like execute the statement set local statement_timeout=120000;. There may be other ways to change the server settings, but setQueryTimeout() is not one of them.