status_changed_time can go back in time

[Bill Ott]

Evergreen 2.2
psql 9.1

I discovered items changing from reshelving to available much too quickly and seemingly randomly. I tracked the problem back to the status_changed_time coinciding with the copy's checkout time instead of its checkin time.

I further realized that this seemed to be isolated to floating items.

I verified in the audit tables that the status_changed_time was getting set backward. It seems that when copies are updated when floated, the update applies the entire copy object, which contains a stale status_changed_time. However, since the status is not changing, the trigger is ignored and the previous status_changed_time is returned.

I believe the answer is to change the trigger function to not allow the status_change_time to revert to an earlier date.

[Bill Ott (bott)]

The attached patch should prevent updates from applying an older status_changed_time than the existing value.

[Bill Ott (bott)]

Missed colon in the assignment in the previous patch!

[Bill Ott (bott)]

Thinko in previous patch. We only want to keep the old time if we're not resetting it to now().

[Jason Stephenson (jstephenson)]

Have you looked at this?

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=commitdiff;h=4b94821578792b6494278672f6d8d3a4f9c0b4cc

Which is part of another bug:

https://bugs.launchpad.net/evergreen/+bug/1220389

I don't want to see either of us wasting effort.

[Jason Stephenson (jstephenson)]

I should add that the point of the branch and bug that I added above are so that the status_changed time can go back in time.

I will see if I can reproduce the behavior reported by Bill in current master when I get a chance, which may mean never.

[Jason Stephenson (jstephenson)]

Further investigation leads me to believe that the better fix is to alter the code where the stale copy object is used so that it's status_changed_time is updated before the database representation is updated.

[Bill Ott (bott)]

I hadn't caught your change for bug 1220389. However, that wouldn't apply in this case, as the status is not changing. The copy floats after the check-in, so the NEW and OLD are both effectively 7, bailing immediately from the IF.

Refreshing the object prior to updating would be an option, although it requires another call to pull the data, and it leaves me with the question, is this the only place this phenomenon can occur?

Because this case is unique, in that it occurs when statuses are not really changing, it could coexist with your proposed change to the function.

[Josh Stompro (u-launchpad-stompro-org)]

Hello, I just ran into this issue with EG 2.10.6.

I can replicate it by doing a checkout without checkin. Checking out an item that is currently checked out, to someone else without checking it in first.

I noticed this because we were looking for issues with the reshelving complete script, where items go from checked out -> reshelving -> checked out -> available. In a few cases (3) when the reshelving complete script was run at the same time as the no-op checkin, the reshelving complete script grabbed the item because of the status_change_time being set back to the last checkout date.

So I can get it to trigger in two cases. Floating to a different branch, or doing a no-op checkin at the same branch.

Josh

[Rogan Hamby (rogan-hamby)]

Looking at this I agree with Jason that looking at the stale copy object is probably the better approach here. I'm assigning this to myself for now. I'm not sure I'll have time this week to look at it more but it doesn't appear to be a major point given the lack of activity on it.

[Jason Stephenson (jstephenson)]

I assume that Rogan is no longer looking into this given his mention of "this week" in the previous comment.