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.

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.

As @abhink has pointed out, your defer statement is not being called due to forever running for loop. Your implementation for scheduled execution of a piece of code using for loop is not optimal.

You should be using golang channels instead.

package main

import (
    "fmt"
    "time"
)

func main() {
    //You can configure your tick for 10 mnts.
    tick := time.Tick(100 * time.Millisecond)
    //some exit condition
    exit := time.After(1000 * time.Millisecond)
    for {
        select {
        case <-tick:
            poke()
        case <-exit:
            fmt.Println("Exit")
            return
        default:
            fmt.Println("    .")
            time.Sleep(50 * time.Millisecond)
        }
    }
}

func poke() {
    fmt.Println("Opening connection")
    defer fmt.Println("Closing connection")
    fmt.Println("Inserting into Database")
}

You can change pock() function to insert record into database. And you can configure ticker to tick at every 10 mnts. Since every tick triggers a new function call, your defer will be called once pock() function execution completes.

Refer this go playground snippet https://play.golang.org/p/1fQgbmI9LY

defer functions are only called when the function returns. In your loop the defer db.Close() are never executed while the loop is active, hence the functions never called.

You can wrap the logic in the for in a closure to effect the defers:

for{
   func(){
       // inner code 
   }()
   time.Sleep(10 * 60 * time.Second)
}