How to kill running queries on connection close

Question

We are using https://github.com/go-gorm/gorm/ ORM in our backend and scripts to connect to our PostgreSQL database.

Sometimes while a script is in progress, we kill the job manually by pressing Ctrl + C on local or killing the pod / process in production. We do have a defer DB.Close() in all scripts and I also added handling SIGINT / SIGTERM signals to do DB.Close() on kill.

Issue is even after closing the connection, any existing queries which were already running do not get killed and kept on eating DB resources. Is there a way to kill any queries started by this connection pool before exiting either directly from gorm or through some other hack.

Thought of using pg_backend_pid() and killing queries using pg_stat_activity, but pid we get while running new pg_backend_pid() will not be same as the running ones.

Version: jinzhu/gorm v1.9.2

This is what context.Context is for. Have you read about that? — Flimzy
Also handle the SO stop signal. As example, as described here in this article — Matteo
Thanks @Flimzy @Matteo. db.WithContext method mentioned on gorm doc is not available it seems. We are currently at v1 jinzhu/gorm v1.9.2. If there is any example which I can look at, that would be really helpful. Will also read up on Context meantime. — pratpor

joelazar joelazar · Accepted Answer · 2021-02-02T00:51:42

You could use BeginTx(ctx context.Context, opts *sql.TxOptions) which takes a context as an argument.

And here is a small example for it:


import (
    "context"
    "fmt"
    "os"
    "os/signal"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/postgres"
)

func main() {
    db, err := gorm.Open("postgres", "host=localhost port=5432 user=gorm dbname=gorm password=mypassword sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    ctx := context.Background()

    ctx, cancel := context.WithCancel(ctx)
    c := make(chan os.Signal, 1)
    signal.Notify(c, os.Interrupt)
    defer func() {
        signal.Stop(c)
        cancel()
    }()
    go func() {
        select {
        case <-c:
            cancel()
        case <-ctx.Done():
        }
    }()

    transaction, err := db.DB().BeginTx(ctx, nil)
    _, err = transaction.Exec("SELECT pg_sleep(100)")
    if err != nil {
        fmt.Println(err.Error())
    }
}

How to kill running queries on connection close

1 Answers