There are certain situations (possibly involving Heroku) where one may want access to the sslfactory parameter of the connection string. For instance, this connection string 'postgres://username:password@host:5432/yourdbname?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory'
allows for ssl access without explicit client side certificate setting (but does expose certain things for potential MITM attacks). I know you can connect to client with a pure string that I could probably add to this manually, but there does not seem to be a way to do this with the pool since it constructs the connection from a config object?

When using pool.connect in the callback form, done(client) and client.release() do not behave in the same way.

If the pool is fully utilized and there are .connect requests waiting for a client, done(client) does not seem to allow those waiting requests to complete. client.release() does.

Using the following code:

'use strict';

const pg = require('pg');

const databasePool = new pg.Pool({
	/* Other connection params here */
	max: 2,
});

let requested = 0;
let retrieved = 0;
let returned = 0;
for (let i = 0; i < 5; i++) {
	requested += 1;
	console.log(`Requested ${i} (${requested}/${retrieved}/${returned})`);
	databasePool.connect((err, client, done) => {
		retrieved += 1;
		console.log(`Retrieved ${i} (${requested}/${retrieved}/${returned})`);
		if (err) {
			returned += 1;
			done(client);
			// client.release();
			console.log(`Returned ${i} (${requested}/${retrieved}/${returned})`);

			return console.log(err);
		}

		client.query('select pg_sleep(1)', [], (err) => {
			returned += 1;
			done(client);
			// client.release();
			console.log(`Returned ${i} (${requested}/${retrieved}/${returned})`);
			if (err) {
				console.log(err);
			}
		});
	});
}

I get the following output. Notice that, even after the two connections are returned with done(client), the other requested connections to not ever get a chance.

Requested 0 (1/0/0)
Requested 1 (2/0/0)
Requested 2 (3/0/0)
Requested 3 (4/0/0)
Requested 4 (5/0/0)
Retrieved 0 (5/1/0)
Retrieved 1 (5/2/0)
Returned 1 (5/2/1)
Returned 0 (5/2/2)

However, if I switch to client.release(), I get the following output. Notice that once a connection is returned, the connection is available for another request, as expected.

Requested 0 (1/0/0)
Requested 1 (2/0/0)
Requested 2 (3/0/0)
Requested 3 (4/0/0)
Requested 4 (5/0/0)
Retrieved 0 (5/1/0)
Retrieved 1 (5/2/0)
Retrieved 2 (5/3/1)
Returned 0 (5/3/1)
Retrieved 3 (5/4/2)
Returned 1 (5/4/2)
Retrieved 4 (5/5/3)
Returned 3 (5/5/3)
Returned 2 (5/5/4)
Returned 4 (5/5/5)

Environment

Mac OS X 10.11.6

$ node -e 'console.log(process.versions)'
{ http_parser: '2.7.0',
  node: '6.8.1',
  v8: '5.1.281.84',
  uv: '1.9.1',
  zlib: '1.2.8',
  ares: '1.10.1-DEV',
  icu: '57.1',
  modules: '48',
  openssl: '1.0.2j' }

$ npm list
[email protected]
└─┬ [email protected]
  ├── [email protected]
  ├── [email protected]
  ├── [email protected]
  ├─┬ [email protected]
  │ ├── [email protected]
  │ └── [email protected]
  ├─┬ [email protected]
  │ ├── [email protected]
  │ ├── [email protected]
  │ ├── [email protected]
  │ ├── [email protected]
  │ └── [email protected]
  ├─┬ [email protected]
  │ └── [email protected]
  └── [email protected]

I think there's an issue with the logic for handling connection timeouts with a full connection pool.

https://github.com/brianc/node-pg-pool/blob/master/index.js#L164:

// set connection timeout on checking out an existing client
const tid = setTimeout(() => {
    // remove the callback from pending waiters because
    // we're going to call it with a timeout error
    this._pendingQueue = this._pendingQueue.filter(cb => cb === response.callback)
    response.callback(new Error('timeout exceeded when trying to connect'))
}, this.options.connectionTimeoutMillis)

On line 164, the intent is to remove the response.callback from this._pendingQueue, because it's going to be handled right now. However, it seems like it actually removes every callback but response.callback from the queue. Is this right?

During my testing I enabled verbose logging from both pg-pool and generic-pool. I noticed when calling client.release(err); or calling pool.query('error producing query');, generic-pool complains of releasing an unknown object.

Adding pool.pool._factory.log = true;(to enable all generic-pool logging) to the pool with promises recovers from all errors test case produces the following error:

ERROR pool undefined - attempt to release an invalid resource: Error
    at Pool.release (.../node-pg-pool/node_modules/generic-pool/lib/generic-pool.js:416:61)
    at null.<anonymous> (.../node-pg-pool/index.js:69:19)
    at null.callback (.../node-pg-pool/index.js:89:9)
    at Query.handleError (.../node-pg-pool/node_modules/pg/lib/query.js:123:17)
    at null.<anonymous> (.../node-pg-pool/node_modules/pg/lib/client.js:171:26)
    at emitOne (events.js:77:13)
    at emit (events.js:169:7)
    at Socket.<anonymous> (.../node-pg-pool/node_modules/pg/lib/connection.js:109:12)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:153:18)
    at Socket.Readable.push (_stream_readable.js:111:10)
    at TCP.onread (net.js:531:20)

I believe that this is caused by the implementation of client.release that calls both this.pool.destroy and this.pool.release.

I try to transfer my code to the new pg-pool.

In my code i have used the query from a client as EventEmitter.
This is not supported by pool.query. Do you plan to introduce that to, or is it a problem with the pooling ?
(So should I refactor my code, or wait for a pg-pool update ?)

Hi,

It seems that only pool-max-size errors can be caught when using pool.connect() function . following is a test case:
pg host is fake data and can not connect to . I thought output should catch 10 ETIMEDOUT errors, but actually it turns out catch only 3 which is pool-max-size, after changing max to 10 all errors can be caught. Is that means pool.connect() can only catch pool-max-size errors ? if so, how to make sure pool.connect() get client successfully or just pending because connections are larger than pool-max-size?

Thanks a lot !

const Pool = require('pg-pool')
// fake host
let config = {
  host: '172.18.0.15',
  port: 5432,
  user: 'postgres',
  password: 'postgres',
  database: 'postgres',
  max: 3,
  min: 1,
  schema: 'public'
};

const pool = new Pool(config)
pool.on('error', (err) => {
  console.error('An idle client has experienced an error', err.stack);
});

const query = () => {
  return pool.connect().then((client) => {
    console.log('get client, then do nothing');
    return 'OK';
  }).catch((err) => {
      console.error('catch an error! ');
      // console.log(err);
    });
};

for(let i = 0; i < 10; i++) {
  query();
}

result:

catch an error! 
catch an error! 
catch an error! 

    const client = await pool.connect()

This line always get stuck especially when used in conjunction with queue management such as Automattic/kue

I really don't know how to debug this but whenever I use the recommended design to only create one pool and then do the pool.connect() then client.release(), the pool.connect always start to get stuck after leaving it running for a couple hours.

However if I don't use a global pool and instead create a new one on every new task, it will never get stuck.

I have a lib/db.js file that contains:

var pgp = require('pg-promise')();

var dbModule = module.exports;

dbModule.db = null;

dbModule.getDatabaseConnection = function() {
	if (!dbModule.db) {
		dbModule.db = pgp(...);
	}

	return dbModule.db;
};

Then, I require this module in multiple other files that help server REST API calls:

var dbModule = require('db.js');

return dbModule.getDatabaseConnection().one(sql.text, sql.values);

I don't ever free the connection/close it when I'm not using it, since it's supposed to act as the one main connection for the entire app to use.

Would I see performance benefits if I used to pg-pool? Unless I am misunderstanding, I only have one global connection, not a pool given my current setup, and all queries happen sequentially, in a blocked fashion? By switching to a pool, multiple connections would get round-robined and there would be less blocking?

If I am wrong, please let me know. Thanks!

I am configuring a Pool with Bluebird as the promise library for use in an AWS Lambda handler:
const pool = new Pool({...config, Promise: Promise});

My handler makes 3 asynchronous calls to the database. If the calls fail, although I have a catch block in my Promise chain, and although the catch block catches and handles the error, the Node process exits regardless.

return makeDbCall(params)
.then((dbResult) => {
// do good stuff
})
.catch((dbError) => {
// handle error, return Promise.resolve
});

Is it possible to stop the error from the query from killing my Node process?

I've tried something like:

query(sql, bind, (error, result) => {
if (error) {
return;
// return Promise.resolve(error);
// return Promise.reject(error);
}
return Promise.resolve(result);
});

But the error still propagates and kills the Node process.

Context

To work with a transaction, we get client

let client = await pool.connect();

After we done, client.release() should be called. If we call client.release(err), client will be destroyed (or so it seems).
Usual (non prepare-d) transaction is alive as long as postgres' session is alive. Session is associated with client connection.

Question

Can client.release(err) be used as a sure way to rollback?
If so, it allows for simple pattern, without ever writing explicit rollback:

try {
   client.query('BEGIN');
   ....
   // throw if conditions are not met
   ....
   client.query('COMMIT');
   client.release();
} catch (err) {
   client.release(err);  // kills transaction with client's connection
   throw err;  // for dealing elsewhere
}

Of cause, this isn't for places were rollback conditions are common.

1. Can this be done?
2. Or, is it too heavy on resources even for infrequent rollbacks?

Hi Brianc,

I just realized that a log option exists in pool config, here :

I'd like to use pg-pool to work with transactions, where I run a BEGIN followed by a few SQL statements then a COMMIT/ROLLBACK. But I'd like to do this with async/await, taking advantage of pg-pool's use of promises. Could you please provide any examples of the proper way to do this with pg-pool?

We call end() on the pool in order to shut down all the connections within the pool.

However, doing so prevents us from being able to use the object any longer.

Any attempt of calling .connect after .end() was called once results in the following error:

pool is draining and cannot accept work

Below is a complete test application:

'use strict';

var pg = require('pg');

var config = {
    database: 'newone',
    port: 5433,
    user: 'postgres'
};

var pool = new pg.Pool(config);

function test1() {
    pool.connect((err, client, done) => {
        if (err) {
            console.log('Error Connecting:', err);
            return;
        }
        client.query('select count(*) from users', (err, data) => {
            if (err) {
                console.log('Query Error:', err);
            } else {
                console.log('DATA:', data.rows);
            }
            done();
            pool.end();
            test2();
        });
    });
}

function test2() {
    pool.connect((err, client, done) => {
        if (err) {
            console.log('Error Connecting:', err);
            return;
        }
        client.query('select count(*) from users', (err, data) => {
            if (err) {
                console.log('Query Error:', err);
            } else {
                console.log('DATA:', data.rows);
            }
            done();
            pool.end();
        });
    });
}

test1();

test1 ends successfully, but in test2 it always throws that error on this line:

console.log('Error Connecting:', err);
//=> pool is draining and cannot accept work

Hi there,

nice work with the pg-pool.

just a thought:

for getting the number of active clients you suggest keeping a counter on the connect callback.
I suggest counting the number of active clients via something like:

showConnections() {  
        logger.notice('PgPool.openConnections: ', {rw: this.pool._clients.length});
        setTimeout(_.bind(this.showConnections, this), 30000); 
}

(b.t.w: I'am using the pool in an es2015 wrapper class. so don't mind the this.pool it is an instance of the pg-pool)

... just a thought.

keep up the good work

chiluap

I am getting this error when running a simple pool test in a serverless environment. Everything works with unit tests, even testing the Lambda promise:

TypeError: (0 , _dbConnection2.default) is not a function

My handler:

import {connection, pool} from '../src/dbConnection'
import lib from '../lib/index'

module.exports.handler = (event, context, callback) => {
  return connection().then(result => {
    callback(null, lib.lambdaProxyResponse(200, { connection: result.rowCount > 0 }))
  }).catch(err => {
    callback(null, lib.lambdaProxyResponse(400, { error: err }))
  })
}

My test shows first a non-pool connection. Then a pool connection

/src/dbConnection.js

import pg from 'pg'
import db from '../lib/db'

var config = {
  user: 'user',
  database: 'db_name',
  password: 'password',
  host: 'path-to-db',
  port: 5432,
  max: 10,
  idleTimeoutMillis: 30000
}

export const connection = () => {
  return new Promise((resolve, reject) => {
    var client = new pg.Client(config)
    client.connect(function (err) {
      if (err) {
        reject(err)
      }
      client.query('SELECT 1', function (err, result) {
        if (err) {
          reject(err)
        }
        resolve(result)
      })
    })
  })
}
export const pool = () => {
  return new Promise((resolve, reject) => {
    db.query('SELECT 1', function (err, result) {
      if (err) {
        reject(err)
      }
      console.log(result)
      resolve(result)
    })
  })
}

../lib/db.js

import pg from 'pg'

var config = {
  user: 'user',
  database: 'db_name',
  password: 'password',
  host: 'path-to-db',
  port: 5432,
  max: 10,
  idleTimeoutMillis: 30000
}

const pool = new pg.Pool(config)

pool.on('error', function (err, client) {
  console.error('idle client error', err.message, err.stack)
})
module.exports.query = function (text, values, callback) {
  // doesn't seem to be connecting in a lambda env.?
  return pool.query(text, values, callback)
}
module.exports.connect = function (callback) {
  return pool.connect(callback)
}

Tests once deploy for both a straight connection test and the pool test fail with that error.

Questions:

• Is there any permissions I need to set for my lambda functions in the security groups?
• With my db.js will that pool be run outside of my lambda handler? DO I need to structure it different so it warms up correctly?

Cheers

I have an Express app and need to use SSPI to connect to PG. Currently, I'm using password authentication, but this is against company policy. (Because of the need to store the password).

I noticed that if I continue making use of a client reference after calling done(), like so:

pool.connect(function (err, client, done) {
  if (err) {
    return console.error('error fetching client from pool', err);
  }
  function execute(input, cb) {
    client.query('SELECT $1::int AS number', [input], function (err, result) {
      console.log('Current number of connections in use: %s', pool.pool._inUseObjects.length);
      console.log('Current number of available connections: %s', pool.pool._availableObjects.length);
      process.nextTick(function() {
        return cb(err, result);
      });
    });
  }

  async.each([1, 2, 3], function (i, cb) {
    execute(i, function(err) {
      if (err) console.error('Oh noes: %s', err);
      done();
    });
  });
});

...that it would release the items to the pool, but still make use of them:

Current number of connections in use: 1
Current number of available connections: 0
Current number of connections in use: 0
Current number of available connections: 1
Current number of connections in use: 0
Current number of available connections: 1

I would still expect a user of this library to be careful about reusing connection instances that they've released to the pool, but if you were to use an intermediate object reference that prevents direct access to those connection instances, you could invalidate those references by setting a flag on client.release() that prevents people from calling things like client.query(...).

If query throws error, then client not returning to connection pool. Example:

const pool = new pg.Pool({ max: 1 })

try {
	await pool.query(`invalid sql`)
} catch (e) {
	console.log(e)
}

await pool.query('Another query') // Never ends

I encountered an issue in my software wherein connections would be left open, thus reaching the PostgreSQL connection limit for that user after a few runs.

My code basically did this:

1. open a VPN connection;
2. connect to a database, fetch data;
3. terminate the pool (wait for the returned promise);
4. close the VPN connection.

As it turned out, pg-pool was unable to properly terminate idle connections, because the VPN tunnel was closed before necessary signals could be sent. I could solve the issue by waiting an extra second between points 3) and 4), so I suspect the issue lies on these lines: 91 and 135. Namely, client.end() returns a promise that resolves only once it receives the 'end' signal, but this promise is not forwarded to _pulseQueue(), which reports the pool as properly closed as soon as it simply requests the closing of all idle connections.

For example, I was running the following query:

const query =
  `
  SELECT statefp, name, stusps
  FROM my_table as tb
  WHERE st_contains(tb.geom, st_geomfromtext('POINT($1 $2)', 4269))
`
const states = await client.query(
 query,
 [ longitude, latitude ]
)

which gave me the following error:

{ error: bind message supplies 2 parameters, but prepared statement "" requires 0
    at Connection.parseE (/home/lucas/projects/docker-projects/political-areas/node_modules/pg/lib/connection.js:546:11)
    at Connection.parseMessage (/home/lucas/projects/docker-projects/political-areas/node_modules/pg/lib/connection.js:371:19)
    at Socket.<anonymous> (/home/lucas/projects/docker-projects/political-areas/node_modules/pg/lib/connection.js:114:22)
    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
    at readableAddChunk (_stream_readable.js:253:11)
    at Socket.Readable.push (_stream_readable.js:211:10)
    at TCP.onread (net.js:585:20)
  name: 'error',
  length: 130,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1556',
  routine: 'exec_bind_message' }

It's as if my query wasn't accepting any parameters, but as we can clearly see above, it takes the $1 and $2 parameters. After about an hour of trying to figure out what went wrong, I realized that my parameters are inside of a quoted string (ie 'POINT($1 $2)'). Perhaps this is a bug, or a feature, where params inside of a quoted string are not parsed by pg-pool's client?

I think this strange behavior should at least be documented. If so, I'm happy to help with that.

In the end, the following refactor fixed it:

const query =
  `
  SELECT statefp, name, stusps
  FROM my_table as tb
  WHERE st_contains(tb.geom, st_geomfromtext('POINT(${longitude} ${latitude})', 4269))
`
const states = await client.query(
 query
)

It's great to see the flurry of activity around node-pg at the moment, and we're big fans of the progress being made. However, dropping out generic-pool leaves one significant problem with the pool in its current form.

PG pools should, for maximum performance, tend to the lowest number of most active clients. Generic Pool's FIFO option allowed you to minimise the number of connections, helping you to ensure that you keep them hot, reducing load on the main server or pgbouncers, and for a somewhat sleepy application improving performance.

Would you consider a PR to restore a stack-based functionality?

Hi,

Before: This is not a bug in pg-pool, this is a problem in my test scripts.

I am using pg for a long time and have no switched to the new version using pg-pool.

In my mocha test environment I got very often the error message:

{ message: 'Connection terminated by user' }

depending on which time I put in this configuration

connectionTimeoutMillis: 500,
idleTimeoutMillis: 500

After reducing the problem, the source was found, it was sinon.useFakeTimers in parallel with pg-pool

Ok, that a pool, that is managed by timeout, and a "stopped" timer are clashing is not really a surprise.

Does anyone has a hint how i can get working "simulated time" and pg-pool together ?

Hi Brianc,
I have just a concern about how to specify the max size (min size) of Pool, mean an optimal number.
I know it depends on the server configuration, but can you give me some hints.

Thanks

I am trying to gracefully stop my postgres db on process.on('SIGINT', handler) in my main index.js.

With Node Postgres, I am using a pool with the 4 clients. After reading the docs titled shut it down for closing a pool, I need to first release the clients.

From the exported pool itself(module.exports.pool = pool;) , how do I access the clients in a pool to do this?

This is how I initiate my postgres db, create the pool, and export it to other modules.

const colors = require('colors');
const pg = require('pg');

if (process.env.DOLPHIN_TEST) {
  var dbName = 'test_dolphin_db';
  var maxCon = 1
} else {
  var dbName = 'dev_dolphin_db';
  var maxCon = 4
}

const config = {
  user: 'postgres',
  database: dbName,
  host: 'localhost',
  max: maxCon,
  idleTimeoutMillis: 30000
}

const pool = new pg.Pool(config);

function poolConnected(err, client, done, resolve) {
  if(err) {
    return promiseArgs.reject(`error fetching client from pool ${err}`);
  }
  client.query('SELECT $1::int AS number', ['1'], function(err, result) {
    //call `done()` to release the client back to the pool
    done();

    if(err) {
      return console.error('error running query', err);
    }

    console.log(colors.grey(
      `Postgres is online using ${client.database} as user ${client.user}\n` + 
      `and is listening on ${client.host}`));
    promiseArgs.resolve();
  });
}

var promiseArgs = { resolve: undefined, reject: undefined };  

function initDb() {
  var promise = new Promise(function(resolve, reject) {
    promiseArgs.resolve = resolve;
    promiseArgs.reject = reject;

    pool.connect(poolConnected);

    pool.on('error', function (err, client) {
      // if an error is encountered by a client while it sits idle in the pool
      // the pool itself will emit an error event with both the error and
      // the client which emitted the original error
      // this is a rare occurrence but can happen if there is a network partition
      // between your application and the database, the database restarts, etc.
      // and so you might want to handle it and at least log it out
      console.error('idle client error', err.message, err.stack)
    });
  });
  return promise;
}

module.exports.pool = pool;
module.exports.initDb = initDb;

I am trying to create a method for a synchronous query. I'm using pg-pool and asyncawait, but I throw the following error: "Error: await functions, yield functions, and value-returning suspendable functions may only be called from within a suspendable function."

The code is:

CustomPool = function(condigDB){
 this.pool = new Pool(cofigDB)
};

CustomPool.prototype.querySync = function(sql){
    var result = {rows:[]};
    try{
        result = await(this.pool.query(sql));
    }
    catch(_e_){
        Log.exception(_e_);
    }
    return result.rows; 
};

module.exports = CustomPool;

Hi, I am using pool in express node js for my RestAPI

var pool = new Pool(config)
function listDetails(req, res) {
  pool.connect().then(client => {
        client.query(sql_str, 
            [userId])
            .then(ressult => {
          client.release()

          var resp = new Object();
          var jsonArr = []; // Populate the result

          var data = result.rows;

          for (var i = 0; i < data.length; i++) {
              var jsonObj = new Object();
              jsonObj.game_id = data[i].game_id;
              jsonObj.record_id = data[i].record_id;
      
              jsonArr.push(jsonObj);
          }
      
          resp.details = jsonArr;
      
          res.json(resp);
        })
        .catch(e => {
          client.release()
          console.error('query error', e.message, e.stack)
        })
      })

I receive the error :

(node:19391) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 2): Error: Release called on client which has already been released to the pool.

Any suggestion is appreciated

Pool constructor changes given options parameter https://github.com/brianc/node-pg-pool/blob/master/index.js#L13-L15
This doesn't work with frozen object. Next program terminates without error:

'use strict';

var Pool = require('pg').Pool;

var config = Object.freeze({
  user: 'foo',
  password: 'secret',
  database: 'my_db',
  port: 5432
});

var pool = new Pool(config);

pool.connect(function(err, client, done) {
  if(err) {
    return console.error('error fetching client from pool', err);
  }
  console.log('done');
});

Also changing user input isn't good practice, because user can use options object later. So, I propose to clone options before adding new properties to it.

pg-pool/index.js:217: err.message = 'Connection terminiated due to connection timeout'

Hi there,

so far very happy with pg-pool.
I just noticed that if I have persistent connections and the PostgreSQL is unavailable (due to connection loss) no event is emitted to pool.on('error', ...).

I wrapped pg pool in a es2016 class to create a simple singleton-pattern.
Am I doing something wrong here?
Here is my code:

import pg from 'pg';
import _ from 'lodash';
// usage:   import pgPool from 'PgPool.js';
// then use as expexted

class PgPool {
    constructor() {
        this.connectCount = 0;
        this.acquireCount = 0;
        // TODO: load connection settings from elsewhere
        this.config = {
            database: 'some_db',
            user: 'some_user',
            password: 'some_pwd',
            host: 'some_ip',
            port: 1234,
            ssl: false,
            max: 50,
            min: 4,
            idleTimeoutsMillis: 1000,
        };
        // Stuff we want to do
        this.pool = new pg.Pool(this.config);
        this.pool.on('connect', _.bind(this.onConnect, this));
        this.pool.on('acquire', _.bind(this.onAcquire, this));
        this.pool.on('error', _.bind(this.onError, this));
    }

    query(queryString = '', substVars = []) {
        return new Promise((resolve, reject) => {
            this.pool.connect().then(client => {
                client.query(queryString, substVars).then(res => {
                    client.release();
                    resolve(res);
                }).catch(error => {
                    client.release();
                    reject(error);
                });
            }).catch(error => {
                console.log('PgPool.query: catching connect error: ', error);
                client.release();
                reject(error);
            });
        });
    }

    onConnect() {
        this.connectCount++;
        console.log(`PgPool.onConnect:\t${this.connectCount}`);
    }

    onAcquire() {
        this.acquireCount++;
        console.log(`PgPool.onAquire:\t${this.acquireCount}`);
    }

    onError(error, client) {
        console.log('PgPool.onError:\tERROR IN POOL'); //${error.message}`);
    }
}

let pgPool = new PgPool();

export default pgPool;

    .then(() => pool.query(`insert into weblog

( start, ende, url , message ,lenf )
values($1,$2,$3,$4,$5 )`, [obj.start, obj.ende, obj.url, obj.message || "",obj.len]))
.then(() => obj);

{"pid":12008,"hostname":"localhost.localdomain","level":50,"time":1477812965529,"msg":"Unhandled Rejection at: Promise {}","type":"Error","stack":"Error: pool is draining and cannot accept work\n at Pool.acquire (/home/walter/projekt/tpg/node_modules/generic-pool/lib/generic-pool.js:385:11)\n at module.exports. (/home/walter/projekt/tpg/node_modules/pg-pool/index.js:58:15)\n at module.exports.Pool.connect (/home/walter/projekt/tpg/node_modules/pg-pool/index.js:56:10)\n at module.exports. (/home/walter/projekt/tpg/node_modules/pg-pool/index.js:99:10)\n at module.exports.Pool.query (/home/walter/projekt/tpg/node_modules/pg-pool/index.js:98:10)\n at Promise.resolve.then.then (/home/walter/projekt/tpg/tester15.js:190:26)\n at process._tickCallback (internal/process/next_tick.js:103:7)","v":1}

This is not in the documentation. I am wondering how to properly run transactions with pg-pool.

Here is what I have come up with. Suppose there is a function that needs to run an insert and an update in a transaction.

function foo(){
  return new Promise((resolve, reject)=>{
      var iq = 'insert into foo ("id", "data") values (1, "foo")';
      var uq = 'update foo set data = "foo1" where id=1';
      pool.connect().then(client => {
        var q = client.query('begin transaction')
          .then(() => {
            return client.query(insert_query);
          }).then(qresult => {
            return client.query(update_query);
          }).then(qresult => {
            return client.query('commit');
          }).then(qresult => {
            client.release();
            resolve();
          }).catch(err => {
            return client.query('rollback');
              .then(qresult => {
                // rilascia il client
                client.release();
                reject(err);
              }).catch(err=>{
                reject(err);
              });
          });
      }).catch(err => {
        reject(err);
      });
  });
}

I am wondering if this is the correct way to handle transactions and all the possible events and errors that could occur while running in order to properly rolling back the transaction and releasing the client to the pool.

If this is ok, maybe the doc can be updated to include this example?

I'm seeing strange behaviour when using pg-pool. The following script demonstrates the problem

const Pool = require('pg-pool')
const pool = new Pool()

pool.connect((err, client, close) => {
  client.query('SELECT 1', () => {
    close()
    pool.connect((err, client, close) => {
      throw new Error()
    })
  })
})

setInterval(() => {}, Number.MAX_VALUE)

Instead of crashing, the error is swallowed. I noticed this because my code was programatically building a query, and thew an error because of an undefined variable. e.g.

const Pool = require('pg-pool')
const pool = new Pool()

pool.connect((err, client, close) => {
  client.query('SELECT 1', () => {
    close()
    pool.connect((err, client, close) => {
      const statement = getStatment() // Typo meant getStatment was not defined
      client.query(statement, (err, result) => {
        console.log(result)
      })
    })
  })
})

I used the Heroku example to create a pool, but I get such error.

  this.options.create = this.options.create || this._create.bind(this)
                                                           ^

TypeError: Cannot read property 'bind' of undefined
[0]                                                 

Basically after debugging the code I see that the this._create, this._destroy is undefined (https://github.com/brianc/node-pg-pool/blob/master/index.js#L14).

--- Edit
After creating the issue I found the problem, no "new" was provided for Pool instance creation.

Where is log function? Where is this.log come from?

at Pool.connect (/var/storage/pss/node_modules/pg-pool/index.js:189:10)

189: this.log('connecting new client')

const client = new this.Client(this.options)
this._clients.push(client)
const idleListener = (err) => {
err.client = client
client.removeListener('error', idleListener)
client.on('error', () => {
this.log('additional client error after disconnection due to error', err)
})
this._remove(client)
// TODO - document that once the pool emits an error
// the client has already been closed & purged and is unusable
this.emit('error', err, client)
}

this.log('connecting new client')

// connection timeout logic
let tid
let timeoutHit = false
if (this.options.connectionTimeoutMillis) {
  tid = setTimeout(() => {
    this.log('ending client due to timeout')
    timeoutHit = true
    // force kill the node driver, and let libpq do its teardown
    client.connection ? client.connection.stream.destroy() : client.end()
  }, this.options.connectionTimeoutMillis)
}

I am using pg-pool via the node-postgres driver, and noticed something interesting. On a MAC, I was using the driver in an electron app, and postgres was running locally via PostgresApp. In the electron app, if I didn't pass in any credentials , the connection would still succeed. How odd! When I looked at the pool object, it had my default username (MAC username) running the postgres instance and that was being passed into the connection.

If you need further explanation, please let me know. This is the best way I know how to describe it so far.

My question is this- How does pg-pool know to gather the default user account running the postgres service on my local computer?

Currently travis failed on node 0.10 and 0.12 because module standard.js only supports node >=4 because of eslint >=3.

I think it will be ok to drop support of node <4, once pg will also remove support, see brianc/node-postgres#1298

Hi,

I need to test a new certificate for SSL connection. The idea is, I'm testing the new certificate to be used to connect to AWS Redshift based on the link: https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-transitioning-to-acm-certs.html

How can I specify the SSL certificate to be used in config when creating Pool object?

Thanks!

It's still not quite finished, but the API changes are all done and pending any critical bugs should be final.
The 2 big changes are

• the move over to using promises instead of callbacks (except for the "pooled" function which I still haven't quite worked out yet)
• nodejs v4+ support only. It uses various ES6 stuff which is only available in node4.

Minor changes include renaming/removing some config options and constructor args.

Before I get started on the PR for this, I just wanted to check that your ok with doing this.

EDIT --
Also - totally up for any suggestions of things you'd like to see in it or questions about the changes or anything you think is completely daft, or if you'd like me to explain anything!

I am trying to adopt the use of node-postgres 6.x and the new pool within pg-promise, and I encountered one critical issue that stopped me from being able to finish it properly.

When it comes to the error handling for failed connections, up till version 5.1 of node-postgres we used the following error handler:

pg.on('error', function(err, client){
});

i.e. we would always get the Client object for which the connection failed.

With the new version 6.x of the driver this still works only if we attach the handler in the old way, via pg.on. However, the new approach suggests that we do pool.on('error', handler), which fires the event without passing in the Client object.

It is thus inconsistent, but more importantly, it is a big issue when trying to bind the Client context with broken connections that's currently at the very core of pg-promise error handling.

Could we, please, have the same behavior within the pool, to provide the Client object?

pg-pool requires pg but does not include it in either dependencies or peerDependencies

I was testing some scenarios where the connection fails and got UnhandledPromiseRejectionWarning from node. I the catch from the client.query().catch() didn't fire so there seems to be no way to handle bad connection errors and send UI change back to browser. I haven't had time to investigate code, just making a note of this before I forget.

edit: scenarios I tried involved bad username, password or host string in the config object passed in to new Pool({ })

It looks like when I call pool.quit(), it doesn't clean up properly. It looks like the this.pool.drain callback isn't being called.

Can you please add an event emit for pool acquire also. Basically we want to get notification when the connection is retrieved from pool.

Does idleTimeoutMillis = -1 keep connections open indefinitely or cause them to close immediately? Reading the ops of [node-poo]l1 led me to believe the connection would just stay open but this doesn't seem to be the case.

No event works with perform pg_notify.

Hello,

I am trying to figure out how to handle my prepared statement already exists errors.

Is there a way to hook into the "new connection created" method and do something before calling its callback? It would be great if I could create some prepared statements, then run any queries. I noticed there is an option you can pass into pg-pool called onCreated but i dont see it being used anywhere in index.js.

any ideas how to go about solving my problem?

thanks

edit: i just noticed this PR #43, should I just fork this lib and use that until the PR is merged?

Release called on client which has already been released to the pool

I'm trying to use this with pg-native and finding that clients aren't connecting to the connection properties I'm passing in. They instead fall back on environmental variables.

I can see that pg-pool passes the connection hash to the Client constructor, but passes nothing but a cb to the connect method. Pg-native expects connection config in the latter. Furthermore, it expects it as a string, following the libpq convention.

Should I pass in a custom create handler so that I control how (pg-native) clients are created? Or is there a better solution?

My test script:

const Pool = require('pg-pool')

const config = require('config')

// pg.url has form: "postgresql://user@localhost:5432/dbname",
const params = url.parse(config.get('pg.url'))
const auth = params.auth.split(':')

const connectionConfig = { 
  user: auth[0],
  password: auth[1],
  host: params.hostname,
  port: params.port,
  database: params.pathname.split('/')[1],
  ssl: true,
  Client: require('pg-native'),
  min: 4,
  max: 40, 
  idleTimeoutMillis: 1000
}
console.log('Connecting with: ', connectionConfig)
const pool = new Pool(connectionConfig)

pool.connect().then((client) => {
  client.query('select $1::text as name', ['pg-pool']).then((res) => {
    client.release()
    console.log('hello from', res.rows[0].name)
  })  
  .catch((e) => {
    client.release()
    console.error('query error', e.message, e.stack)
  })  
}).catch((e) => {
  console.error('error connecting: ', e.message, e.stack)
})

Note: Commenting out Client: require('pg-native'), causes it to succeed.