How to repeat SQL insertion until successful with pg-promise?

Question

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?

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:

   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.

Answer 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.

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.

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.

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

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.

---

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