Connection leak in lib/pq postgres driver

Question

Given that db is of type *sql.DB (uging lib/pq driver), the following code causes connection leak:

    rows, err := db.Query(
        "select 1 from things where id = $1",
        thing,
    )
    if err != nil {
        return nil, fmt.Errorf("can't select thing (%d): %w", thing, err)
    }

    found := false

    for rows.Next() {
        found = true
        break
    }

Calling this code repeatedly increases the number of open connections, until exhausted:

select sum(numbackends) from pg_stat_database;
// 5
// 6
// 7
// ...
// 80

How do I fix it?

Jonathan Hall · Accepted Answer · 2019-12-22T11:49:47.023

There are a couple problems with your code as written. The direct answer to your question of avoiding connection leaks is to close the rows iterator as mentioned in the documentation. The normal way to call it is in a defer statement:

    rows, err := db.Query(
        "select 1 from things where id = $1",
        thing,
    )
    if err != nil {
        return nil, fmt.Errorf("can't select thing (%d): %w", thing, err)
    }
    defer rows.Close()

    found := false

    for rows.Next() {
        found = true
        break
    }

Second, since all you ever care about is a single result, there's no reason to fetch a multi-row result set at all, which will implicitly solve the connection leak issue, as well. See this post for a discussion on the quickest way to check for existence in Postgres. If we adapt that here:

    row, err := db.QueryRow(
        "select EXISTS(SELECT 1 from things where id = $1)",
        thing,
    )
    if err != nil {
        return nil, fmt.Errorf("can't select thing (%d): %w", thing, err)
    }
    var found bool
    if err := row.Scan(&found); err != nil {
        return nil, fmt.Errorf("Failed to scan result: %w", err)
    }

Connection leak in lib/pq postgres driver

1 Answers1