0

The problem

I have to update several records R1, R2, ..., Rn in a Postgres database using sqlalchemy. The operation has to be atomic; i.e., if Ri fails the whole transaction has to be rolled back.

The code is something like this:

try:
    for instance_id in payload:
        instance = get_or_404(instance_id)
        sql = build_sql_for_patch(instance, payload)
        db.session.add(sql)
    db.session.commit()
except Exception:
    db.session.rollback()
    raise ValueError(...)

In case of errors, I'd like to return a message like this: 

{
   "errors": [
      {"record": 1, "reason": "Something is wrong with record 1."},
      {"record": 4, "reason": "Something is wrong with record 4."},
      ...
   ]
}

The question

Is there a way to get the Postgres transaction errors with this granularity? Ideally, I'd like to get an error for each record that fails. If this is not possible, I'd like to get the record number that fails with its proper error message.

Improve this question
3
  • 1
    If nothing else, you could try each insert in a SAVEPOINT, setting a flag that controls whether to rollback or commit. It's not very performing, though, so for huge bulk operations it'll be slow. Commented Jul 5, 2017 at 10:35
  • I'll take a look at SAVEPOINT, thanks! Commented Jul 5, 2017 at 13:12
  • If you do, here's SQLA docs on the subject: docs.sqlalchemy.org/en/latest/orm/… Commented Jul 5, 2017 at 14:26
Related questions
9
Transaction within transaction
4
Handling transaction conflict with SQLAlchemy
30
SQLAlchemy error handling - how is it done?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.