doujiao2014 2016-06-29 09:27
浏览 66
已采纳

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

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.

  • 写回答

3条回答 默认 最新

  • dongnao3990 2016-06-29 09:53
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择