golang 操作 postgres 定期清理僵尸sql语句

package main

import (
	"flag"
	"fmt"
	"github.com/fwhezfwhez/errorx"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/postgres" // 导入postgres
	"log"
	"time"
)

var mode string
var db *gorm.DB

func init() {
	flag.StringVar(&mode, "mode", "local", " go run main.go -mode 'local'")
	flag.Parse()

	log.Println("mode:", mode)
	var dataSource string
	switch mode {
	case "local":
		dataSource = fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=%s password=%s", "localhost", "5432", "postgres", "game", "disable", "123")
	default:
		dataSource = fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=%s password=%s", "10.xx.xx.xx", "5432", "xxx", "game", "disable", "xxxxxxxxxxxxxx")
	}
	var e error
	db, e = gorm.Open("postgres", dataSource)
	if e != nil {
		panic(e.Error())
	}

	db.SingularTable(true)
	db.LogMode(true)
	db.DB().SetConnMaxLifetime(10 * time.Second)
	db.DB().SetMaxIdleConns(30)
}
func main() {
	once := func() {
		var procs = make([]Proc, 0, 20)
		if e := db.Raw(findPids).Scan(&procs).Error; e != nil {
			fmt.Println(errorx.Wrap(e))
			return
		}
		var zombies = make([]Proc, 0, 10)
		for i, _ := range procs {
			if procs[i].Start.IsZero() {
				continue
			}

			if time.Now().Sub(procs[i].Start).Minutes() > 10 {
				zombies = append(zombies, procs[i])
				if e := db.Exec("select  pg_terminate_backend(?)", procs[i].ProcPid).Error; e != nil {
					fmt.Println(errorx.Wrap(e))
					return
				}
			}
		}
		fmt.Println(fmt.Sprintf("%s, 清理了%d条僵尸sql语句", time.Now().Format("2006-01-02 15:04:05"), len(zombies)))
	}

	for {
		once()
		time.Sleep(15 * time.Minute)
	}

}

var findPids = `
SELECT 
procpid, 
start, 
now() - start AS lap, 
current_query 
FROM 
(SELECT 
backendid, 
pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity_start(S.backendid) AS start, 
pg_stat_get_backend_activity(S.backendid) AS current_query 
FROM 
(SELECT pg_stat_get_backend_idset() AS backendid) AS S 
) AS S 
WHERE 
current_query <> '<IDLE>' 
ORDER BY 
lap DESC; 
`

type Proc struct {
	ProcPid      int       `gorm:"column:procpid"`
	Start        time.Time `gorm:"column:start"`
	Lap          []byte    `gorm:"column:lap"`
	CurrentQuery string    `gorm:"column:current_query"`
}

作成函数的话，可以用这个:

func ClearZoombieSQL(DB *gorm.DB, maxMinute float64) (string, error) {
	type Proc struct {
		ProcPid      int       `gorm:"column:procpid"`
		Start        time.Time `gorm:"column:start"`
		Lap          []byte    `gorm:"column:lap"`
		CurrentQuery string    `gorm:"column:current_query"`
	}

	var findPids = `
SELECT 
procpid, 
start, 
now() - start AS lap, 
current_query 
FROM 
(SELECT 
backendid, 
pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity_start(S.backendid) AS start, 
pg_stat_get_backend_activity(S.backendid) AS current_query 
FROM 
(SELECT pg_stat_get_backend_idset() AS backendid) AS S 
) AS S 
WHERE 
current_query <> '<IDLE>' 
ORDER BY 
lap DESC; 
`

	var procs = make([]Proc, 0, 20)
	if e := DB.Raw(findPids).Scan(&procs).Error; e != nil {
		return "", errorx.Wrap(e)
	}
	var zombies = make([]Proc, 0, 10)
	for i, _ := range procs {
		if procs[i].Start.IsZero() {
			continue
		}

		if time.Now().Sub(procs[i].Start).Minutes() > maxMinute {
			zombies = append(zombies, procs[i])
			if e := DB.Exec("select pg_terminate_backend(?)", procs[i].ProcPid).Error; e != nil {
				fmt.Println(errorx.Wrap(e))
				return "", errorx.Wrap(e)
			}
		}
	}
	log.SetFlags(log.LstdFlags | log.Llongfile)
	log.Println(fmt.Sprintf("清理了%d条僵尸sql语句", len(zombies)))
	return fmt.Sprintf("清理了%d条僵尸sql语句", len(zombies)), nil
}