node-postgres: Fails to error on queries inside a transaction after a lost connection

When we execute a lengthy operation inside a transaction, and the connection is suddenly lost during that time, then any query we execute on the client after that become stuck, i.e. they report nether success no error:

'use strict';

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

const cn = {
    database: 'newone',
    port: 5433,
    user: 'postgres'
};

const p = new Pool(cn);

p.on('error', error => {
    // do nothing
});

p.connect((err, client, release) => {
    if (err) {
        console.log('Failed to connect:', err);
        return;
    }
    const test = async function () {
        try {
            await client.query('BEGIN');
            await client.query('SELECT pg_sleep(10)'); // connection breaks during this one
            await client.query('COMMIT');
            console.log('success!');
        } catch (e) {
            console.log('about to rollback...'); // this is reported
            await client.query('ROLLBACK'); // this one is stuck, never reporting an error, if the error was due to lost connection
            console.log('rollback finished'); // this is never reported, if we are here because of lost connection
        } finally {
            release();
            p.end();
        }
    };

    test();
});

I’ve tried both pg-pool directly and via node-postgres, versions 6.x and 7.x - all the same.

Expected Behavior

Executing any query against client at that point should immediately report an error that informs us of the lost connectivity.

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 16 (12 by maintainers)

Commits related to this issue

Disable long transaction due to node-postgres open bug https://github.com/brianc/node-postgres/issues/1454#issue-257874584 — committed to conradoqg/meajudafi-workers by conradoqg 6 years ago

Most upvoted comments

UPDATE

This issue appears to be even more severe, reported against solutions running on a thin WiFi connection, i.e. the issue keeps happening even with small transactions that execute longer due to the slow network.

@charmander @brianc Guys, any chance to see a progress for this some day? 😄 This is a P1 bug 😉

vitaly-t on Feb 19, 2018

Nevermind, I was testing on the wrong branch – confirming that #1503 does fix this as well.

charmander on Jul 26, 2018

While there is no fix for this, for now I am using the following work-around:

try {
      await client.query('BEGIN');
      await client.query('SELECT pg_sleep(10)'); // connection breaks during this one
      await client.query('COMMIT');
     } catch (e) {
        if(!isConnectivityError(e)) {
          await client.query('ROLLBACK');
       }
    }

And here’s code from pg-promise of how I check for a connectivity error:

////////////////////////////////////////////
// Identifies a general connectivity error.
function isConnectivityError(err) {
    const code = err && typeof err.code === 'string' && err.code;
    const cls = code && code.substr(0, 2); // Error Class
    return code === 'ECONNRESET' || cls === '08' || cls === '57';
    // Code 'ECONNRESET' - Connectivity issue handled by the driver.
    // Class 08 - Connection Exception.
    // Class 57 - Operator Intervention.
    //
    // ERROR CODES: https://www.postgresql.org/docs/9.6/static/errcodes-appendix.html
}

vitaly-t on Apr 18, 2018