SDDC 9.x inventory fails to load, "ERROR: duplicate key value violates unique constraint "certificate_expiry_resource_id_key"

Products

• VCF Operations/Automation (formerly VMware Aria Suite)
• VMware Cloud Foundation

Issue/Introduction

SDDC Manager: /var/log/vmware/vcf/operationsmanager/operationsmanager.log

2026-05-19T12:12:19.222+0000 WARN  [vcf_om,6a0b73a0ff73389a79837462394a3ced,0846] [o.h.e.jdbc.spi.SqlExceptionHelper,om-exec-1] SQL Error: 0, SQLState: null
2026-05-19T12:12:19.222+0000 ERROR [vcf_om,6a0b73a0ff73389a79837462394a3ced,0846] [o.h.e.jdbc.spi.SqlExceptionHelper,om-exec-1] HikariPool-1 - Connection is not available, request timed out after 30000ms (total=10, active=10, idle=0, waiting=1)
2026-05-19T12:12:19.222+0000 DEBUG [vcf_om,6a0b73a0ff73389a79837462394a3ced,0846] [c.v.v.p.v.u.ValidateCredentialsTranslationTaskExecutor,om-exec-1] Exception occurred during validate credentials translation task : Unable to acquire JDBC Connection [HikariPool-1 - Connection is not available, request timed out after 30000ms (total=10, active=10, idle=0, waiting=1)] [n/a]
org.springframework.dao.DataAccessResourceFailureException: Unable to acquire JDBC Connection [HikariPool-1 - Connection is not available, request timed out after 30000ms (total=10, active=10, idle=0, waiting=1)] [n/a]
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:263)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:230)

SDDC Manager: /var/log/vmware/vcf/operationsmanager/operationsmanager.log

operationsmanager.2026-05-19.1.log.gz:2026-05-19T07:00:03.840+0000 ERROR [vcf_om,6a0c0a737fd1436460805121ea29a513,7e9b] [c.v.v.c.s.o.i.CertificateOperationOrchestratorImpl,om-exec-30] Exception: could not execute statement [ERROR: duplicate key value violates unique constraint "certificate_expiry_resource_id_key"
operationsmanager.2026-05-19.1.log.gz:2026-05-19T07:00:06.930+0000 ERROR [vcf_om,6a0c0a761ba69e1129e1ffb8fb2081a7,acde] [o.h.e.jdbc.spi.SqlExceptionHelper,om-exec-9] ERROR: duplicate key value violates unique constraint "certificate_expiry_resource_id_key"
operationsmanager.2026-05-19.1.log.gz:2026-05-19T07:00:06.931+0000 ERROR [vcf_om,6a0c0a761ba69e1129e1ffb8fb2081a7,acde] [c.v.v.c.s.o.i.CertificateOperationOrchestratorImpl,om-exec-9] Exception: could not execute statement [ERROR: duplicate key value violates unique constraint "certificate_expiry_resource_id_key"
operationsmanager.2026-05-19.1.log.gz:2026-05-19T08:09:45.107+0000 ERROR [vcf_om,6a0c1ac8a6a48d6a017983d6ba5be187,d973] [o.h.e.jdbc.spi.SqlExceptionHelper,om-exec-15] ERROR: duplicate key value violates unique constraint "certificate_expiry_resource_id_key"

Environment

VMware Cloud Foundation 9.x

Cause

Concurrent "View Certificates" requests for workload domains sharing the same NSX instance can trigger parallel INSERT operations for the same NSX resource. This results in duplicate key constraint violations on certificate_expiry_resource_id_key.

Resolution

1. Take a snapshot of the SDDC Manager appliance
2. SSH to the SDDC Manager as vcf user and then elevate to root with su.

3.  Run the following command:

   /usr/bin/psql -U postgres -h localhost -d operationsmanager << 'EOF'
CREATE TABLE IF NOT EXISTS certificatemanagement.certificate_expiry_backup AS 
SELECT * FROM certificatemanagement.certificate_expiry;

   CREATE OR REPLACE FUNCTION certificatemanagement.prevent_duplicate_cert_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.resource_type = 'nsxt_manager' THEN
        PERFORM pg_advisory_xact_lock(hashtext(NEW.resource_id));
        IF EXISTS (SELECT 1 FROM certificatemanagement.certificate_expiry 
                   WHERE resource_id = NEW.resource_id) THEN
            RETURN NULL;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

   DROP TRIGGER IF EXISTS prevent_dup_cert_trigger ON certificatemanagement.certificate_expiry;
CREATE TRIGGER prevent_dup_cert_trigger
    BEFORE INSERT ON certificatemanagement.certificate_expiry
    FOR EACH ROW
    EXECUTE FUNCTION certificatemanagement.prevent_duplicate_cert_insert();

   SELECT tgname, tgenabled::text AS status FROM pg_trigger WHERE tgname = 'prevent_dup_cert_trigger';
EOF

4. Validate the Workaround
   
   

   /usr/bin/psql -U postgres -h localhost -d operationsmanager -c "SELECT tgname, tgenabled::text AS status FROM pg_trigger WHERE tgname = 'prevent_dup_cert_trigger';"

   Expected:
          tgname          | status
--------------------------+--------
 prevent_dup_cert_trigger | O
(1 row)

   
   Confirm function exists in correct schema

   /usr/bin/psql -U postgres -h localhost -d operationsmanager -c "SELECT proname, pronamespace::regnamespace AS schema FROM pg_proc WHERE proname = 'prevent_duplicate_cert_insert';"

   Expected:

               proname            |        schema
-------------------------------+-----------------------
 prevent_duplicate_cert_insert | certificatemanagement
(1 row)