How to make multiple query in a very short interval / simultaneously

user_8162252 0
Viewed 6258

Hey I'm getting an error message : conn busy from pgx

I don't know how to solve this. Here is my function :

func (r *proverbRepo) SelectPendingProverbs(table string) (proverbs []domain.Proverb, err error) {
    query := fmt.Sprintf("SELECT id, proverb literal FROM %s", table)
    rows, err := r.Db.Query(context.Background(), query)
    defer rows.Close()

    if err != nil {
        return
    }

    for rows.Next() {
        var prov domain.Proverb
        if err = rows.Scan(&prov.ID, &prov.Literal); err != nil {
            return
        }
        proverbs = append(proverbs, prov)
    }
    return
}

r.Db is pgx.Connect(context.Background(), os.Getenv("PSQL_URL"))

I'm fetching two different table in a very short interval from two separate front end requests.

The first request goes through, the other one returns the conn busy error message.

I really don't know what to look for, would somebody help me ?

pgx go psql
2 Answers

pgx.Connect() returns a pgx.Conn which cannot be used concurrently. This is what the godocs of this type state:

Conn is a PostgreSQL connection handle. It is not safe for concurrent usage. Use a connection pool to manage access to multiple database connections from multiple goroutines.

So if you replace pgx.Connect() with pgxpool.Connect() from github.com/jackc/pgx/pgxpool you should be fine.

user_105565250

The r.Db returned by pgx.Connect(context.Background(), os.Getenv("PSQL_URL")) if of type *pgx.Conn and represents a single connection which is not concurrency safe. Usually, you would like to use a connection pool to handle the concurrency for you and allows reusing open connection. To use a connection pool replace the import github.com/jackc/pgx/v4 with github.com/jackc/pgx/v4/pgxpool and connect with pgxpool.Connect() instead of pgx.Connect() and the api will be the same:

r.Pool := pgxpool.Connect(context.Background(), os.Getenv("PSQL_URL"))
r.Pool.Query(context.Background(), query)
...

if at any given point you need to use a single connection to access some lower-level feature you can safely acquire a concurrent safe connection as follow:

conn, err := r.Pool.Acquire(context.Background())
if err != nil {
    fmt.Fprintln(os.Stderr, "Error acquiring connection:", err)
    os.Exit(1)
}
defer conn.Release()
...
user_56583500
Related