Interrupt a long transaction with pg-promise

Question

I need a way to reject the pg-promise in this code:

db.tx(async t => {
        var file = await t.one(`insert into ui.user_datasets_files (user_dataset_id,filename) values (${itemId},'${fileName}') RETURNING id`);
        var data = rows.map(row => {
            return {
                user_dataset_id: itemId,
                file_id: file.id,
                json_data: JSON.stringify(row)
            };
        });
        const insert = pgPromise.helpers.insert(data, dataset_data_columns);
        return t.none(insert);

}).then(() => callback()).catch(err => callback(err));

this line takes long time and user can end the connection:

return t.none(insert);

so, i want a way to end the execution and make rollback inside this event:

 req.on('close', function () {
    promise.reject('Connection Closed');
});

Answer 1

Paginate/throttle through the inserts, and between each insert check if the transaction needs to be interrupted, and if so - throw an error, and the transaction will end.

So for example, instead of inserting 10,000 rows in a single insert, you can do 10 inserts of 1000 rows. It will execute just a tad slower, but will make your transaction interruptable without a large delay.

You can paginate through data either via sequence, as shown in Data Imports, or via a simple loop, if all data is in memory.

in my code, all rows are in memory, how can i paginate the inserts?

db.tx(async t => {
    while(/* there is data */) {

        // get the next set of rows from memory;

        const insert = pgPromise.helpers.insert(data, dataset_data_columns);

        await t.none(insert)
            .then(() => {
               if(/* need to interrupt */) {
                   throw new Error('Interrupting transaction');
               }
            });
    }

}).then().catch();