Postgres pgx驱动在提交时出现卡顿。

huangapple
go评论120阅读模式
英文:

Postgres pgx driver hangs on commit

问题

我有一个在表中更新记录并使用pgx Postgres驱动程序请求的函数。这个函数在提交时卡住了。有什么想法为什么会发生这种情况吗？为什么在这种情况下我不能使用事务？

当然，由于查询是原子的，我可以删除事务。但仍然不清楚为什么会发生这种情况，如果我需要一个事务，该怎么办。

  1. func (r *Repository) GetUpdatedItems(ctx context.Context, filters []string) ([]Item, error) {
  2.     conn, err := r.pool.Acquire(ctx)
  3.     // 错误处理
  4.     defer conn.Release()
  5.     
  6.     tx, err := conn.Begin(ctx)
  7.     // 错误处理
  8.     defer func() {
  9.         closeCtx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
  10.         defer cancel()
  11.         _ = tx.Rollback(closeCtx) 
  12.     }()
  14.     query := fmt.Sprintf(`UPDATE %s
  15.     SET fieldOne = $1, fieldTwo = $2
  16.     WHERE otheField = '' OR otherField IS NULL
  17.     RETURNING fieldOne, fieldTwo, otheField, someMoreField;`,
  18.         r.tableName, sqlArray(aggregatesTypes))
  20.     rows, err := conn.Query(ctx, query, filters[0], filters[1])
  21.     // 错误处理
  22.     defer rows.Close()
  24.     var retItems []reaper.Item
  26.     for rows.Next() {
  28.         var fieldOne string
  29.         var fieldTwo string
  30.         var otheField string
  31.         var someMoreField string
  32.         
  34.         if err := rows.Scan(&id, &fieldOne, &fieldTwo, &otheField, &someMoreField); err != nil {
  35.             return nil, fmt.Errorf("failed to scan item: %w", err)
  36.         }
  38.         item := Item{
  39.             FieldOne:       fieldOne,
  40.             FieldTwo:       fieldTwo,
  41.             OtheField:      otheField,
  42.             SomeMoreField:  someMoreField
  43.         }
  45.         retItems = append(retItems, item)
  46.     }
  48.     if err := tx.Commit(ctx); err != nil {
  49.         return nil, fmt.Errorf("failed to commit transaction: %w", err)
  50.     }
  51.     
  52.     return retItems, nil
  53. }
英文:

I have a function that updates records in table and requests it using pgx Postgres driver. This function hangs on commit. Is there any ideas why does it happen? Why I can't use transactions in this case?

Of course, as the query is atomic I can remove transactions. But it's still unclear—why it happens and what to do if I need a transaction.

  1. func (r *Repository) GetUpdatedItems(ctx context.Context, filters []string) ([]Item, error) {
  2. conn, err := r.pool.Acquire(ctx)
  3. // error handling
  4. defer conn.Release()
  5. tx, err := conn.Begin(ctx)
  6. // error handling
  7. defer func() {
  8. closeCtx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
  9. defer cancel()
  10. _ = tx.Rollback(closeCtx) 
  11. }()
  12. query := fmt.Sprintf(`UPDATE %s
  13. SET fieldOne = $1, fieldTwo = $2
  14. WHERE otheField = '' OR otherField IS NULL
  15. RETURNING fieldOne, fieldTwo, otheField, someMoreField;`,
  16. r.tableName, sqlArray(aggregatesTypes))
  17. rows, err := conn.Query(ctx, query, filters[0], filters[1])
  18. // error handling
  19. defer rows.Close()
  20. var retItems []reaper.Item
  21. for rows.Next() {
  22. var fieldOne string
  23. var fieldTwo string
  24. var otheField string
  25. var someMoreField string
  26. if err := rows.Scan(&id, &fieldOne, &fieldTwo, &otheField, &someMoreField); err != nil {
  27. return nil, fmt.Errorf("failed to scan item: %w", err)
  28. }
  29. item := Item{
  30. FieldOne: 		fieldOne,
  31. FieldTwo: 		fieldTwo,
  32. OtheField: 		otheField,
  33. SomeMoreField: 	someMoreField
  34. }
  35. retItems = append(retItems, item)
  36. }
  37. if err := tx.Commit(ctx); err != nil {
  38. return nil, fmt.Errorf("failed to commit transaction: %w", err)
  39. }
  40. return retItems, nil
  41. }

答案1

得分: 2

tx.Query 必须使用，而不是 conn.Query。

谢谢帮助！

英文:

tx.Query must be used instead of conn.Query.

Thanks for the help!

huangapple
  • 本文由 发表于 2021年8月11日 00:07:53
  • 转载请务必保留本文链接：https://go.coder-hub.com/68730148.html
  • go
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定