jackc
Connections not Released when using OpenDBFromPool
See #523.
When using the new stdlib.OpenDBFromPool function to create an *sqlx.DB connection from a connection pool, it appears that the connections are never released from the pgx connection pool.
Background
We have an application that uses sqlx almost exclusively, but there are two very targeted cases where we need to use a native pgx.Conn instead. I was hoping to be able to use the same connection pool for both sqlx and native pgx connections. I tried modifying our application to use stdlib.OpenDBFromPool, and it worked well, but I noticed that connections were not being released.
Example I configured the application to periodically dump both sqlx and pgx connection pool stats. After running a while, sqlx reports plenty of available connections, but the pgx pool reports that all connections are in use. No native pgx connections were in use during this test
2019/08/14 14:49:35 SQLX Connection Stats: {MaxOpenConnections:10 OpenConnections:2 InUse:1 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxLifetimeClosed:0}
2019/08/14 14:49:35 PGX Connection Pool Stats: {MaxConnections:10 CurrentConnections:10 AvailableConnections:0}
Possible Solution
I think that, when sqlx closes a connection in its pool, the underlying connection is not being released from the pgx connection pool. I've looked at the pgx source code, and I can't find any references where Release is called when a connection is closed. I expect that we'll need to add a connPool attribute to the connection struct and then release the connection from the pool when the connection is closed if this attribute is set (similar to the way a connection is released from the pool when a Rows object is closed). However, I'm new to this code, so I'm not sure exactly where it should go.
On a side note, is there a better way to get a native pgx connection from the sqlx connection pool? If so, I don't need to use stdlib.OpenDBFromPool at all. I've tried using stdlib.AcquireConn, but it specifically notes that it "should not be run ... to use pgx specific functionality".
From https://github.com/jackc/pgx/blob/master/stdlib/sql.go#L44-L47
This allows operations that must be performed on a single connection, but should not be run in a transaction or to use pgx specific functionality.
I'm not familiar with what sqlx is doing so I can't say much about what might be happening with the interaction between the pools.
But with regards to stdlib.AcquireConn it appears the documentation is ambiguous. What it is saying is that is that it is designed for two use cases:
- operations that must be performed on a single connection, but should not be run in a transaction
- to use pgx specific functionality
So using it to get a pgx connection is exactly what it is designed for.
@fzerorubigd Any chance you have observed this behavior when using a pgx connection pool with sqlx?
Our use case is limited, we use pgx mostly everywhere, and the sql version only with the migration tool (which is a 3rd-party library) and no problem so far.
From reading the code for #523 tonight, I agree with @jonyoder that it's not at all obvious how these connections acquired from the pool would get released. I can't find any call path that leads from the standard library types back to (*pgxpool.Conn).Release.
Past that, I can't find the stdlib/opendbpool.go file introduced in #523 in the "master" or "v4" branch. Is it still part of this library?
Past that, I can't find the stdlib/opendbpool.go file introduced in #523 in the "master" or "v4" branch. Is it still part of this library?
At the present it is not -- though not because it was explicitly removed -- it was added to v3 after v4 development had branched off and was never ported to the new connection pool.
We're looking at using pgxpooling due to the min_pool_size feature as the primary connection pool. Is it possible that this issue is related to db.SetMaxIdleConns(0) missing?
We seem to be running into this issue as well. We use OpenDBFromPool to create an Ent client. But as soon as we perform any action on the *sql.DB we are not able to close the *pgxpool.Pool anymore.
