connection pool does not seem to evict closed connections

[chanan]

Please answer these questions before submitting your issue. Thanks!

1. What version of NodeJS are you using (node --version and npm --version)?
   16.5.1

2. What operating system and processor architecture are you using?

Linux (AWS k8s)

4. What are the component versions in the environment (npm list)?

5. What did you do?
   Created a pool per documentation, At first all is good. The longer the application is up, the more errors I get.

6. What did you expect to see?
   Pool should return valid connections

7. What did you see instead?
   Error: Error executing select [snip]. Error: Unable to perform operation using terminated connection.

8. Add this to get standard output.

sync function executeAsync(options) {
  const logStr = `${options.sqlText} params: ${options.binds}`;
  console.time(logStr);
  return new Promise((resolve, reject) => {
    pool.use(async (clientConnection) => {
      await clientConnection.execute({
        ...options,
        complete: function (err, stmt, rows) {
          console.timeEnd(logStr);
          if (err) {
            reject(err);
          } else {
            resolve({ stmt, rows });
          }
        },
      });
    });
  });
}

[manvydasu]

+1 on this.

I left the application running and now connection pool is full of "dead" connections and queries fail with:

Unable to perform operation using terminated connection.

Isn't it that one of the requirements for proper connection pool is to have proper connection management and dead connections should be renewed?

[sfc-gh-dszmolka]

i know this comment is super late and probably not useful anymore, but still putting it here in case someone stumbles upon this issue in the future, maybe it would help them.

so this behaviour is sorta-kinda expected and the closed connections are really not evicted - by default. we do document (in a way which is easy to miss :( ) that the ConnectionPool uses node-pool for implementing the pools and further information about the pool's options is available in the node-pool library documentation

Under the opts and Idle Object Eviction sections they mention that the pool does have an evictor which is off by default, so the initial observation is correct, the closed connections are really not evicted.

To address this behaviour, one could
1., create the Snowflake ConnectionPool with enabled evictor
by adding evictionRunIntervalMillis to the pool options, something like

const pool = snowflake.createPool(
    {
      account: account,
      username: username,
..rest of the connection options..
    },
    {
      evictionRunIntervalMillis: 60000 // default = 0, off
      idleTimeoutMillis: 60000, // default = 30000
      max: 2,
      min: 0,
    },
  );

would run the evictor every minute and evict any connections which is idle for more than a minute.

2. alternatively, keep alive the existing connections in the pool
with adding clientSessionKeepAlive: true (default = false) and if a keepalive is necessary more often than every hour, then clientSessionKeepAliveHeartbeatFrequency: n where n is between 900 (15m) and 3600 (1h), default being 3600.

const pool = snowflake.createPool(
    {
      account: account,
      username: username,
..rest of the connection options..
      clientSessionKeepAlive: true,  // default = false
      clientSessionKeepAliveHeartbeatFrequency: 900 // default = 3600
    },
    {
      max: 2,
      min: 0,
    },
  );

This would send a 'heartbeat' call to Snowflake every n seconds, keeping the connection alive even if there's no other activities like queries from the client. clientSessionKeepAlive of course should without using pooled connections too.

Relevant Snowflake documentation for the session keepalive can be found here.

(also I noticed that despite #377, the connector still seems to be sending select /* nodejs:heartbeat */ 1; queries to Snowflake as a heartbeat in the pool, instead of calling the appropriate endpoint, that will be handled separately from this issue)

hope this helps someone.

[sfc-gh-dszmolka]

closing this issue for now, but please reopen if you still need help with the same matter.

[Roflicide]

Hi @sfc-gh-dszmolka,

I had a few additional questions regarding using the connection pool -- I apologize if this is not the appropriate place to post them, I am happy to start a new thread if needed. For context, I am building a Node.js web app that connects to a Snowflake database, and I am using a SERVICE user with key/pair authentication to make the connection.

1. If I were to follow option 1 you specified in the earlier comment (create the Snowflake ConnectionPool with an enabled evictor), does this automatically time out the session after a set period? In other words, would I need to periodically re-authenticate my service user with the database before executing a query?
2. The same question as above applies to option 2. Would I need to periodically re-authenticate my service user with the database before executing a query?
3. Are there any design considerations you would recommend between choosing option 1 or option 2? I am new to designing this, so any tips would be appreciated.
4. I noticed you mentioned that this code is built on the generic-pool Node.js library. The sample connection pool code listed in their repository (https://www.npmjs.com/package/generic-pool) includes manually releasing connections back into the pool. However, I did not see any reference to this in the Snowflake documentation. Do I need to manually release connections, or does the code handle it for me?

Thanks for your help!