Provide a way to do scheduled connection validation

[grantas33]

Feature Request

Is your feature request related to a problem? Please describe

Idle connections can become invalid due to timeouts on the database or the network side or other causes. In such case, when performing a database call we potentially would have to retry the acquirement of the whole pool size of connections and re-warmup the pool if no valid connections exist. This adds a substantial amount of time to the database call, which could have been avoided if the connection validation was periodically run in the background.

Describe the solution you'd like

The implementation behind backgroundEvictionInterval configuration parameter could be extended. Instead of checking for each connection if it has exceeded its maxIdleTime, it would also check for each connection if it is valid. Also, after the checks, the connection pool should be re-warmuped, so that it contains at least minIdle connections.

This feature exists in popular JDBC pool libraries like HikariCP (keepaliveTime) and C3P0 (idleConnectionTestPeriod) and the lack of scheduled validation could be a problem when migrating to r2dbc-pool

[ifindya]

Yes, it seems to be a problem for me as well. I implemented it in my program, and I hope there
has a plan about supporting things like "keep-alive".

[mp911de]

Background validation would require taking the connection from the pool, testing it, and putting it back to ensure that the pool doesn't use it concurrently and another process that allocates the connection. In any case, that kind of functionality would be required to be provided by Reactor Pool. Feel free to file a ticket at https://github.com/reactor/reactor-pool/issues

[grantas33]

Issue was created at reactor/reactor-pool#169

[grantas33]

Background validation would require taking the connection from the pool, testing it, and putting it back to ensure that the pool doesn't use it concurrently and another process that allocates the connection. In any case, that kind of functionality would be required to be provided by Reactor Pool. Feel free to file a ticket at https://github.com/reactor/reactor-pool/issues

@mp911de reactor/reactor-pool#169 (comment) is this functionality provided by reactor-pool sufficient?

[ifindya]

Connection is removed if invalid when getting from the pool. How did this happen? Any help would be appreciated.

[mp911de]

@grantas33 the comment refers to warmup, not scheduled validations. For scheduled validation, we would need to have a scheduler and a task to allocate/de-allocate a connection from the pool. When we do this from the outside, then we have to follow the pool order and therefore, we cannot guarantee that we've verified every connection.

That being said, we require support for scheduled validation in Reactor Pool first.