Solution to prevent exceeding SESSIONS_PER_USER limit when using JDBC input pipelines

[naj-h]

When using many Logstash JDBC input pipelines, if we configure these pipelines all use the same Oracle user, one can easily go above the limit of the number of simultaneous open sessions per user on the DB.

Indeed, after upgrading from 7.17.1 to 8.6.2, one can have pipeline failures due to this error java.sql.SQLException: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

This PR (https://github.com/logstash-plugins/logstash-integration-jdbc/pull/119) was introduced in 8.6.0 (https://github.com/logstash-plugins/logstash-integration-jdbc/pull/119), and changed the how connection pools are handled on Logstash side- it seems that now the pool is only closed at pipeline stop.

The problem is that when having multiple pipelines (say 10), and the max number of simultaneous sessions for the DB user is 5, pipelines could not run and fail with the ORA error mentioned above.

Workarounds:

• Use different DB users in the pipelines
• Increase the SESSIONS_PER_USER on DB side
• If none of the above can be implemented, the left option is to switch back to 7.17.1 to get the old behaviour bac

Could we have a way to let the user configure if using pool or just single connection pool ? Where the pool would contain just one connection that's opened and closed every time is requested. This would respect the previous behavior.

And we could introduce a new setting into the plugin to enable/disable this behavior. By default it should use the pool mode, but let the user to opt-in for a connection-per-request mode