pg-promise: Nested transaction behavior - maybe incorrect?

I have a situation that kind of looks like this… (stole this from the pg-promise tests)

db.tx(function (t1) {
    context1 = this;
    return this.none('update users set login=$1', ['External']).then(function () {
        return context1.tx(function (t2) {
            return t2.none('update users set login=$1', ['External']) 
        });
    }).then(function () {
        return context1.one('select * from unknowntable') // emulating a bad query;
    })
})

The premise - it creates a transaction, then does something, then creates an inner transaction that does something else, then finishes the inner transaction, then does something else which causes the outer transaction to rollback. I know it’s a contrived situation, but when you have situations involving other dependant methods the composition does make sense. This is just a simplified form.

The pg-monitor outlook looks like this…

12:21:30 tx/start 12:21:30 tx: begin 12:21:30 tx: update users set login=‘External’ 12:21:30 tx/start 12:21:30 tx: begin 12:21:30 tx: select * from users 12:21:30 tx: commit 12:21:30 tx/end; duration: .004, success: true 12:21:30 tx: select * from unknowntable 12:21:30 error: relation “unknowntable” does not exist tx: select * from unknowntable 12:21:30 tx: rollback 12:21:30 tx/end; duration: .013, success: false

You can see in the bolded line that pg-promise finishes the inner transaction and issues a commit. This is incorrect behaviour as there is an outer scope in play, and PG does not support nested transactions. This commit actually commits the initial outer scope update, as well as commiting the inner scope. Shouldn’t it hold the commit until the outer transaction completes?

Any advice on this situation? Thanks!

About this issue

  • Original URL
  • State: closed
  • Created 9 years ago
  • Reactions: 1
  • Comments: 30 (16 by maintainers)

Commits related to this issue

Most upvoted comments

@vertiman the issue has been fixed in v.2.2.0.

Please let me know if it all works for you now as expected.

UPDATE: pg-monitor has been updated to recognize savepoint as special commands.