Closed connections only reconnected after ping

[DVSLabs]

When using a service like Neon that does autoscale to 0 databases, the ping every second prevents this behaviour. However, when you set the ping interval longer (Neon recommends 24 hours) then when the database scales to 0 the connection closes and you get Postgrex: DBConnection ConnectionError ssl send: closed which is correct.

But... the connection will not automatically attempt to reconnect which should succeed as Neon would spin back up a compute unit. It only automatically reconnects on a ping, but frequent pings would prevent autoscale to 0 defeating the purpose.

I'm not sure what the best approach is to solve this issue but it seems like automatic reconnects maybe shouldn't only happen on pings.

[josevalim]

Right, the current system assumes the connection is alive and not that it will start dynamically. We need to add an option for what you need: perhaps something that says "force_reconnect_when_closed". A pull request is welcome.

Although keep in mind that Postgres connections in particular are expensive to boot, because we need to query database types and so on, but I do understand this is a feature some folks would expect from Neon anyway.

[DVSLabs]

I'm not too familiar with the code base and how it connects down from Ecto through Postgrex down to DBConnection. Do you have any pointers on where/how I should be triggering a reconnect?

I have naively patched handle_event(:internal, {:disconnect, {log, err}}, :no_state, %{mod: mod} = s) and I trigger a connect through handle_event(:internal, {:connect, _info}, :no_state, s) but I'm still missing something and I honestly have no idea idea what I'm doing.

[greg-rychlewski]

@josevalim I was interested in this one and started taking a deeper look and have some thoughts/ideas.

First, this is what I think is happening:

• Postgrex tries to send a message on the socket and receives a :closed error here
• Postgrex sends back a :disconnect tuple to DBConnection
• DBConnection handles disconnecting the connection and then returns the error to the caller . Part of the handling is to reconnect with backoff as long as backoff: :stop has not been specified. All disconnects seem to end up here no matter where they came from
• Ecto receives the error and raises

So from what I see there are two issues:

1. Even though DBConnection is handling reconnects it only does it one connection at a time. So you can potentially try several times in a row and fail each time.
2. The exception being raised is not specific enough to know it's because the connection was closed without trying to parse the message. Postgres just calls DBConnection.ConnectionError.exeception(message) here so the reason just defaults to :error (see exception definition here). If we could make the reason :closed then the user could catch the error and retry the query on that field.

In terms of how to deal with a potentially pool full of closed connections, I can think of a few ways:

1. If the user is catching the exception and matching on our new reason :closed they could call disconnect_all and then redo their query
2. We might be able to have some option in DBConnection to call disconnect_all for them when we see a :closed error reason

I might be wrong but I don't see a sane way to do this all synchronously:

User sends query -> Postgrex detections socket is closed -> DBConnection disconnects socket -> DBConnection replaces with new socket and re-runs original query -> User thinks nothing happened except maybe it was a bit slower

[josevalim]

Great find! I just did some refactoring to streamline the paths where we call run/checkout, so we can easily make it so we retry failed checkouts. Now let's see if I can submit a PR shortly.