Assign a custom field order_sl
I'm working with a Django model where I need to assign a custom field order_sl that should increment from the last existing value with a specific filter.
Here's what I'm doing currently:
prev_ordr = Order.objects.filter(abc).values("order_sl").first()
if prev_ordr:
new_order_sl = prev_ordr.get("order_sl")
else:
new_order_sl = 100000
ins.order_sl = F("order_sl") + (new_order_sl + 1)
ins.save()
ins.refresh_from_db()
But I believe this approach is problematic:
It pulls the last value into Python instead of handling it in the DB.
It’s not thread-safe and likely to break under race conditions if multiple inserts happen at the same time.
I'm using PostgreSQL, and I want to ensure that each new row gets a unique, sequential order_sl based on the highest existing value or a dedicated sequence. The order_sl field is not the primary key, and I don’t want to make it an AutoField.
❓ What’s the best, Django-safe, PostgreSQL-optimized way to handle this? Would a raw SQL sequence be better? Or should I use select_for_update() with a transaction block?
Thanks!