> Den 24. maj 2016 kl. 01.11 skrev James Schneider <[email protected]>:
> On Mon, May 23, 2016 at 12:58 PM, Erik Cederstrand
> <[email protected]> wrote:
>
> I have inherited a legacy Item model that has a composite unique key
> consisting of a Customer ID and a per-customer, incrementing Item ID. Assume
> I can't change the model.
>
> On inserts, the legacy code would let the database increment the Item ID to
> avoid race conditions. Something like this:
>
> INSERT INTO item_table (customer_id, item_id, name, ...) VALUES (123,
> (SELECT MAX(item_id) FROM item_table WHERE customer_id =123) + 1, 'MyItem',
> ...);
>
> Is there any way I can do the same using the Django ORM without opening up
> for race conditions? I.e. something better than:
>
> i = Item(customer_id=123, name='MyItem', ...)
> i.item_id =
> Item.objects.filter(customer_id=123).aggregate(Max('item_id'))['item_id__max']
> + 1
> i.save()
>
>
> I feel like an explicit transaction wrapping this set of queries would be the
> way to go to help avoid (or at least detect) a race condition:
Thank you for your suggestions! The default isolation level for PostgreSQL is
READ COMMITTED, so another thread could insert an identical (customer_id,
item_id) pair between the select and the insert. I don't think a transaction
would help here. I have a unique index on (customer_id, item_id) so save()
would fail, and I only have one write statement, so there's nothing else to
rollback if save() fails.
After digging around a bit more in the documentation and Django codebase, this
solution seems to work for me:
from django.db.models import Value
from .models import Customer, Item
class IncrementingValue(Value):
def __init__(self, field, *args, **kwargs):
super().__init__(*args, **kwargs)
self.f = field
def as_sql(self, compiler, connection):
return '(SELECT MAX(%s) FROM %s WHERE customer_id=%%s) + 1' %
(self.f.get_attname_column()[1], self.f.model._meta.db_table), [self.value.id]
c = Customer.objects.get(id=123)
i = Item.objects.create(customer=c, name='MyItem',
item_id=IncrementingValue(value=c, field=Item._meta.get_field('item_id')))
i.refresh_from_db()
I didn't audit thoroughly for SQL injections. I would have used the ORM to
generate the SQL for the SELECT MAX, but I can't seem to get the SQL for an
aggregate() query.
Thanks,
Erik
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/F41E71C4-A91F-4BF4-B3A5-6E70966FF2CC%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.
