1

I use query to Find All DB data, and one of tables need to be array, so i integrated the loop. EveryTime errors is conn is busy. `

for rows.Next() {
        var ord Order
        err = rows.Scan(

and after one more Query() which is uses to find all Item with ord.UID. So the question is, what is wrong in my code? And how to use this. Here is my FindAll Func:

rows, err := r.client.Query(ctx, q)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    orders := make([]Order, 0)
    for rows.Next() {
        var ord Order
        err = rows.Scan(
            &ord.OrderUID,
            &ord.TrackNumber,
            &ord.Entry,
            &ord.Delivery.Name,
            &ord.Delivery.Phone,
            &ord.Delivery.Zip,
            &ord.Delivery.City,
            &ord.Delivery.Address,
            &ord.Delivery.Region,
            &ord.Delivery.Email,
            &ord.Payment.Transaction,
            &ord.Payment.RequestID,
            &ord.Payment.Currency,
            &ord.Payment.Provider,
            &ord.Payment.Amount,
            &ord.Payment.PaymentDT,
            &ord.Payment.Bank,
            &ord.Payment.DeliveryCost,
            &ord.Payment.GoodsTotal,
            &ord.Payment.CustomFee,
            &ord.Locale,
            &ord.InternalSignature,
            &ord.CustomerID,
            &ord.DeliveryService,
            &ord.ShardKey,
            &ord.SmID,
            &ord.DateCreated,
            &ord.OofShard,
        )
        if err != nil {
            return nil, err
        }

        iq := ` ... `

           itemRows, err := r.client.Query(ctx, iq, ord.OrderUID)
        if err != nil {
            return nil, err
        }
        items := make([]item.Item, 0)
        for itemRows.Next() {
            var item item.Item

            err = itemRows.Scan(
                &item.ID,
                &item.ChrtID,
                &item.TrackNumber,
                &item.Price,
                &item.Rid,
                &item.Name,
                &item.Sale,
                &item.Size,
                &item.TotalPrice,
                &item.NmID,
                &item.Brand,
                &item.Status,
            )
            if err != nil {
                return nil, err
            }
            items = append(items, item)
        }
        ord.Items = items

        orders = append(orders, ord)
}

`

I tried to rows.Close, but then i Can't use row anymore.

SloVaC
  • 21
  • 2
  • 1
    `defer`red statements are executed before the return from the function (think of it as if they are executed right before the closing `}`. So basically you do not close the rowset from the 1st execution of the `Query()`. – kostix Nov 11 '22 at 15:06
  • 1
    You're not closing `itemRows` and because of that your pool is running out of available connections. Wrap and execute the nested query and its related loop and scanning in an anonymous func, so that you can use `defer itemRows.Close()` properly, that way the code will at most occupy two connections. E.g. https://go.dev/play/p/kQe8UHDGWLw – mkopriva Nov 11 '22 at 15:11
  • @mkopriva, I'm afraid that in your example, the 1st `defer`red `Close` will prevent successful using of the connection by subsequent queries. Anyway, I'm not really sure about PostgreSQL's peculiarities here, just guessing. – kostix Nov 11 '22 at 16:37
  • 1
    Connection yes, but a pool as well? `*sql.DB` is safe for concurrent use so I'd assume that it shouldn't be a problem. True I do not know the type of `r.client`, but with the stdlib's `*sql.DB` I'd assume should work (each *sql.Rows has its own connection). But if it doesn't it can't be helped. @kostix – mkopriva Nov 11 '22 at 16:53
  • @kostix I'm able to run this on my local just fine: https://go.dev/play/p/txAOXUJyUdw – mkopriva Nov 11 '22 at 17:38

0 Answers0