Pg-promise: Rollback a transaction with intermediate data

[ghost]

Hello @vitaly-t,

I believe this is more of a question/misunderstanding rather than an issue, similar to #33. I have a transaction that first inserts a user into the db. Then with the user id returned from that insertion, I insert a pin. Here's the problem: when inserting the pin fails, the user that was inserted remains. That is, the "insert into user ..." query is not rolled back when the "insert into pin ..." query fails.

db.tx(function(){
    console.log("inserting user:");
    return db.one('insert into user (name) values ($1) returning id', ["example"])
        .then(function (user) {
            console.log("inserting pin:");
            return db.one('insert into pin (user_id, pin) values ($1, $2) returning id',[user.id, pin])
                .then(function () {
                    return user.id;
                }, function(error){
                    console.log("error inserting pin")
                    throw error;
                });
        }, function(error){
            console.log("error inserting user");
            throw error;
        });

}).then(function(data){
    console.log("SUCCESS",data); // printing successful transaction output 
}, function(reason){
    console.log("FAIL", reason); // printing the reason why the transaction was rejected 
});

The transaction documentation reads: "4. Executes COMMIT, if the callback resolves, or ROLLBACK, if the callback rejects". So it appears that the "insert into user ..." query is committed when the corresponding promise is resolved (in order to get the user id).

To be very clear, I want to rollback the "insert into user ..." query when the "insert into pin ..." query fails. I've tried a few different approaches but so far I've had no luck. I'm sure there's a way to accomplish this but I could use some guidance.

I'm using pg-promise v.1.7.8 with PostgreSQL v9.4.

pg-promise is awesome, keep up the good work!

[vitaly-t]

This does look like #33, a misunderstanding of how promises work, although with more problems :).

For the logic you describe your code should look like this:

db.tx(function (t) {
    console.log("inserting user:");
    return t.one('insert into user (name) values ($1) returning id', ["example"])
        .then(function (user) {
            console.log("inserting pin:");
            return t.one('insert into pin (user_id, pin) values ($1, $2) returning id', [user.id, pin])
                .then(function () {
                    return promise.resolve(user.id);
                });
        });
})
    .then(function (data) {
        console.log("SUCCESS", data); // printing successful transaction output 
    }, function (reason) {
        console.log("FAIL", reason); // printing the reason why the transaction was rejected 
    });

When you want the transaction to return data, you resolve with that data, you do not just return it.

And you should skip those reject sections, they are provided by default, that's just an anti-pattern.

Lastly, you are making the wrong calls inside the transaction, i.e. into the wrong connection context. You are supposed to be making calls against the transaction context - parameter t as in the example, not the root database context, which sits outside of transaction.

There is still a problem there though, I've changed it to resolve with the user's id, but that poses a question as to why your query returns id for the inserted pin, if you are not using it....

[ghost]

You are spot on, using the correct connection context and resolving with the data instead of simply returning it is exactly what I needed to do. The transaction behaves as expected now.

Perhaps you'll consider adding an example of a transaction like this to the documentation. It seems like a relatively common and simple use case that emphasizes key ideas (such as using the proper connection context) :)

Anyway, I appreciate your prompt help, thank you!

[vitaly-t]

Use of proper connection context is shown in every transaction example, and resolving data instead of returning it is the very basic principle of using promises, not so much relevant to this library that just uses promises everywhere.

Perhaps you'll consider adding an example of a transaction like this to the documentation. It seems like a relatively common and simple use case that emphasizes key ideas (such as using the proper connection context) :)