Is pgx.pool.Acquire() multithread-ready?

[nuliknol]

During debugging I have found that one of my go-routines got sutck on pgx.pool.Acquire() function. The thing is, I have multiple go-routines using single pool. So, the question is, is pgx.pool safe against deadlocks ???

I am using the pool with CopyFrom and Exec, so, Acquire should release any resouces if those were allocated and free the connection. But I am seening that on request number 26 I am getting blocked inside the pool.Acquire() function

[nuliknol]

here is my config to connect, just in case:

func DbsConnectPool() (*pgxpool.Pool,error) {

    host,port,err:=net.SplitHostPort(os.Getenv("DB_HOST"))
    if (err!=nil) {
        host = "localhost"
        port="5432"
    }   
    conn_str := "user='"+ os.Getenv("USERNAME") +
        "' dbname='" + os.Getenv("DATABASE") +
        "' password='" + os.Getenv("PASSWORD") +
        "' host='" + host +
        "' port='" + port + "'";

    pgx.Connect(context.Background(),conn_str)
    pool_cfg,err := pgxpool.ParseConfig(conn_str)
    if err != nil {
        panic(fmt.Sprintf("Err parsing config: %v\n",err))
    }   
    pool_cfg.MaxConns = MAX_CONN
    pool_cfg.MinConns = MIN_CONN
    pool_cfg.AfterConnect = after_connect_func
    pool_cfg.LazyConnect = false
    pool,err := pgxpool.ConnectConfig(context.Background(),pool_cfg)
    return pool,err
}

[nuliknol]

Replaced pgx.pool with individual connections (not because of this bug, but because I saw there is some logic in pool management that I don't need, so I decided to go for individual connection per business-logic function to shave on clock cycles) , and dead locks disappear. So it is very probable that there is some race condition in pgx.pool that can lock it.

[echlebek]

https://github.com/jackc/puddle/issues/8 describes a deadlock that is possible in puddle, which I gather is used for pooling in pgx. This has been released and incorporated into pgx, but pgx has not been released yet.

@jackc could you perhaps cut a release so that this issue can be re-tested? Perhaps this deadlock is the one the issue submitter experienced. We have run into a similar issue and are keen to see if this resolves is. Many thanks!

[bfontaine]

One year later, is this issue still relevant?

[alexelisenko]

Was https://github.com/jackc/puddle/issues/8 fix merged into pgxpool? I am also experiencing an occasional deadlock, using only the Query and Exec methods.

[jackc]

Yes, there have been several fixes since then. In particular v5.1.0 and higher have a significantly improved pool implementation.

[alexelisenko]

@jackc great, I was using v5.0.4, will try v5.1.1 now, thanks!

[danludwig]

I thought I ran into something like this, but it was deadlocking because I had no defer poolconn.Release() after poolconn.Acquire(c)

[mspi92]

I had a deadlock issue but i think figured it out. Some hints: Problem a)

row := pool.QueryRow(context.Background(), "...")
if conditional {
    // pool connection is first released after row.Scan is executed --> leak
    return 
}
row.Scan(...)

Problem b)

// pool with max_conn == 1
rows, err := pool.Query(context.Background(), "...")
...
defer rows.Close()
for rows.Next() {
    ...
    // connections are exceeded now --> deadlock bcs rows.Close() will never be called
    rows2, err := pool.Query(context.Background(), "...")
    ...
}