Error when publishing pages - ERROR: duplicate key value violates unique constraint "<tablename>_pkey"
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Problem
When trying to publish existing/new pages, users are met with the following error in the UI:
"Unable to communicate with server. Saving is not possible at the moment."
Or while trying to create a space:
"An unknown server error has occurred."
The following appears in the atlassian-confluence.log
1
2
3
4
5
6
7
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=15already exists in the database while Confluence is attempting to insert anotherID=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:
1SELECT 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.
Solution
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:
1
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:
1
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:
1
ALTER SEQUENCE AO_54C900_C_TEMPLATE_REF_ID_seq RESTART WITH 42;Repeat the same step for other tables if applicable.
Was this helpful?