ConnectionTimeoutMillis is not respected and pool.connect hangs indefinitely

[mikewang333]

Hi, I work on a product that allows users to connect to their own postgres instance and write queries.

I noticed for hostnames related to incorta, if the configuration isn't set up properly the connectionTimeoutMillis isn't respected and the event loop is blocked at pool.connect.

Example repro with a sample incorta postgres instance (I'm using a free trial instance so it might expire soon. It's pretty easy to spin up another one though on their site).

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

const executeQuery = async () => {
  const pool = new Pool({ host: 'incorta-5668.sqli.cloud13.incorta.com', port: 3482, connectionTimeoutMillis: 1000});
  pool.on('error', (err) => {
    console.error('Unexpected error on idle client', err);
    process.exit(-1);
  });

  // STUCK AT THIS LINE
  const client = await pool.connect();

  client.release();
  await pool.end();
};

executeQuery();

Would be great if this could timeout after the configured connectionTimeoutMillis like it would with other misconfigured postgres instances.

We're using pg version 8.11.3 currently. Thank you! :)

[pcan]

Hi, we are probably experiencing the same issue with digitalocean URLs like this: postgresql://user:pass@instancename.h.db.ondigitalocean.com:25060/foobar?sslmode=require.

We are not able to reproduce it in our dev environment, but in our production env (running on CloudFlare durable objects), it seems to hang quite consistently on the client.connect() (we're not using pools, btw).

[clintonb]

I'm facing a similar issue. The time out is supposed to be 30 seconds, but I have traces that go on for 30 minutes. They typically have the following spans:

pg-pool.connect
└── pg.connect
    └── tcp.connect
        └── dns.lookup

I'm still trying to figure out why GKE DNS is so broken; however, I would still expect the connection timeout to kick in and kill this attempt.

P.S. This may be a duplicate of #2338.

[hjr3]

I'm still trying to figure out why GKE DNS is so broken

k8s DNS has some real problems. bdeitte/hot-shots#223 may be helpful to read

you may want to consider caching DNS queries

Example:

const dns = require('dns');
const net = require('net')

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

const run = async () => {
    const stream = await (new Promise((resolve, reject) => {
        const socket = net.connect({
            host: 'localhost',
            port: 5432,
            lookup: (hostname, options, callback) => {
                // write your custom DNS lookup logic here
                dns.lookup(hostname, options, callback);
            },
        }, () => {
            resolve(socket);
        });

        socket.on('error', (error) => {
            reject(error);
        });
    }));

    const client = new Client({
        stream,
        database: 'dbname',
        user: 'user',
        password: '',
    });
    
    await client.connect();

    client.end();
    stream.end();
};

run().catch(err => {
    console.error('Connection error', err.stack);
});

[DijieDeng]

I've opened #3721 with a fix for this. It addresses the root cause in pg-pool's newClient(): the connectionTimeoutMillis timer previously only tore down the connection under specific conditions (client.connection existed or !client.isConnected()), so when neither held — e.g. during a slow/stalled DNS lookup — the timer fired but did nothing and pool.connect() hung forever. The fix makes the timeout always reject the checkout at the deadline (preserving the native-client exemption), removes the client from the pool, and ends the connection cleanly. It also discards connections that finish establishing after the timeout, which closes the related connection leak (#3543). A regression test is included.