F() expression on update // using Max() aggregate -- race condition

Using Django Using the ORM
1

Hello all,

Please imagine the following model:

class Item(models.Model):
    # natural PK
    increment = models.PositiveIntegerField(_('Increment'), null=True, blank=True, default=None)
    # other fields

When inserting a new Item(), I want it to have the increment value set to the Max(increment) of that table. For example given the following table:

|_item____________________________|
|_id_|_increment__________________|
| 1  | 11                          |
| 2  | 12                          |
| 3  | 13                          |
| 4  | 14                          |
| 5  | 15                          |

When I do item = Item() I want the increment property to be automatically set to 16. Not client/python side, as that could cause some race conditions, but database-side, using an F() expression – ideally.

Is there any way of doing that? Pseudo/almost code example:

item = Item(
    increment=Max(increment_values_across_table)
)

Many thanks!

2

See AutoField and BigAutoField.

3

mm thanks, my example was misleading.
The model already has a ID field automatically inserted by django. And trying to add another AutoField (or BigautoField) results in:

AssertionError: Model xx.XX can't have more than one auto-generated field.

Which makes sense.

So is there a way?

4

Actually, I don’t agree that it “makes sense”. You can have multiple “serial” columns in PostgreSQL - each using a different sequence and (possibly) each having different values. (What database engine are you using?)

But, leaving that aside for the moment, if you already have an ID field, why do you need another? (This actually does make a difference as to what I might recommend.)

5

Yeah I agree with you … backend is mysql 8.x.
ID fields has holes in it (rows deleted, etc) and… shall we say … product people would like a series without. Which won’t happen because as soon as you delete a row … that auto-series gets a new hole.

6

You will never* prevent holes.

Why?

Deletes (as you’ve mentioned) and rollbacks. (Tracking the max value for the next insertion doesn’t prevent holes if it’s entry numbered “max - 2” that gets deleted. You’ll still have a gap between “max - 3 and max - 1”.)

“Product people” need to be taught to understand that in any reasonably sized database, “gapless sequences” are a fool’s errand.

*Note: It is theoretically possible to do that, but you’re going to kill your database doing so.

7

haha I know sigh. Guess I’m on for a long discussion … :expressionless:

8

I’ve always had the best luck by presenting it as the best of a set of options and letting them make the decision.
For example, you might be able to present the options as:

  • Allow for gaps
  • Use a table-lock to prevent concurrent access when inserts or deletes are performed
  • Add code to perform sequence validation when the table is updated.
  • Updates are “queued and scheduled” for deferred processing

(Each with the corresponding cost of adding / testing / validating / maintaining the additional code required to do this.)

It is possible that if the rate of updates is low enough, or the size of the table small enough, they might be willing to accept the trade-off. (e.g. If you’re talking about a table that averages one insert / month, it’s a much different conversation than a table that averages one insert / minute.)