Orderable Version Incrementing

Hello everyone,

I’ve been working on https://openlmis.atlassian.net/browse/OLMIS-6062 ticket recently to implement incrementing of an orderable version. We do have this functionality working on service level but there was a will to change it to repository layer.
In this post I would like to discuss the approach we should follow on this issue.

First of all, the solution doesn’t seem to be very obvious because we want to increment a versionId which is a part of our composite key. Using GeneratedValue annotation does not work on Embeddable class which is then used as EmbeddedId. One thing that I’ve also tried was to use IdClass annotation on Orderable entity pointing to OrderableIdentity class and then use GeneratedValue on versionId marked as @Id field, but this solution also didn’t end up with saving a new record in a table with a version being incremented.

Taking this into account, I’m afraid that there might be a necessity to use a trigger in order to achieve the goal.
Another idea would be to use Hibernate Envers library. This library offers auditing and versioning of persistent classes.
This approach requires adding two additional tables: first one (REVINFO) with revision number (which is a version indeed) and revision timestamp and second one (orderables_AUD) with audited data.

When persisting the same entity, there would be 3 insert statements generated to the following tables: orderables, revinfo and orderabels_AUD. It means that after updating an entity, a new audit log entry would be added and the previous one would be marked as no longer valid. I was thinking that the latest revision number value could be somehow assigned to our version id.

Maybe you have some other ideas about handling this issue? I will appreciate your feedback and advice on what approach to decide on.

Regards,
Paulina.

1 Like

Thanks @Paulina_Buzderewicz,

Given that this table is so infrequently updated (bulk update once a week?), I might recommend simply relying on Postgres’ Serializable isolation level.

If an update fails due to that isolation level then I don’t even think we need to figure out a retry method, the edge case is so far to the edge that I doubt we’d ever hit it.

@Sebastian_Brudzinski I know you’ve recommended changing an isolation level before and we went with idempotency keys for that problem. Do you think my characterization of how (in)frequently Orderable’s are updated is right?

Welcome your thoughts.

Best,
Josh

Yes, I’d say it may even be updated less frequently than once a week, and having two people do it at the same time is highly unlikely. I’d agree that we can go with the simplest solution, rather than trying to find a fancy way of fixing a problem we probably don’t even have.

Also, at this point versionId is a part of the primary key in orderables, so we cannot end up with a corrupted database state anyways. We might be shifting from that, so finding another, simple way to prevent duplicate versions is the way I’d go.

Best,
Sebastian

Thanks for the answers. Summing up, we don’t want to increment version id on repository layer, as the scenario that two update events would be done at the same time seems to be unlikely.

Instead, changing an isolation level could be the way to go, however do you think that it should be done within this ticket? If not, we would mark https://openlmis.atlassian.net/browse/OLMIS-6062 as dead then.

In case we want to separate our primary key (which may be necessary for our current ticket to drop the reference to orderable version from FTAP: https://openlmis.atlassian.net/browse/OLMIS-6351), having a unique constraint of orderble id and version id might be another way to prevent duplicate versions.

Best,
Paulina

It seems that having the primary key on orderableId and versionId is just enough. If we decide to split the composite primary key in orderables though, the unique constraint should suffice. Any objections to that?

Best,
Sebastian

The constraint should be fine for duplicate versions.

As far as OLMIS-6062 @Paulina_Buzderewicz aren’t we going to update it to reflect that the isolation level is/has been changed?

Since OLMIS-6062 mainly affects Orderable version, I created a new ticket for introducing the isolation level. It can be found here: https://openlmis.atlassian.net/browse/OLMIS-6390. I also marked OLMIS-6062 as Dead.