I am getting error: Querying against a released or lost connection in else statement

Question

Here is the code, I am trying to check the result of insert statement. If it comes null(if tag title already exists), it has to execute else statement. But it is failing in else statement throwing lost connection error.

 return this.store.tx('create-tags', async (transaction: any) => {
    tagDetails.forEach((tag: any) => {
     transaction
      .oneOrNone(
      `INSERT INTO tag(title)
         VALUES ($1)
         ON CONFLICT DO NOTHING
         RETURNING tag_id, title`,
        [tag.title],
      )
        .then((result: any) => {
            console.log('the tag details are', result);
           if (result !== null) {
              this.createTags(collectionId, tag.item_id, result.tag_id);
            } else {
           transaction.oneOrNone(
           `
           SELECT tag_id
           FROM tag
           WHERE title = $1
           `,
           [tag.title],
              ).then((tagId: string) => {
               console.log('the tagid in else statement is', tagId);
              if (tagId) {
                this.createTags(collectionId, tag.item_id, tagId);
               }
             })
             .catch(err => {
               console.log('the error in else statement is', err);
             });
       }
      });
  });

instead of doing transaction.oneOrNone in else statement, doing `this.store.one(` SELECT tag_id FROM tag WHERE title = $1 `, [tag.title], )` works as it's not updating or inserting anything. just selecting columns from table. — Prashanti.D, Oct 01 '19 at 16:42
Possible duplicate of [Using async/await with a forEach loop](https://stackoverflow.com/questions/37576685/using-async-await-with-a-foreach-loop) — Bergi, Oct 01 '19 at 17:11
You need to `return` the promise from that `else` clause so that the `then` chain can wait for it (or just use async/await already!), and then you need to fix that `forEach` loop so that the `tx` callback returns a promise that waits for all the queries. — Bergi, Oct 01 '19 at 17:12
As @Bergi already pointed out. Your code shows a number of problems. The reason you are getting that error is because you fail to chain the result of queries to the transaction, which results in a lot of loose queries, trying to execute outside of the transaction context. Also, mixing old `.then` and new `async` syntax doesn't make sense here. And errors should be handled outside of the transaction. — vitaly-t, Oct 02 '19 at 02:20

vitaly-t · Answer 1 · 2019-10-02T04:04:59.263

Your code shows a number of problems.

The main one, and the reason why you are getting the error, is because you fail to chain result of queries into the transaction result. You end up with multiple queries trying to execute outside of the transaction context, hence the error. Here's a modified version that takes care of that:

return this.store.tx('create-tags', t => {
    const res = tagDetails.map((tag: any) => {
        return t.oneOrNone(`INSERT INTO tag(title) VALUES ($1) ON CONFLICT DO NOTHING RETURNING tag_id, title`,
            [tag.title],
        )
            .then((result: any) => {
                console.log('the tag details are', result);
                if (result !== null) {
                    return this.createTags(collectionId, tag.item_id, result.tag_id);
                } else {
                    return t.oneOrNone(`SELECT tag_id FROM tag WHERE title = $1`, [tag.title])
                        .then((tagId: string) => {
                            console.log('the tagid in else statement is', tagId);
                            if (tagId) {
                                return this.createTags(collectionId, tag.item_id, tagId);
                            }
                        })
                        .catch(err => {
                            console.log('the error in else statement is', err);
                        });
                }
            });
    });
    return t.batch(res);
});

However, this isn't particularly great, for the following reasons:

You should rewrite it using fully async code
You should take error handling outside of the transaction
Your whole code looks like it can be replaced with just a single query.
We do not know what createTags does, so cannot comment on that

I modified the code as above but I am getting this error now ERROR: current transaction is aborted, commands ignored until end of transaction block — Prashanti.D, Oct 02 '19 at 14:30
@Prashanti.D That's because you are spawning all queries before they run. Using `async` code style would remove this issue. But it is just implementation detail, which can be ignored, as long as the code works as expected. — vitaly-t, Oct 02 '19 at 14:32

I am getting error: Querying against a released or lost connection in else statement

1 Answers1