CODEWITHSUNDEEP
databasepostgresqlgopostgresql-9.4
Dharani Dharan
Dharani Dharan

Reputation: 644

Golang-Postgres ..Closing database connection not working for particular query

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.

Upvotes: 3

Views: 7775

Answers (3)

Peter
Peter

Reputation: 2372

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)
}

Upvotes: 1

abhink
abhink

Reputation: 9136

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.

Upvotes: 6

Sameer Azazi
Sameer Azazi

Reputation: 1497

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

Upvotes: 2

Related Questions