jackc / jackc/pgx

Runaway connection attempts with `pgxpool`

Open
#1,780 20 comments 4 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
Go
Stars
14.3k
Forks
1.1k
Avg merge
6d 9h
Merged PRs (30d)
11

Description

**Describe the bug**
I have an application using a `pgxpool.Pool` (pgx/v4 v4.18.1) to manage connections to a PgBouncer instance, and ultimately to a PostgreSQL 15 server.

**To Reproduce**
I am currently trying to reproduce this in a simple manner. The connection setup looks like this:

```go
poolConfig, err := pgxpool.ParseConfig("postgres://postgres:postgres@localhost:5432/postgres")
if err != nil {
sugar.Fatalf("error parsing DB URL: %v", err)
}
poolConfig.MaxConns = 4
poolConfig.MinConns = 4
poolConfig.ConnConfig.Logger = zapadapter.NewLogger(logger)
poolConfig.ConnConfig.LogLevel = pgx.LogLevelDebug

dbPool, err := pgxpool.ConnectConfig(ctx, poolConfig)
if err != nil {
sugar.Fatalf("error connecting to DB: %v", err)
}
```

We're using the `QueryRow()` etc directly on the pool and not using `Acquire()`. The resulting `Row` is passed to `Scan()` eventually.

**Expected behavior**
The pool should fill and keep 4 connections.

**Actual behavior**
The pool fills up, then for some unknown reason, it is attempting to create more connections constantly. The data from `pool.Stat()` indicates there are already 4 total connections, but continues to attempt to create more.

At the debug log level, `pgxpool` is not clearly indicating why it is creating multiple connections, but it is easy to see the attempts:
image

Note: the connection failures here are all PgBouncer running out of client connections, which has way more than pool size * number of copies of app.

```
server error (FATAL: no more connections allowed (max_client_conn) (SQLSTATE 08P01))
```

Stat info for the above logs:
before connection runaway (`2023-10-26 05:34:14.968`):
```json
{
"max_lifetime_destroy_count": 0,
"constructing_connections": 0,
"acquired_connections": 0,
"empty_acquire_count": 0,
"new_connections_count": 4,
"acquire_count": 1,
"acquire_duration": 2.6e-7,
"max_idle_destroy_count": 0,
"total_connections": 4,
"canceled_acquire_count": 0,
"message": "Database pool stats",
"idle_connections": 4,
"timestamp": "2023-10-26T09:34:14.968Z",
"max_connections": 4
}
```

shortly after connection runaway (`2023-10-26 05:34:29.969`):
Note: I thought this is probably when it received its first query attempt, but no query is logged (which eventually happens with the logger attached)

```json
{
"idle_connections": 0,
"canceled_acquire_count": 0,
"constructing_connections": 0,
"max_connections": 4,
"message": "Database pool stats",
"total_connections": 4,
"max_lifetime_destroy_count": 0,
"empty_acquire_count": 5,
"timestamp": "2023-10-26T09:34:29.969Z",
"max_idle_destroy_count": 0,
"acquire_duration": 34.920843834,
"acquire_count": 10,
"acquired_connections": 4,
"new_connections_count": 4
}
```

1 minute after connection runaway (`2023-10-26 05:35:24.975`):
```json
{
"max_lifetime_destroy_count": 0,
"idle_connections": 0,
"new_connections_count": 15,
"max_idle_destroy_count": 0,
"empty_acquire_count": 299,
"acquired_connections": 3,
"acquire_count": 304,
"message": "Database pool stats",
"constructing_connections": 0,
"canceled_acquire_count": 4,
"timestamp": "2023-10-26T09:35:24.975Z",
"acquire_duration": 2790.153294167,
"max_connections": 4,
"total_connections": 3
}
```

**Version**
- Go: 1.20
- PostgreSQL: 15 (CloudSQL), with PgBouncer 1.20.1
- pgx: `v4.18.1`

**Additional context**
I started setting MaxConns and MinConns to mitigate this, with no luck.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the provided pgxpool.ParseConfig and ConnectConfig setup, then reproduce the behavior while collecting the pool.Stat() metrics and debug logs. Trace the transition from four idle connections to repeated acquisition and connection attempts. Done means the cause of the excess attempts is identified and the pool no longer attempts connections beyond the configured capacity.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.