database/sql and pq driver - Limit number of concurrent connections

[daniel....@gmail.com]

Hi *,

I read almost all posts and articles (specially liked this one) I could find regarding the use of the database/sql package. As far as I understood, database connections are started when executing, for example the "Query" method, and not closed until all the Rows are read. With that information in mind, I decided to create a wrapper on top of the database/sql package that will allow me to limit the number of connections. Unfortunately, it doesn't work and PostgreSQL ends up complaining about "too many clients already".

Below is the code I am using to experiment with a maximum number of clients of 5:

package main

import (

"backend-go/client"

"backend-go/service"

"fmt"

)

func main() {

client := client.NewClient(5, 5)

defer client.Close()

done := make(chan int, 100)

for i := 0; i < 100; i++ {

go service.GetIdentifierAndStreet(client, i, done)

}

i := 0

for {

fmt.Printf("Iteration %d\n", i)

if i == 100 {

return

}

<-done

fmt.Printf("Remaining: %d\n", 100-i)

i += 1

}

}

The following is the code of the database Client I implemented to handle the maximum number of concurrent connections.

package client

import (

"database/sql"

"fmt"

"log"

)

type Client struct {

maxConnections     int

maxIdleConnections int

connectionPool     chan int

CurrentConnections int

*sql.DB

}

func NewClient(maxConnections int, maxIdleConnections int) *Client {

return &Client{

maxConnections:     maxConnections,

maxIdleConnections: maxIdleConnections,

connectionPool:     initializeConnectionPool(maxConnections),

DB:                 setupConnection(maxIdleConnections),

currentConnections: 0,

}

}

func (client *Client) Query(query string, args ...interface{}) (*sql.Rows, error) {

<-client.connectionPool

client.currentConnections += 1

fmt.Printf("# of Connections: %d\n", client.currentConnections)

defer func() {

client.currentConnections -= 1

client.connectionPool <- 1

}()

return client.DB.Query(query, args...)

}

func setupConnection(maxIdleConnections int) *sql.DB {

db, err := sql.Open("postgres", "user=damselem dbname=backend_development sslmode=disable")

if err != nil {

log.Fatal(err)

}

db.SetMaxIdleConns(maxIdleConnections)

return db

}

func initializeConnectionPool(maxConnections int) chan int {

sem := make(chan int, maxConnections)

for i := 0; i < maxConnections; i++ {

sem <- 1

}

return sem

}

And finally, the package that uses the Client to send a query to PostgreSQL. Does anyone notice anything that I might be doing wrong? Take in mind that I have about 2 days experience in Go.

BTW, I am aware that Go 1.2 will add a SetMaxOpenConns method to solve this specific problem, but since I am trying to learn how to use goroutines and channels I really want to understand what I am doing wrong.

Thanks!

[Tad Glines]

The actual connection in use by sql.DB is not returned to the idle pool (or closed) until the sql.Rows has been closed.

When service.GetIdentifierAndStreet is called 100 times concurrently (as in your example) it will result in up to 100 active (unclosed) sql.Rows and this 100 real DB connections.

In order to prevent connection leaks, you have two choices; you can wrap the sql.Rows so that you can trap the call to Close and release the connection, or implement an executor model where a function (or interface with Execute method) is passed in to the client so that it's execution can be bracketed by the acquisition and release of the connection. The *DB instance would be passed to function or interface method for use only during that invocation.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[daniel....@gmail.com]

Sorry, I forgot to add the service package. As you can see below, I have a defer rows.Close but still get the too many clients error.

package service

import (

"backend-go/client"

"fmt"

"log"

"math/rand"

"time"

)

type Service struct {

Name   string

Source string

}

func GetIdentifierAndStreet(client *client.Client, routine int, done chan int) {

rows, err := client.Query("SELECT pid FROM pg_stat_activity")

if err != nil {

fmt.Println(err)

}

var pids []int

time.Sleep(time.Duration(rand.Intn(10000)) * time.Millisecond)

        defer rows.Close()

for rows.Next() {

var pid int

if err := rows.Scan(&pid); err != nil {

log.Fatal(err)

}

pids = append(pids, pid)

}

if err := rows.Err(); err != nil {

log.Fatal(err)

}

done <- routine

}

This is what I PostgreSQL shows in the terminal when running this command: while :; do psql -h localhost -d backend_development -c "SELECT * FROM pg_stat_activity"; done

 datid |     datname     |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |              query

-------+-----------------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+----------------------------------

 26112 | backend_development | 61438 |       10 | damselem |                  | 127.0.0.1   |                 |       57478 | 2013-09-20 17:52:53.6461+02   |                               | 2013-09-20 17:52:53.650211+02 | 2013-09-20 17:52:53.652384+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61436 |       10 | damselem |                  | 127.0.0.1   |                 |       57476 | 2013-09-20 17:52:53.645067+02 |                               | 2013-09-20 17:52:53.648908+02 | 2013-09-20 17:52:53.65099+02  | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61437 |       10 | damselem |                  | 127.0.0.1   |                 |       57477 | 2013-09-20 17:52:53.645154+02 |                               | 2013-09-20 17:52:53.652163+02 | 2013-09-20 17:52:53.65551+02  | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61439 |       10 | damselem |                  | 127.0.0.1   |                 |       57479 | 2013-09-20 17:52:53.647286+02 |                               | 2013-09-20 17:52:53.653755+02 | 2013-09-20 17:52:53.656773+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61440 |       10 | damselem |                  | 127.0.0.1   |                 |       57480 | 2013-09-20 17:52:53.64894+02  |                               | 2013-09-20 17:52:53.651756+02 | 2013-09-20 17:52:53.654293+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61442 |       10 | damselem |                  | 127.0.0.1   |                 |       57481 | 2013-09-20 17:52:53.653652+02 |                               | 2013-09-20 17:52:53.656866+02 | 2013-09-20 17:52:53.659138+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61443 |       10 | damselem |                  | 127.0.0.1   |                 |       57482 | 2013-09-20 17:52:53.658798+02 |                               | 2013-09-20 17:52:53.661973+02 | 2013-09-20 17:52:53.664401+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61444 |       10 | damselem |                  | 127.0.0.1   |                 |       57483 | 2013-09-20 17:52:53.660769+02 |                               | 2013-09-20 17:52:53.664362+02 | 2013-09-20 17:52:53.666572+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61447 |       10 | damselem |                  | 127.0.0.1   |                 |       57486 | 2013-09-20 17:52:53.66474+02  |                               | 2013-09-20 17:52:53.66803+02  | 2013-09-20 17:52:53.670324+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61448 |       10 | damselem |                  | 127.0.0.1   |                 |       57488 | 2013-09-20 17:52:53.665334+02 |                               | 2013-09-20 17:52:53.669881+02 | 2013-09-20 17:52:53.672571+02 | f       | idle   | SELECT pid FROM pg_stat_activity

 26112 | backend_development | 61445 |       10 | damselem | psql             | 127.0.0.1   |                 |       57484 | 2013-09-20 17:52:53.663895+02 | 2013-09-20 17:52:53.670771+02 | 2013-09-20 17:52:53.670771+02 | 2013-09-20 17:52:53.670772+02 | f       | active | SELECT * FROM pg_stat_activity

 26112 | backend_development | 61446 |       10 | damselem |                  | 127.0.0.1   |                 |       57485 | 2013-09-20 17:52:53.664702+02 | 2013-09-20 17:52:53.670699+02 | 2013-09-20 17:52:53.670699+02 | 2013-09-20 17:52:53.670701+02 | f       | active | SELECT pid FROM pg_stat_activity

(12 rows)

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

psql: FATAL:  sorry, too many clients already

Sorry for the psql output.

[daniel....@gmail.com]

Oh, I think I now understood what you meant. My maxConnections channel is released after the Query function is executed, but in reality the problem is that the connection is still being used by rows.Next. That means that I should perform client.connectionPool <- 1 instead at the end of the GetIdentifierAndStreet function. I'll check and let you know.

[daniel....@gmail.com]

Works!

[razo...@gmail.com]

I faced with the same problem and I couldn't find a solution yet.

Could you please share an example with  client.connectionPool <- 1 ?