(Postgres & Golang) pq: unexpected parse response ‘c’

Recently, I had a task that involved getting a bunch of info from a database spread across different tables. The main entity assumes it is orders having different statuses and items. Sounds easy so far, right?

But here’s the catch — orders are kind of like set in stone, and we use a builder design pattern to put them together. So, to grab all the orders, their statuses, and items for a specific customer, I set up a plan using a PostgreSQL transaction.

Imagine an order like this:

{
  "order": {
    "orderId": "...",
    "customerName": "...",
    "shippingAddress": {
      "street": "...",
      "city": "...",
      "state": "...",
      "zipCode": "..."
    },
    "orderDate": "...",
    "totalAmount": ...,
    "statuses": [...],
    "items": [...]
  }
}

I started by creating a transaction and selecting all orders and looping over each order row, inside the loop I was sharing the same transaction for getStatus and getItems.

orders := []*domain.Order
rows, err := tx.QueryContext(ctx, SelectOrderCmd, customerId)
if err != nil {
  //log the error
  return nil, err
}
defer rows.Close()
for rows.Next(){
  orderBuilder := domain.OrderBuilder{}
  err =rows.scan(&orderBuilder.Id, .....)
  if err != nil {
    //log the error
    return nil, err
  }

  statusRows, err = tx.QueryContext(ctx, SelectOrderStatusCmd, orderBuilder.Id)
  ....
  ....

  itemsRows, err = tx.QueryContext(ctx, SelectOrderItemsCmd, orderBuilder.Id)
  ....
  ....  
  orders = append(orders, orderBuilder.Build())
}

the real issue appears when testing the function I found this error

pq: unexpected parse response 'c'

after searching for a while I found this fact Because the rows are still in use for SelectOrderCmd, but the next query SelectOrderStatusCmd wants to use the same transaction which causes the error.

the solution is simple just close rows before using the transaction I refactored the code and I got 2 things a working code and a reduction in the cognitive complexity of my function.

func (r *OrderRepository) GetAllOrders(customerID uuid.UUID) ([]*domain.Order, error) {
 var orders []*domain.Order
 //getAllOrderBuilders
 for _, orderBuilder := range orderBuilders {
  //fill other fields
 }

 return orders, nil
}

func (r *OrderRepository) getAllOrderBuilders(connection *pg.DB, customerID uuid.UUID) ([]*domain.OrderBuilder, error) {
 var orderBuilders []*domain.OrderBuilder
  ...
 return orderBuilders, nil
}

func (r *OrderRepository) getAllOrderStatus(connection *pg.DB, orderID uuid.UUID) ([]*domain.Status, error) {
 var statuses []*domain.Status
 ...
 return statuses, nil
}

func (r *OrderRepository) getAllOrderItems(connection *pg.DB, orderID uuid.UUID) ([]*domain.OrderItem, error) {
 var items []*domain.OrderItem
 ...
 return items, nil
}

each function of these functions closes the rows right before return by using

defer rows.Close()

There are several alternative techniques for retrieving such data, including the use of subqueries or joins. However, the code presented adheres to a specific convention, and the article refrains from discussing the comparative merits of one technique over another.

If you found this content valuable, I kindly invite you to follow and applauding the story. The code examples provided above were composed directly on the Medium platform without the aid of dedicated code editors. I appreciate your understanding for any typos that may have occurred.

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us X | **LinkedIn | [YouTube](https://www.youtube.com/c/stackademic) | Discord**
• Visit our other platforms: **In Plain English | CoFeed | Venture**