Unable to purge the trash if notification for old version of a page is present

1. View an old version of a page
2. Choose "Watch" from the tools menu
3. View the current version of that page
4. Remove that page
5. Go to space admin and purge the trash
6. You will get an exception because of a foreign key constraint

The problem here is that purging the trash does not delete notifications on old versions of a page (which should never exist anyway).

Workaround

Backup your database before attempting to perform this! (the following removes all notifications from all deleted pages in the space):

1. Identify the SPACEID of the space which you are trying to purge all trash from, via this query:

   SELECT * FROM SPACES;
   

2. Use the SPACEID in the following query (this query is applicable to Confluence 5.3 only):

   DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT WHERE (CONTENT_STATUS = 'deleted' AND SPACEID = <SPACEID>) OR PREVVER IN (SELECT CONTENTID FROM CONTENT WHERE CONTENT_STATUS = 'deleted' AND SPACEID = <SPACEID>));
   

3. Run the query above, then try to purge the trash again