Correct way to use sql connection with http and pq

[Chris Baynes]

The documentation states that the sql.DB returned from Open is "safe for concurrent use by multiple goroutines". In practise we are getting the error "pq: unexpected describe rows response: '\x01'" when running a http server with a global db connection, and testing with concurrent requests.

We're not sure whether this is a misunderstanding of how we should be using the pooled db connection within a http request, or a bug in the pq or database/sql package.

Here's a sample server which fails under those circumstances:

package main

import (

"database/sql"

"fmt"

_ "github.com/lib/pq"

"net/http"

)

var dbConn *sql.DB

func dbConnect() *sql.DB {

db, err := sql.Open("postgres", "user=dev dbname=my_db sslmode=disable port=5432 host=localhost password=")

if err != nil {

panic(err)

}

return db

}

func main() {

dbConn = dbConnect()

http.HandleFunc("/", run)

http.ListenAndServe(":8080", nil)

}

func run(http.ResponseWriter, *http.Request) {

var pid int

res := dbConn.QueryRow(`SELECT id FROM users WHERE id=$1`, 1365)

err := res.Scan(&pid)

if err != nil {

fmt.Println(err)

return

}

}

[Kamil Kisiel]

What version of Go are you using? If 1.0.3, do you get the same error with tip?

[Chris Baynes]

I'm using go tip (the error is reproduced on versions: +d58997478ec6, +9bb42a7021c9).

[Mike Hughes]

One quick thing before I go to sleep :-)

I noticed you aren't closing the db connection. You may be hitting a limit on the pgsql side.

try adding

defer db.Close()

after

dbConn = dbConnect()

in main.

but, as I said, i'm tired and that may have nothing to do with it :-)

[Chris Baynes]

I missed the defer close in the code above that's true. But unfortunately that's not the problem, I can reproduce the error with only 5 requests in parallel (my db max connections is at 100).

Another interesting thing is that I cannot reproduce this without the http server - using a loop of goroutines within main to call QueryRow works perfectly without errors. My understanding is that net/http just starts a new goroutine for each new request. Is there some other magic there which could be breaking things?

[Kamil Kisiel]

Are you able to reproduce it in the goroutine case with GOMAXPROCS > 1 ? Perhaps the way the goroutines are being scheduled is different in your contrived case versus when net/http is used.

[Chris Baynes]

I've tried it with GOMAXPROCS=4 and the following code, but there is no error produced:

for j := 0; j < 50; j++ {

go func() {

var uid int

res := dbConn.QueryRow(`SELECT id FROM users WHERE id=$1`, 1365)

err := res.Scan(&uid)

if err != nil {

fmt.Println(err)

return

}

}()

}

[Alexey Borzenkov]

Are you sure you're using latest tip? A bug that would cause your exact behavior was fixed very recently:

https://code.google.com/p/go/source/detail?r=b141b837936770eefa49ba2975e6594356c58dbc

Best regards,

Alexey.

--
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.
 
 

[Chris Baynes]

Alexey, you were right, this is now working perfectly in the latest go tip! My tip version was a couple of days before the fix. 

Thanks for the help.

Chris