pgx: Spurious conn busy - bug in QueryRow() ?
Hello,
I’ve just started using your library, pretty impressed, esp. with JSON handling.
I’m seeing spurious conn busy errors on my development machine. Once they start, they never stop until I restart the app.
The app uses pgx basically from two modules, one “does something” and stores “something” into pgsql, and another is an http API which fetches this data from pgsql. The failures always occur on the http side (which obviously uses separate coroutines from the first, “does something” part).
How can I track down what’s causing these errors? I already checked my code for any leaked row sets or transactions, there don’t seem to be none.
Second, related to the above. What is the default connection pool size if I create my connection like this?
pgsqlConfig, err := pgx.ParseConfig("postgres://user:pass@localhost:5432/db")
if err != nil {
...
}
pgsqlConnection, err := pgx.ConnectConfig(context.Background(), pgsqlConfig)
if err != nil {
...
}
And how can I change it (connection pool size)?
About this issue
- Original URL
- State: open
- Created 5 years ago
- Reactions: 6
- Comments: 20 (2 by maintainers)
Commits related to this issue
- Use connection pool to avoid "conn busy" error Using the pgxpool package to create a connection pool is safer than using a single connection, because the DB functions are concurrently accessed from m... — committed to jacksgt/u9k by jacksgt 4 years ago
- Prevent nested queries to handle pgx bug: https://github.com/jackc/pgx/issues/635 Signed-off-by: Alexander Scheel <alexander.m.scheel@gmail.com> — committed to WillowPatchGames/wpg by cipherboy 4 years ago
This was a good read, thanks.
For anyone coming across this thread from Google, the TLDR here is that in PostgreSQL land, you need to use either:
on top of pgx, or else you will get the dreaded “conn busy” error. This is because, as jackc mentions above, the default connection size is 4. And increasing the connection size it is a bad idea because vanilla pgx is only designed to be used by one process/thread at a time.
These two blogs are recommended reading if you want to know more:
It’s embarrassing to admit this but I want to share why this problem happened to me in case anyone else encounters it. I have an HTTP server and was trying to re-use the same connection for all requests. If you’re doing this, be sure to use a single root/main-level pool, and then acquire a new connection from the pool per incoming http request (or, if you’re using GraphQL, a connection per resolver / dataloader).
You’ll probably want to use a global pool var in a separate database package, and create connections from that wherever you need them.
I’m using GraphQL so my http handler and my pool are in the same file (main.go). When the graphql endpoint is called, I put a pointer to the pool in ctx which is passed into the resolvers, and then every graphql resolver creates its own connection as needed.
Both approaches seem to work fine but in retrospect a global variable would probably be cleaner.
I was using pgxpool with Query and QueryRow and still hitting the same problem as you (also noted about the 10 vs 20 goroutines on my own so our experience seems to be very much he same). My fix wont work because the pool gets released to early for QueryRow unfortunately.