Discovered when testing fix for
The update-sequences.sql unfortunately may put your database into an unstable state, as the behavior of assigning Handles has changed. The change in behavior seems to have occurred in
DS-3086 / https://github.com/DSpace/DSpace/pull/1326 (specifically this commit: https://github.com/DSpace/DSpace/commit/8db0faed33f145488aea23efbb30ab5a732a8572)
Here's what seems to be going on:
- update-sequences.sql always sets `handle_seq` to the largest value of handle suffix (in handle column): https://github.com/DSpace/DSpace/blob/master/dspace/etc/postgres/update-sequences.sql#L96
- However, when assigning/minting a new Handle, the largest value of handle_id is used: https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/handle/HandleServiceImpl.java#L136
- Additionally, Hibernate is being told that handle_seq should be used for handle_id column, see this set of annotations: https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/handle/Handle.java#L27
As you may have guessed, the combination of these settings only works when handle_id and the suffix in handle are synchronized. As soon as they become unsynced (e.g. during an AIP restore, where predefined handles may be restored), database errors will occur.
As a basic example...in my local database, I only had 8 handles minted (with suffixes 0 through 7). Running update-sequences.sql set the handle_seq to 7 (the value of the highest suffix). Errors occurred as soon as I deposited a new item, as it attempted to create handle_id=8 (which already existed).
To clarify further, this new approach to minting handles is fundamentally flawed. It assumes that the next available prefix is always equal to the next `handle_id`. This will almost never be true if you restore Items via AIPs, as they come with pre-assigned handles and there is no guarantee that those pre-assigned handle suffixes will somehow match up with the number of items restored.