在 forEach 循环中带有相关查询的 pg-promise 事务发出警告错误:查询已释放或丢失的连接 [英] pg-promise transaction with dependent queries in forEach loop gives warning Error: Querying against a released or lost connection

问题描述

我正在尝试在 pg-promise 事务中插入链接数据.事务成功正确插入所有数据，但给出警告UnhandledPromiseRejectionWarning:错误:查询已释放或丢失的连接..

I am trying to insert linked data in a pg-promise transaction. The transaction successfully inserts all the data correctly, but gives a warning UnhandledPromiseRejectionWarning: Error: Querying against a released or lost connection..

导致这种情况的代码最初是:

The code that causes this was originally:

const item = {
  batch: { batch_number: 1 },
  ingredients: [
    { amount: '12', unit: 'kg' },
    { amount: '4', unit: 'L' }
  ],
}


return await db.tx(async t => {
  const batchQueryString = pgp.helpers.insert(item.batch, null, 'ionomer_batch')
  const batchQuery = await t.one(batchQueryString + ' RETURNING ionomer_batch_id')

  item.ingredients.forEach(async ingredient => {
    const ingredientQueryString = pgp.helpers.insert(ingredient, null, 'ingredient')
    const ingredientQuery = await t.one(ingredientQueryString + ' RETURNING ingredient_id')
    await t.none(
            `INSERT INTO ionomer_batch_step(ionomer_batch_id, ingredient_id) 
            VALUES(${batchQuery.ionomer_batch_id}, ${ingredientQuery.ingredient_id})`
            )
   })

   return batchQuery
  }).then(data => {
    return {success: true, response: data}
  }).catch(error => {
    return {success: false, response: error}
})

我在不发出警告的情况下运行了

I got it to work without producing a warning by doing

return await db.tx(async t => {
  const batchQueryString = pgp.helpers.insert(item.batch, null, 'ionomer_batch')
  const batchQuery = await t.one(batchQueryString + ' RETURNING ionomer_batch_id')

  const ingredientQueries = []
  // this can't be async
  item.ingredients.forEach(ingredient => {
    const ingredientQueryString = pgp.helpers.insert(ingredient, null, 'ingredient')
    const ingredientQuery = t.one(ingredientQueryString + ' RETURNING ingredient_id')
    ingredientQueries.push(ingredientQuery)
   })
   const resolvedIngredientQueries = await t.batch(ingredientQueries)
   resolvedIngredientQueries.forEach(async ingredientQuery => {
     await t.none(
       `INSERT INTO ionomer_batch_step(ionomer_batch_id, ingredient_id) 
       VALUES(${batchQuery.ionomer_batch_id}, ${ingredientQuery.ingredient_id})`
      )
    })

   return batchQuery
  }).then(data => {
    return {success: true, response: data}
  }).catch(error => {
    return {success: false, response: error}
})

但我现在必须循环两次而不是一次，而且我在第一个循环中丢失了异步.似乎应该有一种方法可以在接近第一次尝试时做一些事情，而不会发出有关已释放或丢失连接的警告.我尝试链接查询，但无法使其正常工作.

but I now have to loop through twice instead of once, and I lose the async in the first loop. It seems like there should be a way to do something closer to the first attempt without hitting a warning about released or lost connections. I played around with chaining the queries, but couldn't get that to work.

推荐答案

作为@deanna 自己答案的补充...

As an addition to @deanna own answer...

您实际上并不需要实现循环.您可以将请求重新映射到承诺数组中，然后解决它:

You do not really need to implement a loop. You can just re-map requests into array of promises, and then resolve it:

await db.tx(async t => {
    const batchQueryString = pgp.helpers.insert(item.batch, null, 'ionomer_batch');
    const batchQuery = await t.one(`${batchQueryString} RETURNING ionomer_batch_id`);

    const inserts = item.ingredients.map(async i => {
        const query = pgp.helpers.insert(i, null, 'ingredient');
        const ingredientQuery = await t.one(`${query} RETURNING ingredient_id`);
        return t.none(
            `INSERT INTO ionomer_batch_step(ionomer_batch_id, ingredient_id)
            VALUES($/batchQuery.ionomer_batch_id/, $/ingredientQuery.ingredient_id/)`,
            {batchQuery, ingredientQuery});
   });

    await t.batch(inserts); // settle all generated promises

    return batchQuery;
});

另外，你可以从变化中看到，你不应该像那样使用 ES6 值注入.见这里:

Also, you can see from the change, you should never use ES6 value injection like that. See here:

重要提示:切勿在 ES6 模板字符串中使用保留的 ${} 语法，因为那些不知道如何格式化 PostgreSQL 的值.在 ES6 模板字符串中，您应该只使用 4 种替代方法之一 - $()、$<>、$[] 或 <代码>$//.

IMPORTANT: Never use the reserved ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL. Inside ES6 template strings you should only use one of the 4 alternatives - $(), $<>, $[] or $//.

这篇关于在 forEach 循环中带有相关查询的 pg-promise 事务发出警告错误:查询已释放或丢失的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！