Transaction in Golang with PGX

Question

I am currently in the process of creating a little Go App. Right now I am working on the DB part. The Library I use is this one: https://github.com/jackc/pgx
The problem I have is that every time I try to execute the database read, it tells me that my 'conn is busy'. I read about using a pgxpool instead of a single connection, but it still does not work. What am I doing wrong?

func (postgre *PostgreClient) read(query string) (pgx.Row, error) {
    client, err := postgre.client.Acquire(context.TODO())
    transaction, err := client.BeginTx(context.TODO(), pgx.TxOptions{})
    if err != nil {
        return nil, err
    }
    defer transaction.Rollback(context.TODO())

    rows := transaction.QueryRow(context.TODO(), query)
    if err != nil {
        return nil, err
    }
    err = transaction.Commit(context.TODO())
    return rows, err
}

Thanks in advance.

Answer 1

You have to scan the row before you commit the transaction.

If you want the handling of the transaction to remain within the function you can pass an interface that does the scanning also inside the function.

For example:

// implemented by *sql.Row & *sql.Rows
type Row interface {
    Scan(dst ...interface{}) error
}

// implemented by your "models"
type RowScanner interface {
    ScanRow(r Row) error
}

type User struct {
    Id    int
    Email string
}

func (u *User) ScanRow(r Row) error {
    return r.Scan(
        &u.Id,
        &u.Email,
    )
}

func (postgre *PostgreClient) read(query string, rs RowScanner) (err error) {
    conn, err := postgre.client.Acquire(context.TODO())
    if err != nil {
        return err
    }
    defer conn.Release()
    
    tx, err := conn.BeginTx(context.TODO(), pgx.TxOptions{})
    if err != nil {
        return err
    }
    defer func() {
        if err != nil {
            tx.Rollback(context.TODO())
        } else {
            tx.Commit(context.TODO())
        }
    }()

    row := tx.QueryRow(context.TODO(), query)
    if err != nil {
        return nil, err
    }
    return rs.ScanRow(row) 
}

u := new(User)
if err := pg.read("select id, email from users limit 1", u); err != nil {
    panic(err)
}

For scanning a list of models:

type UserList []*User

func (ul *UserList) ScanRow(r Row) error {
    u := new(User)
    if err := u.ScanRow(r); err != nil {
        return err
    }

    *ul = append(*ul, u)
    return nil
}

func (postgre *PostgreClient) list(query string, rs RowScanner) (err error) {
    conn, err := postgre.client.Acquire(context.TODO())
    if err != nil {
        return err
    }
    defer conn.Release()
    
    tx, err := conn.BeginTx(context.TODO(), pgx.TxOptions{})
    if err != nil {
        return err
    }
    defer func() {
        if err != nil {
            tx.Rollback(context.TODO())
        } else {
            tx.Commit(context.TODO())
        }
    }()

    rows, err := tx.Query(context.TODO(), query)
    if err != nil {
        return err
    }
    defer rows.Close()
    
    for rows.Next() {
        if err := rs.ScanRow(rows); err != nil {
            return err
        }
    }
    return rows.Err()
}

ul := new(UserList)
if err := pg.list("select id, email from users", ul); err != nil {
    panic(err)
}