#17415: Creating an object with an explicit primary key value causes the next
creation to fail under PostgreSQL
-------------------------------------+-------------------------------------
Reporter: niko@… | Owner: nobody
Type: Bug | Status: new
Component: contrib.sites | Version: SVN
Severity: Release blocker | Resolution:
Keywords: site admin create | Triage Stage: Accepted
db orm | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
There are some concurrency problems when setting the sequence to "largest
PK + 1". The problem is that you will not see objects created in other
concurrent transactions, and thus largest PK is not guaranteed to be
actually the largest PK. This could cause subtle bugs where everything
seems to work, except in rare concurrent cases where it doesn't. This
would be very hard to debug. It is worth considering that creating an
object with autofield PK set should be an error, except if force_insert is
defined, or in raw mode (that is, in fixture loading). Document that if
you use force_insert for a model with set autofield PK, then you are
responsible for resetting the sequence (or do that automatically in
force_insert case?).
The underlying problem is that on SQL level, you should not insert your
own values to a column which gets its value from a sequence. It is OK to
do this in non-concurrent setting (as in restore from backup, load
fixtures). You should not expect it to work in concurrent setting. If
Django automatically resets the sequence, it makes it look like it is OK
to insert values to a serial field. But it is not. Unless you lock the
table for inserts. The whole idea of sequences is that they allow
concurrent inserts to a table without using max(id) + 1, which just
doesn't work. (SQLite has one transaction at a time, so there are no
concurrency problems, MySQL probably does some tricks under the hood, so
that it can get the actual largest id even in concurrent cases).
Consider this case, with two transactions T1 and T2, and id_seq starting
from 10. Largest ID in the table is 10:
{{{
T1: begin;
T1: Obj.save() # gets nextval('id_seq') => 11
T1: Obj.save() # gets nextval('id_seq') => 12
T2: begin;
T2: Obj(pk=1).save() # where pk=1 does not exist
T2: reset_sequence('id_seq'): finds the largest visible id: 10 sets
sequence to 11.
T1: commit; T2: commit;
}}}
Now you have: largest id in table: 12, id_seq starts from 11.
Yes, this is a rare condition. But I believe Django should not expose this
possible concurrency problem. So, I suggest this is fixed by "disallow
insert into autofield with user defined value, unless it is asked
explicitly". If reset_sequence is going to be used, then also lock the
table (reset sequence should probably do this in any case...).
--
Ticket URL: <https://code.djangoproject.com/ticket/17415#comment:9>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
