1

I want to implement a row version number that increments whenever a table row is updated.

I'm using PostgreSQL.

Essentially I want this field to behave similarly to the following updated_at timestamp field: updated_at = models.DateTimeField(auto_now=True)

Except instead of updating with the current time, I want to auto-increment the current field value of the row. So if the row had version=1 and I made an update on the row, it would autoincrement that row to version=2.

I know I can implement this by overwriting the model save() method, but I was wondering if there was a way to implement this on the database level (and ideally through the Django ORM) so that it applies to any database accessing script/query.

Jad S
  • 2,705
  • 6
  • 29
  • 49
  • You can write an update trigger in virtually every database. It doesn't involve Django. – thebjorn Mar 09 '18 at 21:50
  • are you aware that field like `models.DateTimeField(auto_now=True)` won't be updated when performing operation like `YourModel.objects.filter().update(some_field=some_new_value)` ? Is it ok for you? – andilabs Mar 09 '18 at 22:51

1 Answers1

1

First of all are you aware that field like models.DateTimeField(auto_now=True) won't be updated when performing operation like YourModel.objects.filter().update(some_field=some_new_value) ? Is it ok for you?

1) If this is acceptable I will go for solution like that:

from django.db.models import F
from django.dispatch import receiver
from django.core.signals import post_save


@receiver(post_save, sender=YourModel)
def ensure_profile_exists(sender, instance, created, **kwargs):
    sender.objects.filter(pk=instance.pk).update(version=F('version')+1)

you can place that code inside your model definitions.

2) If you need to handle also .update() changes you should go for writing custom postgres trigger (see here docs https://www.postgresql.org/docs/10/static/plpgsql-trigger.html). Once created can be then registred in database using fake migration. See here: https://stackoverflow.com/a/31698995/953553

andilabs
  • 22,159
  • 14
  • 114
  • 151
  • 1
    Awesome. I didn't know that about the `update` function. Definitely good to know. I think writing a migration with the SQL could be a winning solution. Do you know if this kind of trigger could also work on the SQLite DB I use in dev? – Jad S Mar 10 '18 at 22:45
  • 1
    you will need to distiniguish in migrations against what backend it shoudl be applied and choose the right trigger code for each – andilabs Mar 10 '18 at 22:56