如何重复SQL插入直到成功使用pg-promise?

In my program I insert some data into a table and get back it's id and I need to ensure I enter that id into another table with a unique randomly generated string. But, in case the insertion fails for attempting to insert an already-existing random string, how could I repeat the insertion until it is successful?

在我的程序中，我将一些数据插入到一个表中，然后取回它的id，我需要确保我使用唯一的随机生成的字符串将这个id输入到另一个表中。但是，如果插入失败，无法插入已经存在的随机字符串，如何重复插入，直到插入成功?

I'm using pg-promise to talk to postgreSQL. I can run program like this that inserts the data into both tables given the random string doesn't already exists:

我正在使用pg-promise与postgreSQL对话。我可以运行这样的程序，在随机字符串还不存在的情况下，将数据插入到两个表中:

   db.none(
            `
            WITH insert_post AS
            (
                INSERT INTO table_one(text) VALUES('abcd123')
                RETURNING id
            )
            INSERT INTO table_two(id, randstr)
                    VALUES((SELECT id FROM insert_post), '${randStrFn()}')
            `
        )
    .then(() => console.log("Success"))
    .catch(err => console.log(err));

I'm unsure if there is any easy SQL/JS/pg-promise based solution that I could make use of.

我不确定是否有任何简单的基于SQL/JS/pg-promise的解决方案可以使用。

2 个解决方案

#1

I would encourage the author of the question to seek a pure-SQL solution to his problem, as in terms of performance it would be significantly more efficient than anything else.

我鼓励问题的作者为他的问题寻找一个纯粹的sql解决方案，因为就性能而言，它比其他任何东西都要有效得多。

But since the question was about how to re-run queries with pg-promise, I will provide an example, in addition to one already published, except without acquiring and releasing the connection for every attempt, plus proper data integrity.

但是，由于问题是关于如何使用pg-promise重新运行查询，除了已经发布的查询之外，我还将提供一个示例，除非每次尝试都没有获取和释放连接，以及适当的数据完整性。

db.tx(t => {
    // BEGIN;
    return t.one('INSERT INTO table_one(text) VALUES($1) RETURNING id', 'abcd123', a => +a.id)
        .then(id => {
            var f = attempts => t.none('INSERT INTO table_two(id, randstr) VALUES($1, randStrFn())', id)
                .catch(error => {
                    if (--attempts) {
                        return f(attempts); // try again
                    }
                    throw error; // give up
                });
            return f(3); // try up to 3 times
        });
})
    .then(data => {
        // COMMIT;
        // success, data = null
    })
    .catch(error => {
        // ROLLBACK;
    });

Since you are trying to re-run a dependent query, you should not let the first query remain successful, if all your attempts with the second query fail, you should roll all the changes back, i.e. use a transaction - method tx, as shown in the code.

由于您正在尝试重新运行一个依赖查询，所以不应该让第一个查询保持成功，如果您对第二个查询的所有尝试都失败，那么您应该将所有更改回滚，例如使用事务-方法tx，如代码所示。

This is why we split your WITH query inside the transaction, to ensure such an integrity.

这就是为什么我们在事务中分割您的WITH查询，以确保这样的完整性。

UPDATE

更新

Below is a better version of it though. Because errors inside the transaction need to be isolated, in order to avoid breaking the transaction stack, each attempt should be inside its own SAVEPOINT, which means using another transaction level:

下面是一个更好的版本。由于事务内部的错误需要隔离，为了避免破坏事务堆栈，每个尝试都应该位于自己的保存点内，这意味着使用另一个事务级别:

db.tx(t => {
    // BEGIN;
    return t.one('INSERT INTO table_one(name) VALUES($1) RETURNING id', 'abcd123', a => +a.id)
        .then(id => {
            var f = attempts => t.tx(sp => {
                // SAVEPOINT level_1;
                return sp.none('INSERT INTO table_two(id, randstr) VALUES($1, randStrFn())', id);
            })
                .catch(error => {
                    // ROLLBACK TO SAVEPOINT level_1;
                    if (--attempts) {
                        return f(attempts); // try again
                    }
                    throw error; // give up
                });
            return f(3); // try up to 3 times
        });
})
    .then(data => {
        // 1) RELEASE SAVEPOINT level_1;
        // 2) COMMIT;
    })
    .catch(error => {
        // ROLLBACK;
    });

I would also suggest using pg-monitor, so you can see and understand what is happening underneath, and what queries are being in fact executed.

我还建议使用pg-monitor，这样您就可以看到和理解下面正在发生的事情，以及实际上正在执行的查询。

P.S. I'm the author of pg-promise.

附注:我是pg-promise的作者。

#2

The easiest way is to put it into a method then re-call that in the catch:

最简单的方法是将它放入一个方法中，然后在catch中重新调用:

const insertPost = (post, numRetries) => {
    return
       db.none(
                `
                WITH insert_post AS
                (
                    INSERT INTO table_one(text) VALUES('abcd123')
                    RETURNING id
                )
                INSERT INTO table_two(id, randstr)
                        VALUES((SELECT id FROM insert_post), '${randStrFn()}')
                `
            )
        .then(() => console.log("Success"))
        .catch(err => {
            console.log(err)
            if (numRetries < 3) {
              return self.insertPost(post, numRetries + 1);
            }
            throw err;
        });

}

#1