Feature: query timeout

[juliusza]

I want to start a discussion about built in query timeout.

In our production environment, we encountered a problem with TCP connections getting stuck. Knex pool would fill up with connections that are not responsive. Note that this would be different from statement timeout, where DB decides to terminate query. Instead pg client would decide that connection is unresponsive and emit an error.

This situation can be simulated with firewall rule:

sudo iptables -A INPUT -j DROP -p tcp --destination-port 5432

Or just use this code snippet:

const { Client } = require('pg')
const client = new Client({user: "user", password: "pass", database: "db"})
const util = require('util');
const exec = util.promisify(require('child_process').exec);

async function main() {
   await exec("sudo iptables -D INPUT -j DROP -p tcp --destination-port 5432");
   await client.connect()

   console.log("Client connected");
   await exec("sudo iptables -A INPUT -j DROP -p tcp --destination-port 5432");

   console.log("Now waiting for DB to respond... forever")
   const res = await client.query('SELECT $1::text as message', ['Hello world!'])
   console.log(res.rows[0].message) // Hello world!
   await client.end()
   process.exit(0)
}

main()

A possible implementation can be found here: master...juliusza:query_timeout
I know I need to add tests and also edit the native driver code for a proper PR. But first I'm looking for feedback from the community to assert if such a thing would be useful.

Also perhaps

socket.setTimeout(3000);

could be a viable solution. I need to look into that.

[abenhamdine]

Happy to see that !

Do you plan to add tests and send a PR ?

[edevil]

Yes, please!

[edevil]

@juliusza You need help with the PR?

@brianc Would this be merged?

[brianc]

I'm definitely down for a pull request for this feature. I think it'd be helpful & something the library should provide since it's pretty difficult to implement in your own code.

[edevil]

@brianc I’ve submitted a PR for this. #1760

[neylsongularte]

Same problem with me, my temporaly solution:

        var socket = new require('net').Socket();
        socket.setTimeout(20000)

        const client = new pg.Client({
            connectionString: urlPostgres,
            stream: socket
        });

        socket.on('timeout', function () {
            // console.log('timeout')
            client.end();
        });

        await client.connect()

[vitaly-t]

Can this be closed now or there is still a problem with the sockets?

[brianc]

yay!