Pgx: stdlib.OpenDBFromPool() does not work with pgxpool ?...

[howesteve]

Hello,

I recently started adding a library (supergraph) support to my project, but it expects a sql.DB handler. Following the pgx docs, I'm using use pgxpool as a pool manager and didn't had to use stdlib's compatibility layer until now.
So I thought of using stdlib.OpenDBFromPool() to create a sql.DB from the pool, but it does not accept pgxpool.Pool, but instead pgx.ConnPool. Why is there two pools, backwards compatibility? And how can I overcome this? (I just want a sql.DB from a pgxpool.Pool).

I'm using:
github.com/jackc/pgx/v4 v4.6.0

Thanks,
Howe

[jackc]

database/sql has its own conn pool. In v3 of pgx OpenDBFromPool was used to allow the database/sql and pgx pools to co-exist. However, the two pools didn't work together very well.

In v4, OpenDBFromPool was removed. Instead use database/sql as the connection pool and use db.Conn().Raw() or stdlib.AcquireConn() to get a *pgx.Conn when needed.

[Teddy-Schmitz]

Hi @jackc

Ideally I want to combine pgx-pool with sqlx as I need the functionality of BeforeAcquire and AfterRelease that you provide with the struct scanning semantics of sqlx. So being able to use pgxpool OpenDBFromPool would be ideal. Do you have any thoughts on how to do this pattern in v4?

[howesteve]

Since my whole project already uses pgxpool, and the library above tries to use the stdlib api, (it expects a *sql.DB) I was hoping not to have to use two pools. It's not a major problem if they do both no not pre-allocate connections.
Does anyone know of they both pre-allocate?

[jackc]

@Teddy-Schmitz Your desired use case is actually a perfect example of how using both pools at the same time can result in unexpected behavior. Even if OpenDBFromPool() still existed what you want wouldn't work. BeforeAcquire and AfterRelease only apply when the connections are checked out and released from pgxpool. But the way the database/sql pool works it would acquire a connection from pgxpool but never release it until it closed the connection.

[Teddy-Schmitz]

@jackc then that is a great case for taking out that functionality. Thanks for the explanation

[jackc]

@howesteve database/sql does not eagerly allocate connections by default. pgxpool eagerly connects a single connection to validate the pool (and this can be disabled).

[Shannor]

Hey @jackc based on the conversation here does that mean sqlx is not recommended to use with pgx going forward? And should just base sql package be used or pgx?

I'm probably misunderstanding but just wanted to know. I started a project using pgx with sqlx and it seems like this may cause problems in the future. So I would rather switch completely over to pgx completely sooner than later if needed.

[jackc]

I don't really have a recommendation for or against sqlx. I've never used it.

But it's definitely possible to use native pgx features and the database/sql interface (which supports sqlx) at the same time with DB.Conn() and Conn.Raw().

    // Given db is a *sql.DB
    conn, err := db.Conn(context.Background())
    if err != nil {
        // handle error from acquiring connection from DB pool
    }

    err = conn.Raw(func(driverConn interface{}) error {
        conn := driverConn.(*stdlib.Conn).Conn() // conn is a *pgx.Conn
        // Do pgx specific stuff with conn
        conn.CopyFrom(...)
        return nil
    })
    if err != nil {
        // handle error that occurred while using *pgx.Conn
    }

Or you can use database/sql and pgxpool at the same time. It's just they are entirely separate pools and that complicates managing min and max connections.

[Shannor]

Ok cool! Also I tried the snippet you linked but for some reason it errors here
conn := driverConn.(*stdlib.Conn).Conn()
It says that it Unresolved Reference on Conn(). I ended up using the older way with AcquireConn/ReleaseConn.
I'm on version v4 but I'm not sure why it's doing that. I'll check around for answers.

Thanks again!

[jackc]

Ah. I forgot. That isn't in a tagged release yet. Only on master.

[Shannor]

Ah I see. Should I switch over to the master branch or just wait until it's added into v4?

[jackc]

Depends on how quick you need it. I expect to release v4.7.0 in the next week or two.

[Shannor]

That's fine with me! It's no rush on my end. Thanks again!