Golang database/sql hangs with SetMaxOpenConns

Question

I have pasted a minimally reproducible test code. In short, with SetMaxOpenConns set to 10, the program hangs forever after 10. I found this relevant thread from way back when, but it seems resolved and tested: https://github.com/golang/go/issues/6593

Note that by commenting out the SetMaxOpenConns the code runs normally.

What am I doing wrong? or should I open a new issue?

   1 package main
   2
   3 import (
   4     "database/sql"
   5     "log"
   6     "time"
   7     _ "github.com/lib/pq"
   8 )
   9
  10 func main(){
  11     // Establish db connection
  12     db, err := sql.Open("postgres", "host=0.0.0.0 port=5432 user=postgres password=password dbname=test sslmode=disable")
  13     if err != nil {
  14         log.Fatal(err)
  15     }
  16
  17     db.SetMaxOpenConns(10) // commenting this line will resolve the problem
  18     db.SetMaxIdleConns(10)
  19     db.SetConnMaxLifetime(10 * time.Second)
  20
  21     // Query more than max open; note that hangs forever
  22     for i:=0; i<12; i++ {
  23     rows, err := Query(db)
  24     if err != nil {
  25         log.Fatal(err)
  26     }
  27     log.Println(i)
  28     log.Println(rows)
  29     }
  30 }
  31
  32 func Query(db *sql.DB) (*sql.Rows, error){
  33     stmt, err := db.Prepare("SELECT * FROM test;")
  34     if err != nil {
  35         log.Fatal(err)
  36     }
  37
  38     defer stmt.Close()
  39
  40     rows, err := stmt.Query()
  41     if err != nil {
  42         log.Fatal(err)
  43     }
  44
  45     return rows, nil
  46 }

user_11810946 · Answer

You need to either fully iterate through the result set with rows.Next and/or call rows.Close(); as per the docs:

Close closes the Rows, preventing further enumeration. If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.

Something like:

 for i:=0; i<12; i++ {
   rows, err := Query(db)
   if err != nil {
      log.Fatal(err)
   }
   log.Println(i)
   log.Println(rows)
   if err = rows.Close(); err != nil {
      panic(err)
   }
}

For this to be useful you need to iterate through the rows (see the example in the docs).

The connection to the database will remain in use until the result set is closed (at which point it is returned to the pool). Because you are doing this in a loop you will end up with 10 active result sets and when you call Query() again the sql package will wait for a connection to become available (which will never happen).

Note that because your query has no parameters (and you are only using the stmt once) calling Prepare has no benefit; the following is simpler and will have the same result:

func Query(db *sql.DB) (*sql.Rows, error) {
    return db.Query("SELECT * FROM test;")
}

Golang database/sql hangs with SetMaxOpenConns

1 Answers