Why is it complaining 'FATAL #53300 sorry, too many clients already' when I am using only one client?

[sayak-dev]

I have a global instance to Postgres database which is being used throughout the code.

There are 500 goroutines inserting some data into the database using the same instance.

Expected Behavior

My understanding is that, there is just one instance of the database which is being created by ConnectToDB() method when it is called for the first time & on the subsequent calls, it merely returns this instance instead of creating a new one.

Finally when main ends, that one single instance is closed.

Current Behavior

It fails with panic: FATAL #53300 sorry, too many clients already

Steps to Reproduce

go run main.go

package main

import (
    "github.com/go-pg/pg/v10"
    "github.com/go-pg/pg/v10/orm"
    "sync"
)

var db *pg.DB
var once sync.Once


type DummyTable struct {
    IdOne int
    IdTwo int
}

func main() {
    var wg sync.WaitGroup

    db := ConnectToDB()
    defer db.Close()

    for i := 0; i < 500; i++ {
        wg.Add(1)
        go insertIntoDB(&wg)
    }
    wg.Wait()
}

func insertIntoDB(wg *sync.WaitGroup) {
    defer wg.Done()
    localDb := ConnectToDB()

    _, err := localDb.Model(&DummyTable{
        IdOne: 2,
        IdTwo: 3,
    }).Insert()
    if err != nil {
        panic(err)
    }
}

func createSchema(db *pg.DB) error {
    models := []interface{}{
        (*DummyTable)(nil),
    }

    for _, model := range models {
        err := db.Model(model).CreateTable(&orm.CreateTableOptions{
            Temp: false,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

func ConnectToDB() *pg.DB {
    once.Do(func() {
        db = pg.Connect(&pg.Options{
            User:     "username",
            Database: "dbName",
        })
        err := createSchema(db)
        if err != nil {
            panic(err)
        }
    })
    return db
}

I am unable to understand what could possibly cause the creation of more than one client

[vmihailenco]

DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines. See https://pkg.go.dev/github.com/go-pg/pg/v10#DB

[sayak-dev]

@vmihailenco well even in that case, why is it complaining that too many clients are using it? Is my understanding wrong? There is just one client, so how could it have the problem of too many clients using it?

[elliotcourant]

The DB object will create multiple "clients" from PostgreSQL's perspective. A client is essentially a single network connection from your program to the PostgreSQL database.

You are getting this error because go-pg's pool is spawning many connections to handle all the queries you are sending. You need to reduce the number of go routines you are using. Or you need to increase the maximum number of connections your PostgreSQL server allows. Information about that can be found here: https://www.postgresql.org/docs/13/runtime-config-connection.html

[elliotcourant]

@vmihailenco what are your thoughts of doing something like SHOW max_connections; and limit the size of the pool in go-pg to whatever that returns?

[sayak-dev]

The DB object will create multiple "clients" from PostgreSQL's perspective. A client is essentially a single network connection from your program to the PostgreSQL database.

You are getting this error because go-pg's pool is spawning many connections to handle all the queries you are sending. You need to reduce the number of go routines you are using. Or you need to increase the maximum number of connections your PostgreSQL server allows. Information about that can be found here: https://www.postgresql.org/docs/13/runtime-config-connection.html

Okay thanks, that makes sense

[vmihailenco]

the problem of too many clients using it

Postgres calls incoming connections (sockets) clients. The error probably should be "too many connections already".

You need to reduce the number of go routines you are using.

Even better would be to use PgBouncer.

@vmihailenco what are your thoughts of doing something like SHOW max_connections; and limit the size of the pool in go-pg to whatever that returns?

With PgBouncer it is not a problem / limiting factor. Besides we need to create a conn pool before we can issue a query...