Problem

When trying to publish existing/new pages, users are met with the following error in the UI:

Or while trying to create a space:

The following appears in the atlassian-confluence.log

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_54C900_C_TEMPLATE_REF_pkey"
  Detail: Key ("ID")=(15) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)

The affected table in the sample error snippet above is AO_54C900_C_TEMPLATE_REF but this also affects other AO tables such as AO_6384AB_DISCOVERED.

Diagnosis

Diagnostic Steps

• Pay attention to the error message, specifically on the ID that was duplicated.
• In the example error snippet above, ID=15 already exists in the database while Confluence is attempting to insert another ID=15.
• All ID sequences start from 1 with the increment of 1, so try running the query below and check what is the current ID position for the table in question:
  

  • SELECT max("ID") FROM "AO_54C900_C_TEMPLATE_REF";

    Please replace the table name with the table name mentioned in your error message.

• If the ID return from the query above is any value greater than the ID mentioned in the error message, in this case, 15, please proceed with the solution steps below.

Cause

The database sequence of the table is out of sync or missing. 

Resolution

To check if the database sequence still exists, please run the query below and according to the example error above, you should be getting the value, 15:

SELECT last_value FROM "AO_54C900_C_TEMPLATE_REF_ID_seq";

Replace the sequence with the table name mentioned in your error message, the sequence naming convention is <table-name>_ID_seq

We will have to bring the sequence number back in sync with your current dataset, please run the query below again to retrieve the current highest ID value:

SELECT max("ID") FROM "AO_54C900_C_TEMPLATE_REF";

The new sequence number value would be the next "+1" value of your current highest ID value. For example, the max("ID") value return above is 41, we will set the sequence value to be 42:

ALTER SEQUENCE AO_54C900_C_TEMPLATE_REF_ID_seq RESTART WITH 42;

Repeat the same step for other tables if applicable.