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
– Lukas Eder, Commented Nov 21, 2022 at 8: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: 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
– Shorn, Commented Nov 21, 2022 at 8:50

jjanes · Accepted Answer · 2022-11-21 16:20:56Z

2

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.

answered Nov 21, 2022 at 16:20

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is the query timeout ignored in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related