No response from the database when same SQL statement is sent to the database multiple times #658
Description
- Driver: r2dbc-postgresql 1.0.5
- Database: 15.6
- Java: 17
- OS: Windows
Current Behavior
We decided to use r2dbc in our new new project what required porting some of our existing blocking code onto a reactive stack. This included paging functions - these were rewritten to preserve the semantics from the traditional model. Instead of creating Fluxes and reading items to page limit, the SQL query is written to use LIMIT with OFFSET.
We noticed that that the query would always time out while fetching the 7th page.
Solution
It turned out that 'salting' the SQL statement to make it unique solves the issue. As a workaround we wrap all queries we send to the database into a sub query:
alias = now()
SELECT (a + alias).* FROM (SQL)as (a + alias)
The above fixes the problem - all our queries now return without timeout as expected, even if 10s of pages are involved.
This however seriously undermined our confidence in our new stack. We cannot tell for sure if the problem is with the driver, the DatabaseClient Spring class, or the database. The solution to the problem we found was more of a luck rather than anything else, neither the driver nor the database would offer any clues as to which end was causing the behaviour. We have taken a guess that the database is responding with data but the driver fails to notice the response therefore triggering the statement timeout exception.
I will try to rewrite our code to provide a reproduction here when I have more time.