Summary

While rebuilding Ancestor table in Confluence, the following error is noticed in the Atlassian Confluence logs: 

2020-08-05 21:25:25,308 ERROR [http-nio-8080-exec-4] [confluence.pages.ancestors.HibernatePageAncestorManager] addAncestorsToTableForDescendant Couldn't execute statement
 -- referer: https://confluence/admin/permissions/pagepermsadmin.action | url: /admin/permissions/rebuild_ancestor_table.action | traceId: c9365ae3379de36f | userName: admin | action: rebuild_ancestor_table
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
	.
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "idx_16387908_primary"
  Detail: Key (descendentid, ancestorposition)=(729820032, 0) already exists.

Cause

This error may happen due to issues with page tree movements in Confluence resulting in duplicates entries in the Ancestor table ( CONFANCESTORS ).

Solution

To resolve this issue go through the following steps.

1. Stop Confluence on all nodes
2. Take backup of the database of Confluence

3. Delete all contents from the confancestors table

   TRUNCATE table confancestors;

4. Start Confluence on only one node

5. Access Confluence Administration UI and browse to General configuration > Logging and Profiling

6. Add the following entry and set the logging level to DEBUG

   com.atlassian.confluence.pages.ancestors.HibernatePageAncestorManager

7. Rebuild Ancestor Data

   Prior to Confluence 7.20.0

   Access to <CONFLUENCE_BASE_URL>/admin/permissions/pagepermsadmin.action and click on the Rebuild ancestor table

   After Confluence 7.20.0

   Head over to General Configuration > Scheduled Job >  Repair the Ancestors Table 

8. Follow the rebuild process through the application log -- atlassian-confluence.log

9. The following entry indicates the rebuild started 

   2020-08-07 15:19:19,613 INFO [http-nio-6740-exec-8] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Started

10. The following entry indicates when it is completed 

    2020-08-07 15:19:19,637 INFO [http-nio-6740-exec-8] [confluence.pages.ancestors.HibernatePageAncestorManager] rebuild Complete!