微服务成倍增加postgres连接

Question

I have a service wrote via golang, which work as a consumer, It gets data from kafka's queue and stores it in PostgreSQL database. While doing certain request golang starts to multiply PG connections and then exceeds the limit of them. I don't know why, please help me with this issue. Here is the code:

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

}

If we see PG admin monitoring, we have a lot of new connections:

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

They all try to execute

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

May be it is related with Wait Event (ClientRead), golang lib starts new connection while the old one is still trying to finish "SELECT"

Here is code of connection to DB

    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

You need to either use QueryRow(), since you aren't using nor expecting multiple rows, or do a defer rows.Close() (which you should always do when using .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()

Since you are not even iterating over rows.Next() you are basically never hitting the automatic close condition, and always leaving your connections open.