8

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?

borellini
  • 365
  • 5
  • 13
  • You can force a second connection, by starting a transaction on the first, then opening a second connection, before committing or rolling back the transaction. But that's only a possible work-around. – Jonathan Hall Aug 15 '18 at 13:10
  • Out of curiosity, how do you demote a postgres master without restarting it (which breaks connections)? You can't turn on replication at runtime, right? – Peter Aug 15 '18 at 13:12
  • 1
    @Peter I'm curious, too, since after asking I realised that a TCP connection made to the previous master must have died in the failover - but I nevertheless got a read-only connection hanging in my pool. My best explanation now is that the connection was acquired between the actual failover and the DNS change reflecting the failover. The DB implementation is Amazon Aurora PostgreSQL, if that is interesting. – borellini Aug 15 '18 at 13:35

3 Answers3

3

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.

Community
  • 1
  • 1
robx
  • 2,221
  • 1
  • 14
  • 31
2

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.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
0

I think you can discard it by using the Raw function and returning a driver.ErrBadConn error:

conn.Raw(func(interface{}) error { return driver.ErrBadConn })
Pomyk
  • 3,288
  • 1
  • 17
  • 8