postgres: CONNECTION_CLOSED error on listen()

The issue here “if the unhandled error is thrown from the listen connection” is simply that we’re not reconnecting

On 19 Apr 2022, at 19.15, S.A.N @.***> wrote:

Why would I need that? If the connection dies I would just simply reconnect. It’s what I’ve been doing with node-pg and it has worked flawlessly.

I mean, the author of the library needs this mechanism, he needs it at the TCP socket level and / or postgres protocol parameters, there are many options for configuring TCP sockets and many parameters at the postgres protocol level…

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.

Ah, I think this could be related to HAProxy being in the middle… So perhaps tcp keep alives are not forwarded, which results in the PostgreSQL server closing the connection. Now for the listen use-case the best would be to not having to do reconnects during normal operation. I think just doing a single message over the wire to keep the connection alive is better than suffering a reconnect and potentially missing notifies.

This is the exact reason why I’ve kept throwing this error instead of ignoring it and doing a simple reconnect, because that could hide deeper problems.

In this case the HAProxy is probably configured to not have long running tcp connections so that load balancing can work better. This is great for everything but our listen case where we indeed would like to keep an open connection ready to receive notifies at all times.

This could also result in issues when running transactions, you could try to do the following when starting your service to test it (unless you have idle_in_transaction_session_timeout set):

sql.begin(sql => sql`select pg_sleep(86400)).catch(err => {
  // You would most likely get a CONNECTION_CLOSED at some point here
})`

You’re welcome, thanks for the debugging 😉

About CONNECT_TIMEOUT error you can increase the connection timeout using connect_timeout option which is 30 seconds by default.

So far the idle_timeout solution has been working great.

Off topic but it would be great if there was some sort of verbose mode that would print stats to the console like how much timeout happened on every query, etc.

I’ll run this for a couple of days and let you know how it goes.

Maybe I’m mistaken but I thought that using sql was some kind of pool that creates clients on demand, retries queries, etc?

No, that’s correct, but it creates connections in the pool lazily, and there is only retry for invalid prepared statements. So it doesn’t retry other queries individually, but it reconnects connections automatically when needed.

I have only seen this error when using listen(). Regular queries have been working fine and I have never seen this CONNECTION_CLOSED error.

Yes, that’s what i meant with:

This means it is not related to onlisten directly, but only happens in tandem because the timeouts match for the two connections.

So let’s say your service only has this listen setup - it will first create a dedicated connection for listening, and it will then open a connection in the pool once you execute your query in onlisten. Those two connections will open almost at the same time. Let’s say the server you connect to closes connections without a FIN packet after 10 minutes. The listen connection will then close after about a minute because it never gets a tcp keepalive response. That will make listen reconnect, then it calls your onlisten function, and now you’re about to execute a query again. Postgres.js will look in the connection pool and see a connection that appears to be open (it cannot know it was half closed because the keepalive timeout was not reached yet), so it will try to write to that socket, which will then throw an error because no ack is received. This is why having an idle_timeout setting lower than the server will solve the problem. Instead of the connection ending up as half open, Postgres.js will close it after idle_timeout, and when you make your query a new connection will be opened instead.

I’ll try with the idle_timeout setting and let you know how it goes.

Cool. Looking forward to hear about it 😊 I cannot imagine the haproxy has a max lifetime on the connection instead of idle, but if it does you can also look at setting max_lifetime.

Apologies for mistakes, this was written on my phone.

Why would I need that? If the connection dies I would just simply reconnect. It’s what I’ve been doing with node-pg and it has worked flawlessly.

I mean, the author of the library (@porsager ) needs this mechanism, he needs it at the TCP socket level and / or postgres protocol parameters, there are many options for configuring TCP sockets and many parameters at the postgres protocol level…