Search code examples
javaspringpostgresqljpa

Query delay using JPA and pg_sleep in native query

trying to implement delay using pg_sleep() on PSQL side

@Query(
    nativeQuery = true,
    value = "SELECT *, pg_sleep(:delay) FROM repayment_applications " +
            "WHERE repayment_applications.correlation_id = :correlationId " +
            "AND repayment_applications.status <> :status")
Optional<RepaymentApplication> findRepaymentApplicationCorrelationIdAndNotStatusWithDelay(
    @Param("delay") Long delay,
    @Param("correlationId") String correlationId,
    @Param("status") String status);

This query perfectly works in case of querying directly to DB with specified delay. But when using JPA native query it occurs instantly without any delay.

The main task is to have a delay between queries to let other app update DB record.

Stack:

  • spring-boot-starter-parent:2.7.12
  • postgresql:42.5.1.

There is also an idea to make a thread sleep like TimeUnit.MINUTES.sleep(1);, but not sure it is a right decsision.

If there are any ideas how to make it work using current idea or using another approach to complete the task, please comment below. Thanks in advance

Solution

  • Finally found the correct decision on my problem, it looks so:

    WITH sleep AS (select pg_sleep(:delay) AS sleep), 
ids AS 
(SELECT id FROM repayment_applications 
WHERE 
repayment_applications.correlation_id =:correlationId 
AND 
repayment_applications.status <>:status) 
SELECT * FROM repayment_applications 
WHERE id IN (SELECT * FROM ids) 
AND 
(SELECT * FROM sleep) IS NOT NULL

    Works both with direct query and using nativeQuery in JPA