Why is the query timeout ignored in PostgreSQL?

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:

1.

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

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.

This isn't really a jOOQ question but a pgjdbc one. jOOQ just passes along the information to the driver as per the Javadoc. — Lukas Eder, Nov 21 '22 at 08:27
@Dikla You definitely want to remove jooq from the equation by writing a simple SSCCE. The pgjdbc driver does some quite unexpected things on occasion, like the way driver `setSchema` calls translate to issuing SQL: https://github.com/brettwooldridge/HikariCP/issues/1633. If you can isolate and simplify an SSCCE, you'll be much more likely to get engagement from Postgres/pgjdbc team if you need to take it that far. — Shorn, Nov 21 '22 at 08:50

score 2 · Accepted Answer · answered Nov 21 '22 at 16:20

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.

Why is the query timeout ignored in PostgreSQL?

1 Answers1