Django, Postgresql & IntegrityErrors

Question

I come from a mysql background where if I want to insert a record I can use an INSERT IGNORE command so that I can just bung rows in and if there's a conflict just ignore it.

Now, with a Django project I'm working on I'm using postgresql, and issue multiple saves in a loop. However, I think if I get an IntegrityError (which I'm ignoring, see code below) then I lose loads of objects that I'm trying to insert.. how should I fix this:

for thing in things:
    try:
        #potentially dangerous save..
        obj = Thing(stuff=10)
        obj.save()
    except IntegrityError:
        pass

If say, on the 5th go round the loop an IntegrityError happens, which objects will get inserted into the DB?

coler-j · Accepted Answer · 2018-07-11 00:02:19Z

8

It will depend on how you manage your transaction.

On Postgres if one of query in transaction fails all subsequent query will also fail with error "current transaction is aborted, queries ignored until end of transaction block".

To deal with it you should use transaction.savepoint_rollback in an except block.

Please refer to Django doc Handling exceptions within PostgreSQL transactions

It gives the next example

a.save() # Succeeds, and never undone by savepoint rollback
try:
    sid = transaction.savepoint()
    b.save() # Could throw exception
    transaction.savepoint_commit(sid)
except IntegrityError:
    transaction.savepoint_rollback(sid)
c.save() # Succeeds, and a.save() is never undone

edited Jul 11, 2018 at 0:02

coler-j

2,2484 gold badges36 silver badges65 bronze badges

answered Nov 18, 2011 at 15:20

Igor

3,1791 gold badge24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-11-18 15:17:39Z

1

By not ignoring errors?

The code you wrote works kind of like this.

You:    Save all the things.
Django: You're about to violate data integrity in your database.
You:    I don't care.
Django: I can't simply corrupt your data. I'll just throw away 
        the things that would corrupt your data.
You:    That's what I wanted.

While that might be appropriate for some applications, it not appropriate for most.

You just need to decide what you ought to be doing with data that violates your integrity constraints, and you need to fix the code that's allowing bad data to get to the save() method in the first place. Documentation on Form and Field Validation might help.

answered Nov 18, 2011 at 15:17

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Collectives™ on Stack Overflow

Django, Postgresql & IntegrityErrors

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related