Question: How to properly handle unexpected disconnection / autoreconnect ?

[abenhamdine]

After have updated from 5.1 to 6.2.4, I see sometimes (nearly once a day) the following error which crashes our production server :

Error: Connection terminated unexpectedly
2017-06-15T10:03:33.683+02:00 at Object.onceWrapper (events.js:293:19)
2017-06-15T10:03:33.683+02:00Connection terminated unexpectedly
2017-06-15T10:03:33.684+02:00 at Socket.<anonymous> (/home/bas/app_21386476-a451-424c-ad67-870442bbdbe7/node_modules/pg/lib/connection.js:138:10)
2017-06-15T10:03:33.684+02:00 at emitNone (events.js:86:13)
2017-06-15T10:03:33.684+02:00 at emitNone (events.js:91:20)
2017-06-15T10:03:33.684+02:00 at Connection.emit (events.js:188:7)
2017-06-15T10:03:33.684+02:00 at Socket.emit (events.js:188:7)
2017-06-15T10:03:33.685+02:00 at process._tickCallback (internal/process/next_tick.js:104:9)
2017-06-15T10:03:33.685+02:00undefined
2017-06-15T10:03:33.685+02:00 at _combinedTickCallback (internal/process/next_tick.js:80:11)
2017-06-15T10:03:33.685+02:00 at endReadableNT (_stream_readable.js:975:12)

I've seen #1316

AFAIK, the error was not emitted before this patch, as of now, we have an error emitted on the pool.

I'm a bit confused here about what we should do when this error is happening ?
Does it mean that we have to reconnect the entire pool ? But how ?
Or does it means that the client emitting the error is definitively dead, so we rather drop the operation initiated by the client, and tell the user that his query is dropped. But once again how since the client.query callback seems not called with this error (perhaps somehow related to #1322) ?

[brianc]

Hey @abenhamdine - I'm glad to see the patch I put into pg@6.2.4 is working!

Before when connections unexpectedly dropped from the backend or a network partition happened node-postgres would silently ignore the closed connection and not always purge the disconnected connections from the pool so your pool could fill up with stale connections under heavy load. Under light load connections are purged on a configurable timeout, so you like wouldn't see the old 'stale connection' issue unless you had sustained heavy load for many days in production or were doing db failovers or the like.

What's happening now is one (or many) client(s) in your pool sits idle and connected to the backend database. A network partition happens or database failure, fail-over, forced disconnection from the backend, etc and the idle client(s) all notice and emit an error event. The pool listens for errors on idle clients. When an idle client has an error the pool destroys the client and removes it from the pool so the next time you request a client from the pool it will automatically create and connect a new client.

It's unlikely the error is being emitted from the pool during an active query (if so, this is a bug) - during query execution a client will delegate error handling to its active query so the query can call it's callback with the error (or reject it's promise, etc).

You'll want to put a pool.on('error', (err) => console.error(e.stack)) listener or something on your pool and handle (or at least log) errors here. You probably don't need to restart your application every time an idle client disconnects in the pool because node-postgres will try to do the right thing & reconnect you next time you need a client but you should at least be aware of it. And since node crashes the process when you don't listen for an error event, by default your process is crashing.

tl; dr -

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

Hope this helps. I've tagged this as a doc candidate so I'll refine the docs here & make an entry in the docs about error handling of pooled clients.

[brianc]

Also @abenhamdine I know this isn't really the best place for this kinda conversation but I'll be in Paris in september from the 21st through the 30th if you want to meet up for a coffee or something & chat about node-postgres. If so, email me & we can plan! brian.m.carlson@gmail.com - if not, no worries!

[abenhamdine]

Thx you so much for such a complete & precise explanation ! 👍 💃
It's very very precious for not-so-advanced-users like me.
I've always thought that the quality of a open-source project depends on the ability of the maintainers/advanced users to provide help & answers to such questions.

The 7.0 release (and its docs !) looks very promising, so good luck !

Of course, i will be glad to meet you in Paris late september 🍷

[vyerneni17]

Hi @brianc, I faced this issue in my application. Thank you for your explanation I was able to put in the console.error and know why and when it is happening.

My question is how should I handle it in the pool.on ('error', () => { }) ? I mean to say, how would I reconnect automatically when the connection terminates ? At this point, I'm required to restart the node server manually when the connection is terminated.

Please pardon if it is a silly question, just wanted to know what would be the best way to do this ?
Thank you!

[pcothenet]

@vyerneni17 attempting to belatedly provide an answer for @brianc:

You don't need to do anything in the listener aside logging it to your destination of choice.

• This error only happens outside of a query
• The pool will automatically attempt to recreate a connection at your next query

You still want to add a listener:

• so the error doesn't crash your process
• you can monitor those instabilities in your connection.

Hope that helps!

[mshick]

I'm encountering problems on this front as well. I'm trying to intercept momentary interruptions (e.g., due to a db restart) which emit this error: terminating connection due to unexpected postmaster exit.

Initially I tried connecting a handler for pool errors on the pool instance directly, but found it was never triggered when restarting my database:

const pool = new pg.Pool({});
pool.on('error', err => console.log(err));

I then tried waiting to attach the error handler until I get the connect event emitted:

const pool = new pg.Pool({});
pool.on('connect', () => {
  pool.on('error', err => console.log(err));
});

Much better luck there. I got the errors logged out. Unfortunately somewhere along the way there is still an error thrown from the connection itself:

[exiting] Fatal exception: error: terminating connection due to unexpected postmaster exit
    at Connection.parseE (/opt/runtime/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/opt/runtime/node_modules/pg/lib/connection.js:378:19)
    ...

@brianc or others: Can you advise on a thorough, proper implementation which will avoid unhandled errors in situations like these? Have searched the issues, but haven't had much luck for a comprehensive solution with the new pools.

[mshick]

I'm very sorry to add to the confusion here. It just occurred to me that I might have another pool (aside from the one I was working with above) in place, and indeed that's the pool that was throwing unhandled errors.

The simplest solution appears to work great for my case.

Thanks!

[CoreyCole]

@brianc @pcothenet I'm running my node process with nodemon, I'm not sure if this changes things, but when this error occurs it stops the process.

/app/node_modules/pg/lib/client.js:179
      const error = new Error('Connection terminated unexpectedly')
                    ^
Error: Connection terminated unexpectedly
    at Connection.con.once (/app/node_modules/pg/lib/client.js:179:21)
    at Object.onceWrapper (events.js:313:30)
    at emitNone (events.js:106:13)
    at Connection.emit (events.js:208:7)
    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:76:10)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at TCP._handle.close [as _onclose] (net.js:557:12)
[nodemon] app crashed - waiting for file changes before starting...

(I've already added the event listener)

[charmander]

@CoreyCole How did you add the event listener?

[CoreyCole]

@charmander

import { Injectable } from '@nestjs/common';
import { Client, QueryResult, Pool } from 'pg';

import { EnvironmentsService } from '../environments.service';

@Injectable()
export class PostgresRepositoryService {
    private client: Client;
    private pool: Pool;

    constructor(private environment: EnvironmentsService) {
        this.client = new Client({
            host: this.environment.getDatabaseUrl(),
            port: this.environment.getDatabasePort(),
            database: 'db_name',
            user: 'db_user',
            password: this.environment.getDatabasePassword()
        });
        this.client.connect()
            .catch(err => {
                console.error(`[PostgresRepositoryService][constructor]: error connecting to db [${err}]`);
                process.exit(1);
            });
        this.pool = new Pool();
        this.pool.on('error', (err) => {
            console.error('An idle client has experienced an error', err.stack);
        });
    }
// other code