Closing *sql.DB from stdlib.OpenDBFromPool

[mfridman]

Since the pgx pool handles the underlying connections, is it necessary to close both *sql.DB and *pgxpool.Pool, or is closing the pgxpool enough?

db := stdlib.OpenDBFromPool(pool)
db.SetMaxOpenConns(maxConns)

[...]

_ = db.Close() // handle error
pool.Close()

[lzakharov]

It should be better to close both *sql.DB and *pgxpool.Pool, even taking into account the fact that nothing can be done (in current implementation) on closing *sql.DB.

Also, you should not set max open conns in the *sql.DB (check the OpenDBFromPool docs for more info):

db.SetMaxOpenConns(maxConns)

[mfridman]

Is there a situation where *sql.DB might try to create too many connections (more than the pool can acquire)?

Fwiw I'm always setting this value to pool.Config().MaxConns. In this way *sql.DB cannot acquire more connections than are available in the underlying pgxpool.

[lzakharov]

The *sql.DB must not create any connection by itself - it is just an adapter for pgxpool.Pool, where connections live.

Maybe this answer also would be helpful.

[mfridman]

I (briefly) skimmed the implementation and some of the corresponding issues. I get it's an adapter, but I also don't see any downside explicitly capping the *sql.DB connections to the same max set on pgxpool config.

Also, note that we're setting the max conns, not idle conns (which is what the doc mentions)

Note that this method automatically sets the maximum number of idle connections in *sql.DB to zero

db.SetMaxIdleConns(0)

// we are setting 
db.SetMaxOpenConns(pool.Config().MaxConns)

[lzakharov]

Sorry for the late reply. I think that setting limits in the sql.DB can lead to problems with a large number of concurrent requests, since the counters in the sql.DB and pgxpool.Pool are not synchronized. This is not about query execution errors, but about performance issues.

[mfridman]

Yeah, that's fair, I appreciate the insight. I also went and checked SetMaxOpenConns, and there are cases where it'll set SetMaxIdleConns internally:

func (db *DB) SetMaxOpenConns(n int) {
	db.mu.Lock()
	db.maxOpen = n
	if n < 0 {
		db.maxOpen = 0
	}
	syncMaxIdle := db.maxOpen > 0 && db.maxIdleConnsLocked() > db.maxOpen
	db.mu.Unlock()
	if syncMaxIdle {
		db.SetMaxIdleConns(n) // <--------- HERE
	}
}

https://github.com/golang/go/blob/7255b949202bb752b6525aa24cb636ceaf24e4d1/src/database/sql/sql.go/#L1037-L1039