spring.jpa.properties.javax.persistence.query.timeout not working for PostgreSQL

Question

I would like to have an option to limit all Spring jpa queries to a certain time limit. If a query takes longer than the specified limit it should be cancelled. From what I saw this can be achieved by using javax.persistence.query.timeout and since I am in a Spring context:

spring: 
  jpa:
    properties:
      javax.persistence.query.timeout: 10000

This should limit all queries executed from spring repositories to 30 seconds, but it's not working. This repository method takes 20 seconds:

@Query(value = "select count(*) from pg_sleep(20)", nativeQuery = true)
int slowQuery();

I tried it with Postgresql 9.6.11 and with Postgresql 10.5.

Is there some problem with these Postgres versions? Is there any other way to achieve the query execution timeout?

Generally speaking it is a query hint, if it works when defined globally kind of depends on the hibernate version you are using. It should work for Hibernate 5.4.6 and up. — M. Deinum
– M. Deinum, Commented Jan 23, 2020 at 13:36
The relevant bug is: hibernate.atlassian.net/browse/HHH-13493 Double check if you have 5.4.10. — Robert Niestroj
– Robert Niestroj, Commented Jan 23, 2020 at 14:20
If your ultimate goal is to find slow queries consider using AvailableSettings.LOG_SLOW_QUERY: docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/cfg/… — Robert Niestroj
– Robert Niestroj, Commented Jan 23, 2020 at 14:21
@RobertNiestroj I confirm that I have the version 5.4.10.Final — Noah Martin
– Noah Martin, Commented Jan 23, 2020 at 14:26

peterh · Accepted Answer · 2020-12-17 19:50:29Z

7

The following code should be a good alternative.

YAML file:

spring:
    jpa:
        properties:
            javax:
                persistence:
                    query:
                        timeout: 10000

edited Dec 17, 2020 at 19:50

peterh

1

answered Dec 17, 2020 at 17:34

Mauricio Hartmann

1191 silver badge3 bronze badges

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

1 Comment

Paul Over a year ago

can confirm that for a SpringBoot project you'd add the following line to your application.properties: spring.jpa.properties.javax.persistence.query.timeout=10000. Take note that the timeout is defined in millis. Should be applicable to most DB's.

tariksahin4391 · Accepted Answer · 2025-01-02 06:38:44Z

-1

You should also add the suitable property depends on your database.

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

answered Jan 2 at 6:38

tariksahin4391

1

Collectives™ on Stack Overflow

spring.jpa.properties.javax.persistence.query.timeout not working for PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related