How can a unique timestamp be incremented on constraint violation in a Django model?

Question

As part of using TimescaleDB, which requires a timestamp as the primary key (time in SensorReading), I need to handle the case when the same timestamp is used by different sensor values. One elegant solution might be to smear colliding timestamps (add a microsecond on collision).

How can this problem be solved in a robust and performant manner for the following models?

class Sensor(models.Model):
    name = models.CharField(max_length=50)

class SensorReading(models.Model):
    time = models.DateTimeField(primary_key=True, default=datetime.now)
    sensor = models.ForeignKey(Sensor, on_delete=models.CASCADE)
    value = models.FloatField()

P.S. This is a workaround as Django does not support composite primary keys. Otherwise it would be possible to set the sensor and timestamp as a composite primary key.

Answer 1

One solution I found was to use a try/except block around the model save call. Try to add a sensor reading, which will succeed most of the time, but if a collision occurs handle that error. Ensure that the exception thrown is exactly because of that collision and then increment the timestamp by one microsecond (the smallest resolution). Then repeat the try to save the sensor reading. This will almost always succeed due to the low collision probability in the first place. Below is the code tested to recursively handle incrementing the timestamp until it succeeds.

class SensorReading(models.Model):
    time = models.DateTimeField(primary_key=True, default=datetime.now)
    sensor = models.ForeignKey(Sensor, on_delete=models.CASCADE)
    value = models.FloatField()

    def save(self, *args, **kwargs):
        self.save_increment_time_on_duplicate(*args, **kwargs)
    
    def save_increment_time_on_duplicate(self, *args, **kwargs):
        try:
            super().save(*args, **kwargs)
        except IntegrityError as exception:
            if all (k in exception.args[0] for k in ("Key","time", "already exists")):
                self.time = str(parse_datetime(self.time) + timedelta(microseconds=1))
                self.save_increment_time_on_duplicate(*args, **kwargs)

A more robust implementation might also add a max number of tries before aborting.

Answer 2

To work with timescale db, I make a virtual primary key field, that tricks the Django and represents compound key value as a single json tuple.

https://viewflow.medium.com/the-django-compositeforeignkey-field-get-access-to-a-legacy-database-without-altering-db-tables-74abc9868026

You could check the code sample here - https://github.com/viewflow/cookbook/tree/v2/timescale_db

Answer 3

In general, I'd recommend taking the table management for that sensors table at least somewhat out of Django, do your insertion with psycopg2 or another driver, and create a proper primary key on Sensor, time, then expose the table (or a view on top of it) as a Django model which it can read from, perhaps with a join if you need to. These should be write once, so you shouldn't have to deal with updates which is usually why it needs the non-compound primary key. At some point Django should really start supporting compound primary keys though, it's too bad they don't.

The other approaches may work, but they likely won't scale very well at all. So if you care about ingest rate, I might try something different.