Pgxpool在扫描时返回“连接池已关闭”错误。

huangapple go评论87阅读模式
英文:

Pgxpool returns "pool closed" error on Scan

问题

我正在尝试在一个新的Go应用程序中实现pgxpool。在尝试将数据扫描到结构体后,我不断收到"pool closed"错误。

连接后,pgx记录器给出了以下信息。我原以为pgxpool应该保持打开状态。

> {"level":"info","msg":"closed connection","pid":5499,"time":"2022-02-24T16:36:33+10:30"}

这是我的路由器代码:

func router() http.Handler {
    var err error

    config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))

    if err != nil {
        log.Fatalln(err)
    }

    log.Println(os.Getenv("DATABASE_URL"))

    logrusLogger := &logrus.Logger{
        Out:          os.Stderr,
        Formatter:    new(logrus.JSONFormatter),
        Hooks:        make(logrus.LevelHooks),
        Level:        logrus.InfoLevel,
        ExitFunc:     os.Exit,
        ReportCaller: false,
    }

    config.ConnConfig.Logger = NewLogger(logrusLogger)

    db, err := pgxpool.ConnectConfig(context.Background(), config)

    if err != nil {
        log.Fatalln(err)
    }

    defer db.Close()

    // minio connection

    rs := newAppResource(db, mc)
    // ...
}

然后,在一个辅助文件中,我设置了资源:

type appResource struct {
    db *pgxpool.Pool
    mc *minio.Client
}

// newAppResource函数用于传递全局变量
func newAppResource(db *pgxpool.Pool, mc *minio.Client) *appResource {
    return &appResource{
        db: db,
        mc: mc,
    }
}

在这段代码的末尾,出现了"pool closed"错误:

func (rs *appResource) login(w http.ResponseWriter, r *http.Request) {
    // ...
    row := rs.db.QueryRow(context.Background(), sqlStatement, login.Email)

    err = row.Scan(&user.UserId, &user.Password)

    if err == sql.ErrNoRows {
        log.Println("user not found")
        http.Error(w, err.Error(), http.StatusUnauthorized)
        return
    }

    if err != nil {
        log.Println(err)
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
}

希望这能帮到你!如果你有任何其他问题,请随时问我。

英文:

I'm trying to implement pgxpool in a new go app. I keep getting a "pool closed" error after attempting a scan into a struct.

The pgx logger into gives me this after connecting. I thought the pgxpool was meant to remain open.

> {"level":"info","msg":"closed connection","pid":5499,"time":"2022-02-24T16:36:33+10:30"}

Here is my router code

func router() http.Handler {

	var err error

	config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))

	if err != nil {
		log.Fatalln(err)
	}

	log.Println(os.Getenv("DATABASE_URL"))

	logrusLogger := &logrus.Logger{
		Out:          os.Stderr,
		Formatter:    new(logrus.JSONFormatter),
		Hooks:        make(logrus.LevelHooks),
		Level:        logrus.InfoLevel,
		ExitFunc:     os.Exit,
		ReportCaller: false,
	}

	config.ConnConfig.Logger = NewLogger(logrusLogger)

	db, err := pgxpool.ConnectConfig(context.Background(), config)

	if err != nil {
		log.Fatalln(err)
	}

	defer db.Close()

--- minio connection

rs := newAppResource(db, mc)

Then, in a helper file I setup the resource

type appResource struct {
   db *pgxpool.Pool
   mc *minio.Client
}

// newAppResource function to pass global var
func newAppResource(db *pgxpool.Pool, mc *minio.Client) *appResource {
	return &appResource{
    	db: db,
	    mc: mc,
	}
}

There "pool closed" error occurs at the end of this code

func (rs *appResource) login(w http.ResponseWriter, r *http.Request) {

	var user User
	var login Login

	d := json.NewDecoder(r.Body)
	d.DisallowUnknownFields() // catch unwanted fields

	err := d.Decode(&login)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err != nil {
		fmt.Println("can't decode JSON", err)
	}

	if login.Email == "" {
		log.Println("empty email")
		return
	}

	log.Println(login.Email)

	log.Println(login.Password)

	if login.Password == "" {
		log.Println("empty password")
		return
	}

	// optional extra check
	if d.More() {
		http.Error(w, "extraneous data after JSON object", http.StatusBadRequest)
		return
	}

	sqlStatement := "SELECT user_id, password FROM users WHERE active = 'true' AND email = ?"

	row := rs.db.QueryRow(context.Background(), sqlStatement, login.Email)

	err = row.Scan(&user.UserId, &user.Password)

	if err == sql.ErrNoRows {
		log.Println("user not found")
		http.Error(w, err.Error(), http.StatusUnauthorized)
		return
	}

	if err != nil {
		log.Println(err)
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

答案1

得分: 4

看起来你正在做类似以下的事情:

func router() http.Handler {
   db, err := pgxpool.ConnectConfig(context.Background(), config)
   if err != nil {
       log.Fatalln(err)
   }
   defer db.Close()

   return appResource{db: db}
}

这种情况的问题在于 defer db.Close() 在函数 router() 结束时运行,而在实际使用返回的 pgxPool.Pool 之前(返回的 http.Handler 将在稍后处理 http 请求时使用)。尝试使用已关闭的 pgxPool.Pool 会导致你看到的错误。

最简单的解决方案是简单地移除 defer db.Close(),但你也可以考虑在清理关闭过程中调用 db.Close()(只要你处理请求,它就需要保持打开状态)。

你正在使用 pgxpool,它与标准库有所不同;然而,我认为标准库文档中给出的建议在这里同样适用:

> 很少需要关闭 DB。

英文:

It appears that you are doing something like the following:

func router() http.Handler {
   db, err := pgxpool.ConnectConfig(context.Background(), config)
   if err != nil {
       log.Fatalln(err)
   }
   defer db.Close()

   return appResource{db: db}
}

The issue with this is that the defer db.Close() runs when the function router() ends and this is before the returned pgxPool.Pool is actually used (the http.Handler returned will be used later when http requests are processed). Attempting to use a closed pgxPool.Pool results in the error you are seeing.

The simplest solution is to simply remove the defer db.Close() however you might also consider calling db.Close() as part of a clean shutdown process (it needs to remain open as long as you are handling requests).

You are using pgxpool which does differ from the standard library; however I believe that the advice given in the standard library docs applies here:

> It is rarely necessary to close a DB.

huangapple
  • 本文由 发表于 2022年2月24日 15:17:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/71248378.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定