pq: sorry, too many clients already

Question

I am getting pq: sorry, too many clients already error when I am calling the GetMessages() multiple times.

Please find the updated code:

main() code

func main() {
  dbConn, err := InitDB()
  if err != nil {
    Log.Error("Connection Error: ", err.Error())
    return
  }
  defer dbConn.Close()
  go run()
  var input string
  fmt.Scanln(&input)
}

Database connection code is:

func InitDB()(*sql.DB, error) {
  connectionString := fmt.Sprintf("user=%v password='%v' dbname=%v sslmode=disable", USER, PASSWORD, DATABASE)
  db, err = sql.Open(DRIVER, connectionString)
  return db, err
}

run goroutine:

func run() {
  for {
    messages, err := GetMessages()
    if err != nil {
      Log.Error("Connection Error: ", err.Error())
      return
    }
    log.Info(messages)
  }
}

GetMessages() function code:

func GetMessages() (messages []string, err error) {
    rows, err := db.Query(`SELECT message1, message2, message3, message4, message5,
            message6, message7, message8, message9, message10, message11, message12, message13, 
            message14, message15, message16, message17, message18, message19, message20, message21,
            message22, message23, message24, message25, message26, message27, message28, message29,
            message30, message31, message32, message33, message34, message35, message36, message37,
            message38, message39, message40, message41, message42, message43, message44, message45,
            message46, message47, message48 FROM table1 WHERE id=1`)

    if err != nil {
        Log.Error("Query error", err)
        return messages, err
    }

    var pointers []interface{}
    defer rows.Close()

    for rows.Next() {
        pointers = make([]interface{}, 48)
        messages = make([]string, 48)
        for i, _ := range pointers {
            pointers[i] = &messages[i]
        }
        err = rows.Scan(pointers...)
        if err != nil {
            Log.Error("Failed to scan row", err)
            return messages, err
        }
    }

    return messages, nil
}

I checked this answer and I have used scan but still it isn't working

UPDATE

Issue was in another function. I was using db.Query without closing the returned rows object and was repeatedly calling that function. I've updated my code; used db.Exec instead of db.Query and it's working now. Thank you so much @mkopriva for this answer. :)

Your `GetMessages` code looks fine to me. Where and how many times are you executing the connection code? — mkopriva, Dec 21 '18 at 14:30
I am calling connection only once from another function. `db` is global variable of type `*sql.DB` — Bhavana, Dec 21 '18 at 14:41
... although not related to the issue, the `WHERE id=1` suggests that you're selecting a single row and if that's the case i would recommend you use `db.QueryRow` instead of `db.Query`. (https://play.golang.com/p/pMsuG2alB1v) — mkopriva, Dec 21 '18 at 14:41
Check what the `max_connection` setting of your postgres server is. ([click here if you don't know how](https://stackoverflow.com/a/8288860/965900)) Also check the number of already existing connections by using `SELECT COUNT(*) from pg_stat_activity;` and/or try inspecting the connections with `SELECT * FROM pg_stat_activity;` during the execution of your go app. — mkopriva, Dec 21 '18 at 14:52
Tried `db.QueryRow` but no luck. Will try `SELECT * FROM pg_stat_activity;` and let you know the output — Bhavana, Dec 21 '18 at 15:13
How are you deploying this? Is it possible that there are MANY instances of your code running? — poy, Dec 21 '18 at 16:26
I wonder what the full function that makes the connection looks like. Because you have a deferred connection close in it, so if that function returned your connection would be closed, but if it does not return, I wonder if it might be opening more and more connections in a loop somewhere — Mad Wombat, Dec 21 '18 at 17:29
@MadWombat Please check the updated code. I am deferring the `db.Close` in `main`. — Bhavana, Dec 22 '18 at 05:57
@KavehShahbazian, No, I am not calling `GetMessages` concurrently — Bhavana, Dec 22 '18 at 05:59
The code in the question is incomplete or inconsistent. The `GetMessages` function uses package-level variable `db`, but you don't show how the variable set. The `main` function assigns a database connection to local variable `dbConn`, but that variable is only used to close the connection. — Charlie Tumahai, Jun 02 '21 at 03:47
start your database connection once. and pass it using a pointer to all your functions. you are creating many connections and that's why you get this warning. — Eddwin Paz, Jun 30 '21 at 18:11
Consider answering your own question with your solution and marking it as correct. It'll give you some points and remove the lingering question from the unanswered section :) — Gustavo Kawamoto, Jul 15 '21 at 16:21

score 3 · Answer 1 · answered Aug 24 '21 at 18:13

3

Try setting SetMaxOpenConns. The default is 0 (unlimited). This may be causing the issue. It would help if you also had SetConnMaxLifetime; otherwise, Postgres will start holding connections longer, and you will notice an increase in memory usage.

answered Aug 24 '21 at 18:13

titogeo

2,156
2
24
41

score 0 · Answer 2 · edited Dec 29 '21 at 19:14

I've had the same problem with my postgres / golang project.

Eventually, this example worked flawlessly, without "eating" any DB connections:

// example params
firstName := "Jeremy"
lastName := "Baker"

// setup statement
stmt, err := db.Prepare(
    `INSERT INTO user (
        firstname,
        lastname) VALUES($1, $2)
        RETURNING id`) // id is the primary key of table: user
if err != nil {
    return err
}
defer stmt.Close()

// execute statement
var userID string
err = stmt.QueryRow(
    firstName,
    lastName).Scan(&userID)
if err != nil {
    return err
}

pq: sorry, too many clients already

2 Answers2