Connection termination error causes cursor.read to hang indefinitely

[CaptainHansen]

We have a service that uses cursors to populate an elasticsearch cluster. This morning, a large operation caused our read-replica to lag behind long enough for postgres to kill off a couple connections in an effort to allow recovery to catch up.

In one instance, postgres gave the error ERROR: canceling statement due to conflict with recovery and the client received the error message canceling statement due to conflict with recovery which allowed the service to abort the operation and try again. This worked fine.

The problem I'm writing about came about when the postgres server gave the error: FATAL: terminating connection due to conflict with recovery and instead of the client receiving an error message, the connection's error handler received the following error message: Connection terminated unexpectedly. Around the time this error was emitted by the connection, cursor.read's callback resolved with the full number of rows we asked it to fetch (no error was present when reading), and then, after processing, our program attempted to read another set of results from the connection which hung for about a half hour until I noticed it and manually restarted the service.

Its very possible that I'm missing something to prevent this from happening, and I will attempt to solve this in our own code but figured I'd write about it in case others are having the same issue - especially if it is in fact a bug and an easy fix for node-postgres to make.

[jakubin]

The issue is already 1 year old, but I've come across a very similar case. Connection to DB was lost during a DB query with cursor and that caused and error written to console (Connection terminated unexpectedly) and Node JS app complete hanging. The solution in my case was to attach error handler to connection from pool:

const client = await pool.connect();
client.on('error', error => { /*anything here*/ });

I'm not sure yet if I need to detach the handler when releasing the connection back to pool. Something to investigate.

[brianc]

if you can give me self-contained code that reproduces the issue I can code up a test & fix & release it!

[jakubin]

@brianc I've tried to come up with a simplified example and I've figured out that the app just crashes and it seems like "hanging" because of ts-node-dev I'm using for development.

Either way, I don't think crashing the app is a valid behavior in this case. What I do is run the code below and while it's fetching data I terminate the docker image with postgres (docker kill my-postgres-container) to simulate disconnect:

import { Pool } from 'pg';

(async function() {
  try {
    const pool = new Pool({
      host: 'localhost',
      port: 5432,
      database: 'postgres',
      user: 'postgres',
      password: 'postgres'
    });
    const client = await pool.connect();
    // client.on('error', () => console.log('Error in event.'));
    await client.query('BEGIN TRANSACTION');

    console.log('Open cursor');
    await client.query('DECLARE "cur1" NO SCROLL CURSOR FOR SELECT pg_sleep(0.1) from generate_series(1, 500)');

    console.log('Fetch');
    await client.query('FETCH 100 FROM "cur1"');

    console.log('Close');
    await client.query('CLOSE "cur1"');

    console.log('Commit');
    await client.query('COMMIT');

    console.log('Release');
    client.release();

    console.log('End pool');
    await pool.end();

    console.log('Done');
  } catch (error) {
    console.log('Error in catch.');
  }
})();

Without this line client.on('error', () => console.log('Error in event.')) I get the following output:

Open cursor
Fetch
C:\temp\sample\node_modules\pg\lib\client.js:132
      const error = this._ending ? new Error('Connection terminated') : new Error('Connection terminated unexpectedly')
                                                                        ^
Error: Connection terminated unexpectedly
    at Connection.<anonymous> (C:\temp\sample\node_modules\pg\lib\client.js:132:73)
    at Object.onceWrapper (events.js:420:28)
    at Connection.emit (events.js:314:20)
    at Connection.EventEmitter.emit (domain.js:483:12)
    at Socket.<anonymous> (C:\temp\sample\db-migration\node_modules\pg\lib\connection.js:107:12)
    at Socket.emit (events.js:326:22)
    at Socket.EventEmitter.emit (domain.js:483:12)
    at endReadableNT (_stream_readable.js:1241:12)
    at processTicksAndRejections (internal/process/task_queues.js:84:21)

App crashes and my catch clause is never executed.

With this line client.on('error', () => console.log('Error in event.')), the behavior is better:

Open cursor
Fetch
Error in event.
Error in catch.

Btw - I was surprised to find out that node-postgres is not an official client from postgres team. It's written and performing so nicely I was convinced a bigger company must be behind it. Great work on that!