Support for per-statement SAVEPOINTs for Postgres
Here is an article, which explains in detail the problem and solution: https://community.pivotal.io/s/article/How-to-Overcome-the-Error-current-transaction-is-aborted-commands-ignored-until-end-of-transaction-block
And this is an example of UB code, which does the trick:
// ### ADDED just here for testing possible UB PG patch (feature request)
console.log('############=====================> SAVEPOINT')
this._batchControlStore.runSQL('SAVEPOINT myapp_temporary_savepoint')
console.log('############=====================> SAVEPOINT done')
try {
// ### ---
this._store.run('addParticipant', {
execParams: {ID: execParams.ID, subjectID, role: 'candidate'}
})
// ### ADDED just here for testing possible UB PG patch (feature request)
console.log('############=====================> SAVEPOINT release')
this._batchControlStore.runSQL('RELEASE myapp_temporary_savepoint', {})
console.log('############=====================> SAVEPOINT release done')
} catch (e) {
console.log('############=====================> SAVEPOINT rollback')
this._batchControlStore.runSQL('ROLLBACK TO myapp_temporary_savepoint', {})
console.log('############=====================> SAVEPOINT rollback done')
}
// ### ---
This feature request is to add support this for UnityBase PG DB connection, so that this would be done per statement implicitly by UnityBase, ideally, in a controlled manner, such as:
store.setOnErrorRollbackInteractive(true) // Set interactive rollback, for handling batches
try {
for (const batchItem of batchArray) {
try {
processBatchItem(batchItem) // Do something. Could fail
} catch (e) {
// Handle batch item error, and will to continue with further items, so that the whole batch does not fail
// ....
}
}
} finally {
store.setOnErrorRollbackInteractive(false) // Return the default mode
}
Note, that I needed to use another instance of store for controlling savepoints, otherwise I was getting error: EMetabaseException {"errorCode":38,"Message":"Instance data with name \"selectBeforeUpdate\" is read only"}
Edited by Andrii Bezuglyi