sql - lib/pq postgres驱动程序中的连接泄漏

Question

假设db的类型为*sql.DB(使用ugt lib/pq驱动程序)，则以下代码会导致连接泄漏:

    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
    }

反复调用此代码会增加打开的连接数，直到耗尽:

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

我如何解决它？

Answer 1

您编写的代码有几个问题。您避免连接泄漏的问题的直接答案是关闭rows迭代器as mentioned in the documentation。正常的调用方式是在defer语句中:

    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
    }

其次，由于您只关心单个结果，因此根本没有理由获取多行结果集，这也将隐式解决连接泄漏问题。有关在Postgres中检查存在性的最快方法的讨论，请参见 this post。如果我们在此处进行调整:

    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)
    }