Resolving Index Corruption and Primary Key Duplication in PostgreSQL

Resolving Index Corruption and Primary Key Duplication in PostgreSQL

Overview

This KB provides guidance for identifying and resolving Primary Key (PK) duplication errors that typically occur due to data corruption or constraint issues.

Step 1: Verify the Error from Logs

     Corruption errors may surface in different scenarios. Check the appropriate log file based on the failure:
Info
      a. Upgrade failure → Review updatemgrlogXX.txt for corruption errors.
      b. Backup failure → Review /pgsql/data/pg_log for corruption errors.
      c. Daily pgbackup failure → Review logs/sps/pgsql/dailypgbackupX.txt for corruption errors.

Look for errors similar to:

Quote
[SEVERE] : SQLState :: [23505], Error Code:: [0]
[SEVERE] : Returning [true] from isDBAlive method
[INFO] : Exception in handleRequestConversationMigration
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "requestnotificationtodesc_pk"
Key indicator :

Warning
ERROR: duplicate key value violates unique constraint "<table_name>_pk"

Step 2: Pre-Resolution Action

If the above PK duplication / index corruption error is confirmed:

  1. Stop the application server.

  2. Take a snapshot of the PostgreSQL folder (pgsql) before attempting any corruption resolution.

    • This ensures you have a rollback point in case the cleanup process causes unexpected issues.

Alert

If the error is different from the above PK duplication issue → do not proceed with cleanup. Instead:

  • Collect the complete pgsql folder in debugging mode.

  • Move the ticket to G3-Sunil Team for detailed corruption analysis and resolution.

Step 3: Resolve Primary Key Duplication

Notes
 The query must be executed during downtime to resolve corruption for the tables listed below. If corruption is detected in any other table, collect the complete pgsql folder and move the ticket to the G3-Sunil Team.

Table : arc_notification, notificationtodesc, arc_workorderhistorydiff, workorderhistorydiff, workordertodescription, requestnotificationtodesc, workorderstates, workorder, conversationdescription, requestresolution, workordernotes, adsadsyncobjects, adsadsyncobjattributes, spaceparent, dcparameters, UserDepartment

      Use the following template query inside a transaction to remove duplicate rows:

Idea
BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
DELETE FROM <table_name> a USING <table_name> b WHERE a.ctid < b.ctid AND a.<pk_column1> = b.<pk_column1> [AND a.<pk_column2> = b.<pk_column2> ...];
COMMIT;
Info
What to Replace:
<table_name> → Replace with the table name from the error message.
Example: requestnotificationtodesc or adsadsyncobjattributes.
<pk_column1>, <pk_column2>, … → Replace with the primary key column(s) of that table.

For single-column PK tables → use just that column.
For composite PK tables → include all PK columns with AND conditions.

Example 1: Single Primary Key Column
Warning
Error:

ERROR: duplicate key value violates unique constraint "requestnotificationtodesc_pk"

PK column = notificationid

DELETE FROM requestnotificationtodesc a USING requestnotificationtodesc b WHERE a.ctid < b.ctid AND a.notificationid = b.notificationid;


Example 2: Composite Primary Key
Warning

Error:


ERROR: duplicate key value violates unique constraint "adsadsyncobjattributes_pk"

PK columns = object_id, attrib_ldap_name

DELETE FROM adsadsyncobjattributes a USING adsadsyncobjattributes b WHERE a.ctid < b.ctid AND a.object_id = b.object_id AND a.attrib_ldap_name = b.attrib_ldap_name;

Alert

If an error message appears while executing the cleanup query, rollback the transaction immediately to prevent partial changes.

  • Check whether the error is due to a syntax mistake. If so, correct the query and re-execute it.

  • If the query still fails for reasons other than syntax, collect the complete pgsql folder and move the ticket to the G3-Sunil Team.


BEGIN;
-- run the duplicate cleanup query here

-- if error message occurs during execution
ROLLBACK;

Step 4: Identify Primary Key Columns for Other Tables

If the error involves a different table:

  1. Use the \d command in psql to describe the table:  \d <table_name>;

  2. Identify the primary key column(s) from the output.

    • If the table has a single-column PK, update the query with that column.

    • If the table has a composite PK, include all PK columns in the query.

Step 5: Post-Resolution Validation

Run the following maintenance queries on the database: (Note : Don't run below query within transaction)



IdeaREINDEX DATABASE SERVICEDESK;
VACUUM ANALYZE;
UPDATE GLOBALCONFIG SET PARAMVALUE = 'false' WHERE CATEGORY = 'BACKUPSCHEDULE' AND PARAMETER = 'IS_DB_CORRUPTED';

Confirm that above commands executed successfully.

  • If errors occur and the error mention index corruption, re-check the inconsistent table as per Steps 3–4, delete duplicate records, and re-run REINDEX and VACUUM.

  • If any other type of error occurs, collect the complete pgsql folder in debugging mode and move the ticket to G3-Sunil Team for corruption analysis and resolution.

Step 6: Final Validation Backup

After completing the cleanup and validation:

Run a pg_dump command from the pgsql/bin/ directory to ensure the database can be backed up successfully

./pg_dump -d servicedesk -U sdpadmin -h 127.0.0.1 -p <port_number> >nul
  1. Replace <port_number> with the actual PostgreSQL port configured in your environment.
  2. If the dump runs without errors, the corruption has been resolved successfully.
  3. If the dump fails, collect the complete pgsql folder and escalate the issue to the G3-Sunil Team.
Notes

Note

i)The provided queries are safe cleanup queries that retain one valid row while removing duplicates.
ii)Always execute the fix in a transaction block (BEGIN … COMMIT) to avoid accidental data loss.
iii)Ensure you are running the query on the correct table as indicated in the error message.



      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • VPAT Document for ServiceDesk Plus On-premises

                      A Voluntary Product Accessibility Template (VPAT) is a document that explains how a product's accessibility meet the revised section 508 and EN 301549 and EN 301549 standards based on the Web Content Accessibility Guidelines (WCAG) requirements. ...

                      Related Products