Menu
We have been using golang's database/sql and github.com/lib/pq with a PostgreSQL cluster, meaning that a certain database server that was a replication master might be a read-only replica after the previous master has failed.
Recently, our low-usage service managed to keep a single connection in its connection pool (there were no concurrent requests that would have opened a second connection), and its target had been demoted to a read-only replica. As a result, each write operation to the service failed until it was restarted. That would not have happened, if a new connection was made to the cluster.
The problem is I can't find a documented way to discard a connection on certain kinds of errors. The only public method in database/sql that sounds correct is Conn.Close which returns the connection to the pool without closing it. Not calling it will cause a resource leak which will eventually make the pool unusable. Is there a sustainable way to get rid of a connection when the application wants to?
455
And both make use of PostgreSQL. There are many good tutorials out there that talk about the Golang’s native SQL abstraction - how to run queries along with other intricacies about the package. Most of them, however, seem to skip rather production-relevant settings that have to do with connection pooling.
A connection pool is a set of maintained connections that can be reused for future requests to the database. When you instruct sql.DB to run a query, it will first check if there are any idle connections - if there’s one in the pool, it will take it and return it back to the pool at the end of the query.
When you instruct sql.DB to run a query, it will first check if there are any idle connections - if there’s one in the pool, it will take it and return it back to the pool at the end of the query. If there’s no idle connection available in the pool, a new connection will be open.
Keeping a connection idle for a long time can cause problems (like in this issue with MySQL on Microsoft Azure). Try db.SetMaxIdleConns (0) if you get connection timeouts because a connection is idle for too long.
I believe that when using database/sql, the answer is "no", though I'd be happy to be corrected.
A while back, we switched from database/sql with lib/pq to the pure Go jackc/pgx for similar reasons (the inability to control connections on a low level), and were happy with the result. That module offers ConnPool.Reset which sounds like what you want here:
Reset closes all open connections, but leaves the pool open. It is intended for use when an error is detected that would disrupt all connections (such as a network interruption or a server state change).
It is safe to reset a pool while connections are checked out. Those connections will be closed when they are returned to the pool.
189
Your best bet is probably Postgres's DISCONNECT command.
_, err := conn.ExecContext(context.Background(), "DISCONNECT")
will cause the connection to be closed, from the server side.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
