英文:
Postgres pgx driver hangs on commit
问题
我有一个在表中更新记录并使用pgx Postgres驱动程序请求的函数。这个函数在提交时卡住了。有什么想法为什么会发生这种情况吗？为什么在这种情况下我不能使用事务？
当然，由于查询是原子的，我可以删除事务。但仍然不清楚为什么会发生这种情况，如果我需要一个事务，该怎么办。
func (r *Repository) GetUpdatedItems(ctx context.Context, filters []string) ([]Item, error) {
conn, err := r.pool.Acquire(ctx)
// 错误处理
defer conn.Release()
tx, err := conn.Begin(ctx)
// 错误处理
defer func() {
closeCtx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
_ = tx.Rollback(closeCtx)
}()
query := fmt.Sprintf(`UPDATE %s
SET fieldOne = $1, fieldTwo = $2
WHERE otheField = '' OR otherField IS NULL
RETURNING fieldOne, fieldTwo, otheField, someMoreField;`,
r.tableName, sqlArray(aggregatesTypes))
rows, err := conn.Query(ctx, query, filters[0], filters[1])
// 错误处理
defer rows.Close()
var retItems []reaper.Item
for rows.Next() {
var fieldOne string
var fieldTwo string
var otheField string
var someMoreField string
if err := rows.Scan(&id, &fieldOne, &fieldTwo, &otheField, &someMoreField); err != nil {
return nil, fmt.Errorf("failed to scan item: %w", err)
}
item := Item{
FieldOne: fieldOne,
FieldTwo: fieldTwo,
OtheField: otheField,
SomeMoreField: someMoreField
}
retItems = append(retItems, item)
}
if err := tx.Commit(ctx); err != nil {
return nil, fmt.Errorf("failed to commit transaction: %w", err)
}
return retItems, nil
}
英文:
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.
func (r *Repository) GetUpdatedItems(ctx context.Context, filters []string) ([]Item, error) {
conn, err := r.pool.Acquire(ctx)
// error handling
defer conn.Release()
tx, err := conn.Begin(ctx)
// error handling
defer func() {
closeCtx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
_ = tx.Rollback(closeCtx)
}()
query := fmt.Sprintf(`UPDATE %s
SET fieldOne = $1, fieldTwo = $2
WHERE otheField = '' OR otherField IS NULL
RETURNING fieldOne, fieldTwo, otheField, someMoreField;`,
r.tableName, sqlArray(aggregatesTypes))
rows, err := conn.Query(ctx, query, filters[0], filters[1])
// error handling
defer rows.Close()
var retItems []reaper.Item
for rows.Next() {
var fieldOne string
var fieldTwo string
var otheField string
var someMoreField string
if err := rows.Scan(&id, &fieldOne, &fieldTwo, &otheField, &someMoreField); err != nil {
return nil, fmt.Errorf("failed to scan item: %w", err)
}
item := Item{
FieldOne: fieldOne,
FieldTwo: fieldTwo,
OtheField: otheField,
SomeMoreField: someMoreField
}
retItems = append(retItems, item)
}
if err := tx.Commit(ctx); err != nil {
return nil, fmt.Errorf("failed to commit transaction: %w", err)
}
return retItems, nil
}
答案1
得分: 2
tx.Query 必须使用，而不是 conn.Query。
谢谢帮助！
英文:
tx.Query must be used instead of conn.Query.
Thanks for the help!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论