postgresql - 微服务使 postgres 连接倍增

Question

我有一个通过 golang 编写的服务，它作为消费者工作，它从 kafka 的队列中获取数据并将其存储在 PostgreSQL 数据库中。在执行某些请求时，golang 开始增加 PG 连接，然后超过它们的限制。我不知道为什么，请帮我解决这个问题。这是代码:

func SaveMessageStatus(msg models.Message) error {

db := GetPostgreInstance() // Get *sql.DB instance

// Проверяем есть ли записи

rows, err := db.Query(`select * from "tNotifStatus" where "NotificationId" = $1`, msg.NotificationID)

if err != nil {
    CheckError(err, "SLCT status", "")
    return err
}

if rows.Next() {
    // Смотрим если запись в таблице уже есть, тогда просто обновляем статусы по сообщению
    fsql := `update "tNotifStatus" set "Status" = $1, "Error" = $2, "UpdateTime" = $3 where "NotificationId" = $4`

    _, err = db.Exec(fsql, msg.Status, msg.Error, msg.UpdateTime, msg.NotificationID)

    if err != nil {
        //Логируем
        CheckError(err, "UPDT status", "")
        return err
    }

} else {
    // Если записей нет, то создаем новую
    fsql := `insert into "tNotifStatus" values ($1,$2,$3,$4,$5)`
    _, err = db.Exec(fsql, msg.NotificationID, msg.Status, msg.Error, msg.ChannelName, msg.UpdateTime)
    if err != nil {
        //Логируем
        CheckError(err, "INS status", "")
        return err
    }
}
return err

如果我们看到 PG 管理员监控，我们有很多新连接:

6460 UCS ucs 10.3.40.20 2018-12-27 09:35:14 +06 空闲客户端:ClientRead
46462 UCS ucs 10.3.40.20 2018-12-27 09:35:17 +06 空闲客户端:ClientRead
46463 UCS ucs 10.3.40.20 2018-12-27 09:35:17 +06 空闲客户端:ClientRead
46517 UCS ucs 10.3.40.20 2018-12-27 09:36:20 +06 空闲客户端:ClientRead
46518 UCS ucs 10.3.40.20 2018-12-27 09:36:21 +06 空闲客户端:ClientRead
46520 UCS ucs 10.3.40.20 2018-12-27 09:36:22 +06 空闲客户端:ClientRead
46521 UCS ucs 10.3.40.20 2018-12-27 09:36:23 +06 空闲客户端:ClientRead
46522 UCS ucs 10.3.40.20 2018-12-27 09:36:23 +06 空闲客户端:ClientRead
46524 UCS ucs 10.3.40.20 2018-12-27 09:36:24 +06 空闲客户端:ClientRead
46525 UCS ucs 10.3.40.20 2018-12-27 09:36:24 +06 空闲客户端:ClientRead
46527 UCS ucs 10.3.40.20 2018-12-27 09:36:25 +06 空闲客户端:ClientRead
46529 UCS ucs 10.3.40.20 2018-12-27 09:36:25 +06 空闲客户端:ClientRead
46531 UCS ucs 10.3.40.20 2018-12-27 09:36:26 +06 空闲客户端:ClientRead
46532 UCS ucs 10.3.40.20 2018-12-27 09:36:26 +06 空闲客户端:ClientRead
46534 UCS ucs 10.3.40.20 2018-12-27 09:36:27 +06 空闲客户端:ClientRead

他们都试图执行

select * from "tNotifStatus" where "NotificationId" = $1

可能与等待事件(ClientRead)有关，golang lib 开始新连接，而旧连接仍在尝试完成“SELECT”

这里是连接数据库的代码

    func GetPostgreInstance() *sql.DB {
    return postgreClient
}

func InitPqConnection() {

    var err error

    var configuration models.Configuration
    configuration.Load()

    connStr := configuration.PostgreConnString

    postgreClient, err = sql.Open("postgres", connStr)
    if err != nil {
        CheckError(err, "Connection to db", "")
        panic("Error connection to DB")
    }
    fmt.Println("Connected to db")
}

Answer 1

您需要使用 QueryRow()，因为您没有使用也不期望多行，或者执行 defer rows.Close()(您应该在使用 .Query

时 总是

https://golang.org/pkg/database/sql/#Rows.Close

Close closes the Rows, preventing further enumeration. If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.

http://go-database-sql.org/retrieving.html

If for some reason you exit that loop – an early return, or so on – then the rows doesn’t get closed, and the connection remains open.

rows, err := db.Query(`select * from "tNotifStatus" where "NotificationId" = $1`, msg.NotificationID)
if err != nil {
    CheckError(err, "SLCT status", "")
    return err
}
defer rows.Close()

由于您甚至没有遍历 rows.Next()，您基本上永远不会达到自动关闭条件，并且始终保持连接打开。