update_or_insert -- I end up with duplicates

[Brad Miller]

Hi All,

I have the following update or insert statement in a controller.

            db.grades.update_or_insert(

                ((db.grades.auth_user == student.id) &

                 (db.grades.assignment == assignment.id)),

                auth_user = student.id,

                assignment = assignment.id,

                score=score)

The function that this is part of is called as the result of a user pressing a button on the web page.  It could get pressed multiple times in quick succession. (Yes, I'll fix that too).  ButI was surprised that I end up with duplicate values every now and then.   This is on top of Postgresql with psycopg2 driver.  I added a constraint to the table definition to ensure that user,assignment pairs are unique and now at least I can catch the IntegrityErrors when they happen.

Since this is running in an multi-process environment I guess I could see how this might happen, but it seems like a bug.  Or am I doing something stupid?

Brad

[Anthony]

update_or_insert first selects the record (if it exists) and then does the create/update. I suppose when two requests come in very close to each other, the second request is checking for the record before the first request has had a chance to commit the insert. I'm not sure I would call that a bug. The alternative would be to lock the database table before the read, but that would stop all other reads on the table and would introduce complexities with web2py's transaction-per-request workflow (e.g., in Postgres, to release a lock, the transaction must end, but if we explicitly commit a transaction in update_or_insert, that would prematurely close the transaction that is intended to remain open for the duration of the HTTP request). I suppose we could add an option to lock the table.

Anthony

[pbreit]

I'm surprised this would be a problem unless you have a very high traffic service or there's something else going on with your code.

If it really is a problem, another option you could consider is a new column with enforced uniqueness that stores some sort of mashup of the two fields ( as simple as "%s-%s" % (student.id, assignment.id) ).