Suggestion

JSM's PSMQ (message queue) system uses two database tables to track messages and queue - AO_319474_MESSAGE and AO_319474_QUEUE

Messages and created and destroyed dynamically with the running application. Queue's are created and updated on demand, but are not removed dynamically. Instead, a scheduled job, com.atlassian.psmq.internal.scheduled.QueueCleanupScheduledJob ,  is used to clean up queues that have not been used in 7 days.

The logic is defined in psmq/core/src/main/java/com/atlassian/psmq/internal/io/db/QueueCleanupDao.java:

        final List<Long> idsToDelete =
                databaseConnection.select(QUEUE.ID)
                        .forUpdate()
                        .from(QUEUE)
                        .where(QUEUE.MODIFIED_TIME.loe(unusedSince.getMillis())
                                .and(QUEUE.CLAIMANT.isNull().and(QUEUE.CLAIMANT_TIME.isNull().or(QUEUE.CLAIMANT_TIME.loe(unusedSince.getMillis()))))
                                .and(QUEUE.ID.notIn(select(MESSAGE.QUEUE_ID).distinct().from(MESSAGE))))
                        .limit(limit)
                        .fetch();

This works well, however it does not cater for MESSAGE rows that are not able to get destroyed

.and(QUEUE.ID.notIn(select(MESSAGE.QUEUE_ID).distinct().from(MESSAGE))))

Known trigger for stale MESSAGE rows

• Application is restarted with Active Messages due to PSMQ deadlock (In JSM <v4.9)

This results in stale MESSAGEs building up in the system, of whom's QUEUES then cannot be cleaned up.

Suggested Solution

• Modify the logic to remove MESSAGE rows if they are not claimed within 7 days. This would result in the queue clean up task to remove queues that aren't used, but are held open by stale message rows being present.