Golang-Postgres ..关闭数据库连接不适用于特定查询

Question

I am using golang to access postgresql. This is my function

for {
    db, err := database.GetNewConnection(dbname)

    err = db.QueryRow("SELECT COALESCE(COUNT(1),0) FROM table").Scan(&count)

    if count == 0 {

        var insert = "INSERT INTO table(last_update_time,next_update_time,schedule_frequency)" +
            "VALUES($1,$2,$3)"
        prep_ins, err := db.Prepare(insert)
        if err != nil {
            return
        }
        _, err = prep_ins.Exec(cur_time, 1464718530, 86400)
        if err != nil {
            return
        }
        defer prep_ins.Close()
        defer db.Close()
    } else {
        var sel_str = "SELECT next_update_time FROM table"
        prep_update, err := db.Prepare(sel_str)
        if err != nil {

            return
        }
        _, err = prep_update.Exec()
        if err != nil {
            defer prep_update.Close()
            return
        }
        defer prep_update.Close()
        defer db.Close()
    }
    time.Sleep(10 * 60 * time.Second)
}

Every 10 mins , this function will run and execute those statement. Its working fine but the connection will be in idle state. I am checking using pg_stat_activity, the state is idle only. Every 10 mins, new connection will be created and went to idle state.so its keep on increasing. i don't know why its happening.

Thanks in advance.

Answer 1

The for loop is an infinite loop and it will never return. This means that defers will never be executed and the connections won't be closed. You need to add explicit db.Close() at the end of the loop.

Also move the initialization of db out of the loop if you don't need a new connection every time you perform the task. You can just create a single connection and reuse it. If you do this, move defer statement just below the initialization of db. If the database operations return some errors then defers will close the connection.

db, err := database.GetNewConnection(dbname)
if err != nil {
    return fmt.Errorf("Error in creating database connection: %+v", err)
}
defer db.Close()
for {

    // ...

    db.Close()
}

By doing this you ensure that the connection gets closed on every return path put of the function (https://blog.golang.org/defer-panic-and-recover)

If you still leave db initialization in the loop, remove the defers since they'll only add to the defer stack while the db connection will be closed explicitly.