Knex:Error Pool2 - error: too many connections for role

[JDillon522]

@myndzi

We're having issues with our database lately. Our settings are:

{
    client: 'postgresql',
    connection: {
      database: 'honestpacket'
    },
    pool: {
      min: 2,
      max: 10
    },
    seeds: {
        directory: './seeds'
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: './migrations'
    }
  }

We are not calling knex.destroy() anywhere so we aren't explicitly destroying any connections. Here is the full stacktrace from the error:

2015-10-15T13:53:04.287266+00:00 app[web.2]: Unhandled rejection Error: Pool is destroyed
2015-10-15T13:53:04.287266+00:00 app[web.2]:     at Pool.acquire (/app/node_modules/knex/node_modules/pool2/lib/pool.js:163:12)
2015-10-15T13:53:04.287266+00:00 app[web.2]:     at /app/node_modules/knex/lib/client.js:204:19
2015-10-15T13:53:04.287267+00:00 app[web.2]:     at tryCatcher (/app/node_modules/knex/node_modules/bluebird/js/main/util.js:26:23)
2015-10-15T13:53:04.287267+00:00 app[web.2]:     at Promise._resolveFromResolver (/app/node_modules/knex/node_modules/bluebird/js/main/promise.js:480:31)
2015-10-15T13:53:04.287267+00:00 app[web.2]:     at new Promise (/app/node_modules/knex/node_modules/bluebird/js/main/promise.js:70:37)
2015-10-15T13:53:04.287268+00:00 app[web.2]:     at Client.acquireConnection (/app/node_modules/knex/lib/client.js:200:12)
2015-10-15T13:53:04.287268+00:00 app[web.2]:     at /app/node_modules/knex/lib/runner.js:138:49
2015-10-15T13:53:04.287268+00:00 app[web.2]:     at tryCatcher (/app/node_modules/knex/node_modules/bluebird/js/main/util.js:26:23)
2015-10-15T13:53:04.287269+00:00 app[web.2]:     at Function.Promise.attempt.Promise.try (/app/node_modules/knex/node_modules/bluebird/js/main/method.js:31:24)
2015-10-15T13:53:04.287269+00:00 app[web.2]:     at Runner.ensureConnection (/app/node_modules/knex/lib/runner.js:137:26)
2015-10-15T13:53:04.287269+00:00 app[web.2]:     at Runner.run (/app/node_modules/knex/lib/runner.js:30:31)
2015-10-15T13:53:04.287270+00:00 app[web.2]:     at QueryBuilder.Target.then (/app/node_modules/knex/lib/interface.js:27:43)
2015-10-15T13:53:04.287270+00:00 app[web.2]:     at QueryBuilder.tryCatcher (/app/node_modules/bookshelf/node_modules/bluebird/js/main/util.js:26:23)
2015-10-15T13:53:04.287271+00:00 app[web.2]:     at doThenable (/app/node_modules/bookshelf/node_modules/bluebird/js/main/thenables.js:52:38)
2015-10-15T13:53:04.287271+00:00 app[web.2]:     at tryConvertToPromise (/app/node_modules/bookshelf/node_modules/bluebird/js/main/thenables.js:30:20)
2015-10-15T13:53:04.287271+00:00 app[web.2]:     at Promise._resolveCallback (/app/node_modules/bookshelf/node_modules/bluebird/js/main/promise.js:442:24)
2015-10-15T13:53:04.287272+00:00 app[web.2]:     at Promise._settlePromiseFromHandler (/app/node_modules/bookshelf/node_modules/bluebird/js/main/promise.js:515:17)
2015-10-15T13:53:04.287272+00:00 app[web.2]:     at Promise._settlePromiseAt (/app/node_modules/bookshelf/node_modules/bluebird/js/main/promise.js:581:18)
2015-10-15T13:53:04.287272+00:00 app[web.2]:     at Promise._settlePromises (/app/node_modules/bookshelf/node_modules/bluebird/js/main/promise.js:697:14)
2015-10-15T13:53:04.287273+00:00 app[web.2]:     at Async._drainQueue (/app/node_modules/bookshelf/node_modules/bluebird/js/main/async.js:123:16)
2015-10-15T13:53:04.287275+00:00 app[web.2]:     at Async._drainQueues (/app/node_modules/bookshelf/node_modules/bluebird/js/main/async.js:133:10)
2015-10-15T13:53:04.287275+00:00 app[web.2]:     at Immediate.Async.drainQueues [as _onImmediate] (/app/node_modules/bookshelf/node_modules/bluebird/js/main/async.js:15:14)
2015-10-15T13:53:04.287276+00:00 app[web.2]:     at processImmediate [as _immediateCallback] (timers.js:368:17)

We're at a loss. Any help on how to debug this would be appreciated. Its especially hard since the error is intermittent. It'll be fine for a day or two with active development and then reappear and halt everything.

[JDillon522]

Update:

I've confirmed that this happens when we first get the error that there are too many connections for our role.

Knex:Error Pool2 - error: too many connections for role "<rolename>"

[tomatau]

frequently getting a similar issue here too.. not sure how to deal with it

[tjwebb]

I've also been seeing a lot of these, seems like maybe a new issue. We shouldn't have to call destroy explicitly

[tomatau]

I'm seeing these mostly on heroku - I've heard that it has a some bugs with Postgres connections.

[blah238]

@JDillon522 @tomatau @tjwebb do either of the suggestions in #975 help with this?

[tomatau]

I've been using min: 0 since before the troubles started for me. Fortunately after performing some heavy SQL query optimisations the troubles are far less frequent... but I feel as the application logic grows, they'll come back again.

[tjwebb]

@blah238 I will try and report back

[ayhoung]

just ran into this problem myself, we're performing a lot of writes. might have to move away from knex because of this problem

[tomatau]

Any new suggestions for this? Still getting Knex:Error Pool2 - error: too many connections for role even after upping to max: 16

[blah238]

@tomatau Sorry, just throwing out ideas. I don't have a good understanding of the issue here. I think we would need a detailed repro case to be able to do anything about it.

[badave]

Been having a lot of issues with this while trying to run migrations -- connecting to heroku seems to be the source of all the issues.

Knex:Error Pool2 - error: too many connections for role "databasename"
Knex:warning - Pool2 - Error: Pool was destroyed

Just all over the place.

[tomatau]

It helped for me to force sharing the knex connection instance within the same heroku deploy for different tasks and making sure it's below the free 20 connections.

[mouhong]

I encountered this error yesterday. In my case, it's because I create Knex instance every time when I need to access the database. But each Knex instance maintains a connection pool internally, and by default, each connection pool has at least 2 connections alive. Then you know how many connection pools will be created and how many connections will be open if I create knex instance every time.

The fix is simple in my case: just make knex singleton, that is, share the knex instance in the application.

@tgriesser Am I missing something? If what I mentioned above is correct, I would suggest to emphasize this in the documentation, so users will know they need to share Knex instance in the app. Or, maybe a better approach is to share connection pool inside knex.

[elhigu]

@mouhong There are various reasons why people likes to have multiple connection pools / knex instances, so making knex internally to share always the same pool would be a problem... I always thought that it was obvious that every instance has separate pool, since one can give pool configuration as a parameter for constructor.

Anyways if you have good suggestion where and how to add it to documentation, please send a pull request, I would be happy to merge it. Documentation page is index.html in project root.

[mouhong]

@elhigu but the pool configuration is optional. Starters might not be aware of it. I think it's good to have a "safe by default" design, that is, user can use a very small set of parameters to create Knex instance and then start using it without problems.

Anyway, for the doc, I think we can add a "notice" or "best practice" at the end of the Initializing the library section. But English is not my primary language and I'm not quite good at English writing. It might be a bit difficult for me to write formal docs. :(

[anzileiro]

Hello friends.
I'm studying Node.js a short time, but already went through this problem with knex.js and postgresql in
heroku.

Error does not seem so clear, but if we look well all throw stack:

I realized that heroku requires ssl to connect to postgresql, so did this:

I hope I've helped!

[jimmanu]

@mouhong did the singleton approach work for you? we're having this exact same issue with Knex and Postgress on Azure.

[brunocascio]

Updates? I'm facing the same problem on AWS Lambda + RDS

[elhigu]

Closing, Pool2 has not been used since knex ~0.11 here is some info about knex and aws lambda #1875