Hi. Sorry ahead of time, I don't have a lot of detail here. We updated our packages today and afterwards started seeing a connection error from Sequelize ORM: "self signed certificate". We do in fact use a self signed certificate but this has never been a problem for us in the past. We downgraded from [email protected] to [email protected] and then we were able to connect again.

First off, thanks for the library! Overall it's been incredibly performant and straightforward to use. I am running into one issue which may be an issue with the library, though it's a bit hard to follow the protocol for me so my usage may be off as well.

I'm running into an uncaught error during the parseComplete handler in client.js (https://github.com/brianc/node-postgres/blob/v6.0.1/lib/client.js#L131).

I am doing a direct connect against the pool and then utilizing pg-query-stream

pool.connect().then(function(client){
  stream = client.query(new QueryStream(query, null));

  stream.on('error', function(streamError){
    stream.close(function(closeError){ 
      // What to do with a close error? At this point stream is already hosed :shrug:
      client.release(streamError);
    }
  });

  stream.on('close', function(){
    client.release();
  }); 
});

At some point during my app's life cycle, I get an uncaught error

TypeError: Cannot read property 'name' of null
    at null.<anonymous> (/var/app/current/node_modules/pg/lib/client.js:139:26)
    at emit (events.js:107:17)
    at Socket.<anonymous> (/var/app/current/node_modules/pg/lib/connection.js:121:12)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:540:20)

So obviously activeQuery is becoming null, and I am trying to narrow down why I would receive a parseComplete event when the active query is null.

According to my logs, the stream is not emitting an error, and seems to be closing normally. Therefore it seems like the connection client is getting a readyForQuery or end event, and then right after that getting the parseComplete event. Any idea why this would be happening, or see any issues with my usage? Thanks for any help you can give! I'll keep looking into it as well.

var pg = require('pg'); var conString = "..."; var client = new pg.Client(conString); client.connect(function(err) { if(err) { return console.error('could not connect to postgres', err); } var sql = "SELECT NOW() as now1, timezone('UTC'::text, now()) as now2"; client.query(sql, function(err, result) { if(err) { return console.error('error running query', err); } console.log(result.rows); client.end(); }); });

Result is: [ { now1: Thu Aug 29 2013 11:37:14 GMT+0300 (FLE Summer Time), now2: Thu Aug 29 2013 08:37:14 GMT+0300 (FLE Summer Time) } ] And must be: [ { now1: Thu Aug 29 2013 11:37:14 GMT+0300 (FLE Summer Time), now2: Thu Aug 29 2013 08:37:14 GMT+0000 (UTC) } ] or equal dates This is tested with latest version node-postgres.

We're in the process of bringing up our (normally AWS-based) stack on azure with a hosted PG instance. Azure apparently requires SSL (which may or may not be important, but it's in the stack trace, so..)

We're running into issues with unpooled connections (used to verify DB availability) in our periodic DB status tests. node version is 6.9.0; node-postgres version is 6.4. Code looks like this (w/o longjohn):

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

const DSN = 'postgres://uid:pwd@azure_db:5432/imp?ssl=true';

function status(cb) {
    var start = Date.now();
    var client = new pg.Client(DSN);
    client.connect(function(err) {
        if (err) {
            return cb(err);
        }

        client.query("select 1 as pong", function(err, results) {
            client.end();

            if (err) {
                return cb(err);
            }

            if (!results.rows || results.rows.length !== 1 || results.rows[0].pong !== 1) {
                return cb(new Error("expected pong of 1"));
            }

            return cb(null, Date.now() - start);
        });
    });
}

setInterval(function() {
    console.log(new Date().toISOString());
    status(function(err, latency) {
        if (err) {
            console.error(err);
        }
    })
}, 5000);

.. errors (after 5s) with this:

Error: read ECONNRESET
    at exports._errnoException (util.js:1022:11)
    at TLSWrap.onread (net.js:569:26)
---------------------------------------------
    at Client.connect (/home/andrew/tmp/node_modules/pg/lib/client.js:184:7)
    at status (/home/andrew/tmp/test-unpooled-pg.js:9:12)
    at Timeout.<anonymous> (/home/andrew/tmp/test-unpooled-pg.js:32:5)
    at ontimeout (timers.js:365:14)
    at tryOnTimeout (timers.js:237:5)
    at Timer.listOnTimeout (timers.js:207:5)

.. seems like it may be related to https://github.com/brianc/node-postgres/issues/314 but unclear.

When I connect to an existing database with the connect username but the wrong password, I got an unhandled exception using the following code. The parameter err is set, but the exception is still thrown.

pg.connect(dbURL, function(err, client, done) {
  if(err) {
    return console.error('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(result.rows[0].number);
    //output: 1
    client.end();
  });
});

When I do Pool.query (Pool is instance of pg.Pool and successfully connected to DB), it never returns. I tried both promise syntax (async/await and then/catch) and callback syntax, none of them worked. The script just never ends.

Using connection with the same details with psql works.

Node v12.14.0 npm 6.13.4 pg 7.17.1 PostgreSQL 12.1

EDIT: forgot to specify this happens when there's no row returned. EDIT2: Select DATE() works while from table doesn't

Hello -

I think the client object interface is pretty good. What is not a good interface is the current incarnation of the pool. Namely pg.connect. Here are some reasons why:

First of all, as a quick aside. The pooling was added to the module before the there was a lot of wisdom around "tiny modules that just do one thing." Sooo really it would be better to have the pool in a completely separate module. I would like, probably, to start developing the pool off in it's own module and maybe eventually rip the built in stuff out...but I don't like breaking APIs. Anyways....

I think a good API makes the right thing easy and the wrong thing hard. Currently the pool is just about opposite of that.

Here are some examples of the pool sucking:

dead clients

Because the pool waits for a client's query queue to drain, and once the client query queue is drained the client is automatically returned, if you never send a query to the client, the client is never returned. Eventually your pool is all checked out and idle and your app hangs. Here is an example of this total bullshit.

app.get('/', function(req, res, next) {
  pg.connect(app.constring, function(err, client) {
    if(!req.session) {
      return next();
      //way to go partner. you just leaked a connection
    }
    client.query('SELECT * FROM customers WHERE id = $1', [req.session.id], function(err, result) {
      var html = app.render('home', {user: JSON.stringify(result.rows[0])});
      res.send(html);
    })
  });
});

transactions

On the flip side of dead connections, greedy pool release mechanics of 'the first drain event' cause the client to get pushed back into the pool of available clients way too early.

pg.connect(/*constring*/, function(err, con) {
  con.query('begin', function(err) { //start our transaction
    con.query('update bla', function(err) {
      setTimeout(function() {
        //way to go partner, you returned this connection to the pool with an open transaction
        //better hope no one else wants to run a query for 2 seconds or some weird stuff is gonna happen
        con.query('commit');
      }, 2000);
    });
  });
});

That right there is an example of the 'auto release' being too greedy and not greedy enough.

I am going to fix this

I'd like to open up discussion around a different API for the pool. After 24/48 hours I'll get started on a new implementation. So here's what I'm thinking....this would be backwards compatible by adding a 3rd parameter to the pg.connect function. A callback you call when you want to release the client back to the pool. The callback itself will take a single error argument in the style of node. If the argument is non-null, the client will be closed and removed from the pool instead of returned to the pool. This is how it would look:

pg.connect(/*constring*/, function(err, client, done) {
   //hmmm....turns out I actually don't need this client
  done();
});

or what about doing a transaction? currently you have to do some nasy stuff with pauseDrain() and resumeDrain() which are themselves somewhat cludgey hacks I put on because I was mentally exhausted and planning a home remodel + my wedding. Well, I'm back now, refreshed, and want to make things better and all of our lives easier...

pg.connect(/*constring*/, function(err, client, done) {
  client.query('begin', function(err) {
    if(err) return callback();
    client.query('update something', function(err) {
      client.query('commit', function() { done() });
    })
  });
});

Also, the 1st parameter (connection info) is optional. I think node-postgres should use the same environment variables as the psql command to get default connection info, and then supplement with pg.defaults and then override with custom stuff passed in. So if you use defaults or the environment variables, you can do this:

pg.connect(function(client, done() {
  done();
});

The pool needs a cleaner way to shut itself down too without being destructive. I'm not sure how that should work currently.

feedback

I needs your feedback on this. Let's hear it!

Deprecation Warning when testing against node 7.0.0 rc1

Using Buffer without new will soon stop working from node v7.0.0. Use new Buffer(), or preferably Buffer.from(), Buffer.allocUnsafe() or Buffer.alloc() instead.

I know we shall support node prior to 6.x, so I use new Buffer() to replace Buffer().

Hello,

I'm working on setting up a health check within our system, and have noticed that our Postgres implementation doesn't seem to handle connection timeouts, dropped network, or a db failover event very gracefully (at least in the way it works when using a multi-az deployment in Amazon RDS). The issue is very similar to one that was fixed in the mysql package a while back -- https://github.com/mysqljs/mysql/issues/821.

We effectively have a class that looks similar to this:

const Promise = require('bluebird');
const pg = require('pg');

class PostgresSQLInterface extends Service {
    constructor(dbConnectionOptions, logger) {
        const defaults = {
            host: 'localhost',
            port: '5432',

            user: '',
            password: null,

            max: 10, // Poolsize
            min: 10,

            keepAlive: true,
            idleTimeoutMillis: 10000
        };

        this.state = {
            config: _.merge({}, defaults, dbConnectionOptions)
        };

        this.createDatabasePool();
    }

    createDatabasePool() {
        this.state.pool = new pg.Pool(this.state.config);

        this.state.pool.on('connect', () => {
            this.state.healthy = true;
            console.log('HEALTHY');
        });

        this.state.pool.on('error', () => {
            this.state.healthy = false;
            console.log('UNHEALTHY');
        });
    }

    *getDatabaseConnection() {
        try {
            const connection = yield this.state.pool.connect();

            return connection;
        }
        catch (error) {
            throw new Errors.DatabaseError("Could not connect to Postgres", {
                originalError: error
            });
        }
    }    
};

In a normal environment, when our application connects, I see it spit out "HEALTHY" x the number of connections it made, as expected. However, there are a few issues:

1.) If the connection is severed (I turn off my wireless, kill my VPN, or trigger a reboot on the RDS instance), no error events are raised even though the documentation for pg-pool states an error is "Emitted whenever an idle client in the pool encounters an error. This is common when your PostgreSQL server shuts down, reboots, or a network partition otherwise causes it to become unavailable while your pool has connected clients." My expectation would be to see a string of "UNHEALTHY" statements.

2.) Similarly, if I initiate a failover in RDS, no error events are raised but I am unable to run queries. This may be due to how AWS handles the DNS entries around multi-az deployments, but I cannot test this while issue #1 remains unresolved.

3.) Calling getDatabaseConnection when no replies are received from postgres (sudo iptables -A INPUT -p tcp --source-port 5432 -j DROP, sudo iptables -F afterwards to restore) hangs on grabbing a connection, as there is no timeout setting.

Am I missing a setting in my configuration, or is this a situation that just hasn't come up yet for someone else? If it's the latter, I'd be more than happy to create a fix for the issue, but want to make sure I'm not overlooking something first.

Node version: 6.2.x PG version: 6.0.2 Postgres version: 9.5.x

Thanks!

The pg client seems to fail quietly when connecting to postgress when using Node 14.x. Tested using Docker.

Dockerfile

FROM node:14.0.0-alpine3.10 as builder
RUN apk add postgresql-client
WORKDIR /src
COPY . .
ENTRYPOINT [ "/bin/sh" ]

package.json

{
  "devDependencies": {
    "pg": "^8.3.2",
    "ts-node": "^8.10.2",
    "typescript": "^3.7.5"
  }
}

test-pg-client.ts

const { Client } = require('pg');

start();

async function start() {
    const conString = "postgres://postgres:(password)@localhost:5432/postgres";

    const client = new Client(conString);
    await client.connect();

    const query = await client.query('select 1');

    query.rows.forEach(value => console.info(`row value`, value));

    await client.end();
}

Since we're using Docker, let's build and run the image

docker build -t my-image .
docker run -it --network=host my-image

(this runs the image with host network access, allowing you to connect to a postgres database that is running on the same machine. )

Expected Result (while inside the container):

❯ yarn install
❯ ts-node test-pg-client.ts
row value { '?column?': 1 }

Actual Result:

❯ ts-node test-pg-client.ts

(no errors, no messages, just nothing)

The behavior is remedied by changing the first line in Dockerfile to the following:

FROM node:12.18.3-alpine3.12 as builder

This behavior seems similar to https://github.com/brianc/node-postgres/issues/2180 but wasn't totally sure if the circumstances were the same, so chose to make a new issue just in case.

If we take the example on the main page:

var pg = require('pg');
var conString = "postgres://username:password@localhost/database";

//this initializes a connection pool
//it will keep idle connections open for a (configurable) 30 seconds
//and set a limit of 10 (also configurable)
pg.connect(conString, function(err, client, done) {
  if(err) {
    return console.error('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(result.rows[0].number);
    //output: 1
  });
});

And simply switch to Node.js 6.0.0, we are immediately getting an error:

error fetching client from pool { error: password authentication failed for user "postgres"
    at Connection.parseE (D:\NodeJS\tests\node_modules\pg\lib\connection.js:539:11)
    at Connection.parseMessage (D:\NodeJS\tests\node_modules\pg\lib\connection.js:366:17)
    at Socket.<anonymous> (D:\NodeJS\tests\node_modules\pg\lib\connection.js:105:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:172:18)
    at Socket.Readable.push (_stream_readable.js:130:10)
    at TCP.onread (net.js:535:20)
  name: 'error',
  length: 115,
  severity: 'FATAL',
  code: '28P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'src\\backend\\libpq\\auth.c',
  line: '304',
  routine: 'auth_failed' }

i.e. authentication doesn't work under the newly released Node.js 6.0.0

My Test environment:

• Windows 10, 64-bit
• Node.js 6.0.0 (released)

Best security practices recommend refreshing TLS certificates often. Pool let's you set the secureContext like this:

  const pool = new pg.Pool({
    host: config.postgres.host,
    port: config.postgres.port,
    user: config.postgres.user,
    password: config.postgres.password || undefined,
    database: config.postgres.database,
    ssl: config.postgres.tls_enabled
      ? {
          rejectUnauthorized: config.postgres.tls_verify_server_certificate,
          secureContext: certManager.tlsServices.postgres.secureContext,
        }
      : 

But there is no way to update the secure context without re-creating the entire pool. We solve this by doing:

pool.options.ssl.secureContext = context; // context is here a new Secure Context

But options is private in the TypeScript files, and possibly not intended to be changed.

Would it make sense to add a functionality to the Pool to update the secure context, like setSecureContext as to mimic the Node.js API for setting secure contexts? Or are we expected to always re-create the pool?

Hey, I am trying to connect to Redshift using ssl. Redshift cluster is configured with enable_ssl, when creating new client we using ssl: true. This works but is it using CA certificate of Redshift? Or how it connected without me adding CA certificate? What CA certificate is it using? like the this client is for Postgres sql or it's support Redshift officially as well?

I'm wondering what library you suggest for migrating your postgres database when using node-postgres in your server. I've found a few that don't have a ton of reviews, so i was wondering what y'all use, especially @brianc as the creator of this repo. Native migration capabilities would be cool too!

Bumps chai from 4.3.6 to 4.3.7.

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.

Bumps mocha and @types/mocha. These dependencies needed to be updated together. Updates mocha from 7.2.0 to 10.2.0

Updates @types/mocha from 5.2.7 to 10.0.1

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.

Bumps typescript from 4.8.4 to 4.9.4.

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.