Allow prepared statements to persist across returning a connection to the pool

[andrewsensus]

Expected Behavior

When returning a connection to the pool, the connection is reset. This invalidates any prepared statements. It is desired to prepare statements and continue using them if the same connection is re-acquired from the pool. In the case of certain errors (in tedious or in code using the pool), it is also desired to allow a connection to be reset.

To accomplish this, an additional parameter can be passed to Connection.release(suppressReset). When suppressReset is not passed (old behavior) or passed with a falsy value, reset the connection as was previously done. When suppressReset is passed with a truthy value (new behavior), return the connection to the pool without resetting it.

Current Behavior

When a truthy argument is passed to Connection.release, return the connection to the pool WITHOUT resetting it.

Steps to Reproduce (for bugs)

N/A

Reason For Request (for feature requests)

I've seen a notable performance improvement for certain queries when running them as prepared statements, but resetting the connection invalidates prepared statements.

Possible Solution

see SensusDa/tedious-connection-pool@35b4417ae9b6517c5bbdfbc6b2a9a21ef6a49f85

Background Details

Running code which used prepared statements and a custom version of tedious-connection-pool with changes as shown in the commit above resulted in a notable performance improvement.

Environment

N/A

[arthurschreiber]

I’m not sure how feasible this request is. 🤔 prepared Statements can not be used across different connections, so this will only work if the same connection is used. But that can’t be guaranteed when using a pool with more than one connection that is managed. And if you’re only using a single connection, why would you want to use a pool at all?

I’m sorry I don’t quite understand your use case here, would you mind to explain it a bit further? 🙇🏻‍♂️

[andrewsensus]

@arthurschreiber I use code with the "Possible Solution" (plus a tweak to allow the version of tedious to be specified at runtime). The caller of the connection pool uses

  var metaInfoByConnection = new WeakMap();

metaInfoByConnection is a WeakMap which maps connections to Maps containing the meta-data needed for tracking which statements have been prepared (as well as anything else I need to track about how the connection is currently being used). Once I'm done with the connection, it is returned to pool. If the pool decides to stop using the connection for whatever reason, WeakMap allows the connection to be garbage-collected. If I decide to reset the connection when returning it to the pool (I do that after various errors), then I also remove the connection's entry from metaInfoByConnection.

Does that make my use case clearer?