Pgxpool returns "pool closed" error on Scan

Question

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
    }

Answer 1

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.