Orphaned entries in the OS_PROPERTYENTRY table cause failure in space import

Orphaned entries should be removed from space export/OS_PROPERTYENTRY table otherwise space import would failed with the following error (for example):

Caused by: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Error while importing backup: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=119570525,entityName=confluence_ContentEntityObject,key=net.customware.confluence.plugin.toc:cached:1,type=6,boolean=false,string=<null>,text=<div>

com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Error while importing backup: could not insert: [bucket.user.propertyset.BucketPropertySetItem#BucketPropertySetItem[entityId=117440539,entityName=confluence_ContentEntityObject,key=isbookmark,type=5,boolean=false,string=<null>,text=<null>,int=0,double=0.0,long=0,date=<null>]]

Logs also show:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "os_propertyentry_pkey"

Workaround:

As a precaution measure, backup the database before making any changes.

1. Run the following queries to identify the orphaned entries in Confluence database:

   SELECT * FROM OS_PROPERTYENTRY WHERE entity_key LIKE '%net.customware.confluence.plugin.toc%';
   

   SELECT
       *
   FROM
       OS_PROPERTYENTRY
   WHERE
       OS_PROPERTYENTRY.entity_name='confluence_ContentEntityObject'
   AND OS_PROPERTYENTRY.entity_id NOT IN
       (
           SELECT
               CONTENTID
           FROM
               CONTENT)
               order by 2,3;
   

   If the queries above returned a result, proceed with the following:

2. Shut down Confluence
3. The queries to delete the orphaned entries from the database:

   DELETE FROM OS_PROPERTYENTRY WHERE entity_key LIKE '%net.customware.confluence.plugin.toc%';
   

   Note that this query is an example and refers to the entity_key value referred to in the error mentioned earlier. Depending on the imported os_propertyentry data, it may be required to remove entries with a different key.

   DELETE
   FROM
       OS_PROPERTYENTRY
   WHERE
       OS_PROPERTYENTRY.entity_name='confluence_ContentEntityObject'
   AND OS_PROPERTYENTRY.entity_id NOT IN
       (
           SELECT
               CONTENTID
           FROM
               CONTENT);
   

4. Start Confluence
5. Restore the space backup (make sure there is no identical space key exist before the restoration)