"pq: sorry, too many clients" - using Scan(), closing rows, but getting a connection leak somewhere

Question

My code is set up such that every time I want to make a query I open the DB, run QueryRow(), Scan the results into variables, and then close the DB and rows using defer. However, I'm getting a connection leak though I believe I've closing all of them out.

I have tried taking the close statements out of defer, closing them just when I am done using it. I followed How to reuse a single Postgres DB connection for row inserts in Go? but though I believe I'm following the answer, still no luck.

func (a AccessPostgres) OpenDB() (*sql.DB, error) {
    dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
                            user, password, a.DBName)

    var err error

    db, err := sql.Open("postgres", dbinfo)
    if err != nil {
        return nil, err
    }

    return db, nil
}

func (a AccessPostgres) Run(s string) error {
    db, err := a.OpenDB()
    if err != nil {
        return err
    }
    defer db.Close()

    _, err = db.Exec(s)
    if err != nil {
        return err
    }

    return nil
}

type Row struct {
    Collection_time string
    Dbid            string
    Hostname        string
    Dbname          string
    Name            string
    Value           string
}

type QueryResult struct {
    Rows  []Row
}

func (a AccessPostgres) Query(q string) ([]byte, error) {

    db, err := a.OpenDB()
    if err != nil {
        return nil, err
    }
    defer db.Close()

    rows, err := db.Query(q)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var qr QueryResult
    for rows.Next() {
        var r Row
        err := rows.Scan(&r.Collection_time, &r.Dbid, &r.Hostname, &r.Dbname, &r.Name, &r.Value)
        if err != nil {
            return nil, err
        }
        qr.Rows = append(qr.Rows, r)
    }


    result, err := json.Marshal(qr)
    if err != nil {
        return nil, err
    }

    return result, nil
}

For the most part things work well. But this code is run at an interval and after awhile the 'pq: sorry, too many clients' error shows up. That's all that happens.

Is this the only code in the application that is accessing postgres? — Adrian, Aug 05 '19 at 13:31
Also, it takes awhile for the error to appear. Just if that sheds any light on anything. — Davenporten, Aug 05 '19 at 13:38
Probably unrelated, but the [`sql.Open` documentation](https://golang.org/pkg/database/sql#Open) specificly says you shouldn't call it repeatedly like you do: "The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB." — Dave C, Aug 05 '19 at 14:17
Is there a specific reason why you're creating a new db connection pool every time you need to make a query? — mkopriva, Aug 05 '19 at 14:18
No, there isn't, I just thought it made sense to open it and close it. I will make some changes and give it a try. Thanks all for your help! — Davenporten, Aug 05 '19 at 14:30

"pq: sorry, too many clients" - using Scan(), closing rows, but getting a connection leak somewhere

0 Answers0