Pg-promise: How to fail a transaction when update affected 0 rows

[dorongrinstein]

When updating one table and inserting into another table in the context of a transaction (for example when the update is the main operation and the insert is into an audit table), I'd like the transaction to fail if the update affected zero rows. I can detect zero row update using .result, but I don't know how to do that in the context of a transaction. thanks.

[vitaly-t]

db.tx(t=> {
        return t.result("UPDATE table bla-bla")
            .then(result=> {
                if (!result.rowCount) {
                    // 1. throw new Error("Ops, not updated anything!");
                    // 2. return Promise.reject("Ops, not updated anything!");
                }
            });
    })
    .catch(error=> {
        // 1. error.message = "Ops, not updated anything!";
        // 2. error = "Ops, not updated anything!";
    });

[dorongrinstein]

amazing response time. THANK YOU!!!

[vitaly-t]

You are welcome, and I have updated the answer for completeness ;)

[dorongrinstein]

sorry, one more question -- can this be rolled into a batch (multiple part) transaction?

[vitaly-t]

Any query can be part of a batch. Can you give an example of what you mean exactly? I'm not quite clear there...

[vitaly-t]

If you mean something like this, then certainly:

db.tx(t=> {
        var updates = [];
        for (var i = 0; i < 100; i++) {
            var query = t.result("UPDATE table bla-bla")
                .then(result=> {
                    if (!result.rowCount) {
                        return Promise.reject("Ops, not updated anything!");
                    }
                });
            updates.push(query);
        }
        return t.batch(updates);
    })
    .catch(e=> {
        var errors = e.getErrors(); // to get only the text statements;
    });

[dorongrinstein]

var r = db.tx(function(t) {
t.batch([
t.none('update foo set field1 = ${f1} where id = ${id}, {f1: 10, id: 1}),
t.one("insert into foo_audit values ('1', 'abc')")
]);
});

I want the transaction to fail if the update did not affect any rows. thanks

[vitaly-t]

Doesn't my example above show exactly that? Unless you want it only to fail when the overall update from all requests resulted in 0 rows, then you would change it accordingly.

[dorongrinstein]

I'll check into it in a few. In a meeting now. THANK YOU again for the super prompt response!

[vitaly-t]

n.p.

at this point it's mostly about how to use promises, not so much about pg-promise itself ;)

[dorongrinstein]

I see what you did there. Perfect! thank you!!

[dorongrinstein]

You are a genius!! When you visit L.A., ping me, I owe you a beer!

[vitaly-t]

You are a genius!! When you visit L.A., ping me, I owe you a beer!

If i'm there next time, it will be Florida. Other than that I'm in Dublin, Ireland, the beer is good here too :)

Cheers! :+1: