Problem

While rebuilding Ancestor table in Confluence, the following error is noticed.

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.

Resolution

This error can happen due to issues with page tree moves in Confluence resulting in duplicates in Ancestor table. 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

6. Go to Cog icon > General configuration

7. Go to Logging and Profiling

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

   com.atlassian.confluence.pages.ancestors.HibernatePageAncestorManager

9. Access <Confluence Base URL>/admin/permissions/pagepermsadmin.action and click on the Rebuild ancestor table

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

11. 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

12. The following entry indicates it completed 

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