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

Question

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 ?

Xanthate

Advice: do not defer before the error check, if err is not nil rows will be nil and since you're defering before the error check the defer will be triggered upon return from the error check and Close will be called on a nil rows instance. Not good practice. – Jegar

Thanks, not solving the problem but I've updated my functions. – Xanthate

conn busy indicates that you're missing a Close call somewhere, probably in other parts of the program that share the DB instance. – Eyeopening

ok, nothing to do with simultaneity of the request then ? I'll check again for the close call. – Xanthate

might have something to do with context also. Still looking. – Xanthate

I have similar problem and created a ticket github.com/jackc/pgx/issues/1226 – Herodotus

Answer 1

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.

Scupper

Answer 2

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

Shellieshellproof

thanks for the complete answer. just one question thou. why they did not mention it in their documentation and dont we always connect to db simultaneously? why is it designed as a single connection api? . @DhiaTN – Overwinter

@Overwinter Totally agree. Might be better to just stick to the standard package to avoid future errors like this. – Telegonus