Multiple active program statuses
Description
Environment
relates to
Activity
Paul Spaude
Query below to delete duplicates in case this comes up.
Postgres:
DELETE FROM person_program_status a USING (
SELECT MIN(ctid) as ctid, person_id
FROM person_program_status
WHERE expiration_date is null
GROUP BY person_id HAVING COUNT(*) > 1
) b
WHERE a.person_id = b.person_id
AND a.ctid <> b.ctidSQL Server:
WITH ps AS (
SELECT ROW_NUMBER() OVER(PARTITION BY person_id ORDER BY person_id) AS row
FROM person_program_status WHERE expiration_date is null)
DELETE FROM ps
WHERE row > 1;Paul Spaude
Immediate issue appears to be resolved. I'm still seeing multiple program status errors locally as pop-ups. Once I get a handle on where that issue is occurring I'll create a ticket. Otherwise the work around is to just set the student to an Active or other status and the issue goes away.
In any case, Person Directory works and returns results with multiple program statuses so this ticket is closed.
Paul Spaude
Linked this to https://apereo.atlassian.net/browse/SSP-3006#icft=SSP-3006Preview which will solve the real cause. Again this fixes only PersonDirectory to handle multiple ProgramStatuses safely and pick the latest status for PersonSearch/PersonDirectory.
Michael Sultzaberger
Modified the GET_PROGRAM_STATUS_FROM_PERSON_ID function to always return the lasted program status for the person id. The underlying transaction issue will be address SSP-3006.
Paul Spaude
Looks like this always could occur since ProgramStatus' creation, but certain features (PersonDirectory) make this much more noticeable.
PersonProgramStatusService is the culprit, it runs expireActive which contains saveAndFlush before adding a new PersonProgramStatus with another save and flush. The individual methods are not marked @Transactional although that may not be the best choice due to exceptions. See https://apereo.atlassian.net/browse/SSP-3006#icft=SSP-3006Preview and as such, the problem still may occur in certain circumstances.There are some ways we could refactor away the issue, not sure if there is a best way to solve this. It may be easier to fix PersonDirectory to select top of potentially multiple non-expired PersonProgramStatuses although that doesn't solve the issues elsewhere.
Seeing person records in the field associated with multiple unexpired program statuses. This most likely happens b/c the current approach to a program status transition reads the current set of statuses into memory, expires them all and adds a new one to that set, then flushes out the entire collection to the db, but without any of this being protected by any sort of lock, optimistic or otherwise. So concurrent requests to transition a student can result in multiple unexpired program statuses, one for each concurrent request.
Really a subset of a more general problem w/ the Hibernate layer where SSP doesn't use that framework's locking features, period.