Bad connection error after commit fails due to statement_timeout
Author: horghCreated Dec 6, 2019Updated Jan 25, 2026
Labelsbug
Hello!
I have been periodically seeing these errors in my application:
driver: bad connectionI investigated today and I found strange behaviour in this situation:
- I am in a transaction
- I have statement_timeout set to something non-zero
- The commit or rollback call hits the statement_timeout condition
- Afterwards, pinging (or maybe using the connection at all?) raises a bad connection error
I'm wondering if this is what should be happening given there is an error message available showing that the transaction failed.
Here is a program that demonstrates this:
package main
import (
"context"
"database/sql"
"log"
_ "github.com/lib/pq"
)
func main() {
log.SetFlags(0)
uri := "postgres://snip:[email protected]:2345/snip?sslmode=require&timezone=UTC"
db, err := sql.Open("postgres", uri)
if err != nil {
log.Fatal(err)
}
db.SetMaxOpenConns(1)
txOptions := &sql.TxOptions{ReadOnly: true}
tx, err := db.BeginTx(context.Background(), txOptions)
if err != nil {
log.Fatal(err)
}
if _, err := tx.Exec(`SET LOCAL statement_timeout TO 1`); err != nil {
log.Fatal(err)
}
if _, err = tx.Exec(`SELECT 1`); err != nil {
_ = tx.Rollback()
log.Printf("error selecting")
if err := db.Ping(); err != nil {
log.Printf("error pinging after select failure: %s", err)
}
return
}
if err := tx.Commit(); err != nil {
log.Printf("error committing: %s", err)
if err := db.Ping(); err != nil {
log.Printf("error pinging after commit failure: %s", err)
} else {
log.Printf("ping succeeded after commit failure")
}
return
}
}If you run this for a while (e.g. while [ true ]; do ./foo ; done), you will periodically see messages like this:
error committing: pq: canceling statement due to statement timeout
error pinging after commit failure: driver: bad connectionI am using lib/pq v1.2.0, Postgres 9.6.15, and Go 1.13.4.
What do you think?
Thank you!
Source: lib/pq