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 ?

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. — mkopriva, Oct 28 '19 at 20:06
Thanks, not solving the problem but I've updated my functions. — Ado Ren, Oct 28 '19 at 20:17
conn busy indicates that you're missing a Close call somewhere, probably in other parts of the program that share the DB instance. — Peter, Oct 28 '19 at 20:18
ok, nothing to do with simultaneity of the request then ? I'll check again for the close call. — Ado Ren, Oct 28 '19 at 20:20
might have something to do with context also. Still looking. — Ado Ren, Oct 28 '19 at 21:19
I have similar problem and created a ticket https://github.com/jackc/pgx/issues/1226 — Sergey Ponomarev, Jun 22 '22 at 17:26

score 16 · Accepted Answer · answered Oct 28 '19 at 21:28

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.

score 7 · Answer 2 · answered Oct 29 '20 at 22:53

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

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 — Aliy, Mar 29 '21 at 17:26
@Aliy Totally agree. Might be better to just stick to the standard package to avoid future errors like this. — Jessica, Mar 30 '23 at 07:00

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

2 Answers2