Как повторить вставку SQL до успешного завершения с pg-обещанием?

Question

В моей программе я вставляю некоторые данные в таблицу и возвращаю их идентификатор, и мне нужно убедиться, что я ввожу этот идентификатор в другую таблицу с уникальной случайно сгенерированной строкой. Но в случае неудачной вставки при попытке вставить уже существующую случайную строку, как я могу повторить вставку, пока она не будет успешной?

я использую 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));

Я не уверен, есть ли какое-нибудь простое решение на основе SQL/JS/pg-обещаний, которое я мог бы использовать.

Answer 1

Я бы посоветовал автору вопроса найти решение своей проблемы на чистом SQL, поскольку с точки зрения производительности это было бы значительно более эффективно, чем что-либо еще.

Но поскольку вопрос был о том, как повторно выполнить запросы с помощью pg-обещания, я приведу пример, в дополнение к уже опубликованному, за исключением того, что без получения и освобождения соединения для каждой попытки, а также с надлежащей целостностью данных.

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;
    });

Поскольку вы пытаетесь повторно запустить зависимый запрос, вы не должны позволять первому запросу оставаться успешным. Если все ваши попытки со вторым запросом не удаются, вы должны откатить все изменения назад, т.е. использовать транзакцию - метод tx, как показано в коде.

Вот почему мы разделили ваши WITH запрос внутри транзакции, чтобы обеспечить такую ​​целостность.

ОБНОВИТЬ

Ниже приведена лучшая версия этого. Поскольку ошибки внутри транзакции должны быть изолированы, чтобы не нарушать стек транзакций, каждая попытка должна быть внутри собственной SAVEPOINT, что означает использование другого уровня транзакции:

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;
    });

Я бы также предложил использовать pg-monitor, чтобы вы могли видеть и понимать, что происходит внизу, и какие запросы фактически выполняются.

---

PS Я автор pg-обещания.

Answer 2

Самый простой способ - поместить его в метод, а затем повторно вызвать его в 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;
        });

}