Error "Unable to upgrade the database: java.lang.IllegalStateException: Exception encountered while Create app records for existing vApps" when upgrading VCD database from 10.x to 10.5 or later

Products

• VMware Cloud Director

Issue/Introduction

• VCD service is successfully upgraded, however database upgrade task '/opt/vmware/vcloud-director/bin/upgrade' fails with following error:
  
  Do you wish to upgrade the product now? [Y/N] y
  Examining database at URL: jdbc:postgresql://VCD_Database_IP:5432/vcloud?socketTimeout=90&ssl=true
  The next step in the upgrade process will change the VMware Cloud Director database schema.
  Backup your database now using the tools provided by your database vendor.
  Enter [Y] after the backup is complete. y
  Running 5 upgrade tasks
  Executing upgrade task:
  Successfully ran upgrade task
  Executing upgrade task:
  Successfully ran upgrade task
  Executing upgrade task:
  Successfully ran upgrade task
  Executing upgrade task:
  .............[13]
  Unable to upgrade the database: java.lang.IllegalStateException: Exception encountered while Create app records for existing vApps
  
  
• In /opt/vmware/vcloud-director/logs/upgrade-date-timestamp.log and vcloud-container-debug.log on the Cloud Director cell, you see entries similar to:
  
  Caused by: java.sql.BatchUpdateException: Batch entry 8 INSERT INTO app (id, app_id, app_template_id, name, description, app_type, version, vapp_entity_id, org_id) VALUES ('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid, 'TemplateName', 'Automatically copied CatalogItem
  SrcCatalog:SourceCatalogName
  SrcTemplate:SourceTemplateName
  TemplateDescription', 'VmApp', 'TemplateVersion', 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid, 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid) was aborted: ERROR: duplicate key value violates unique constraint "uq_app_name_version_org_id"
    Detail: Key (name, version, org_id)=(TemplateName, TemplateVersion, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) already exists.  Call getNextException to see other errors in the batch.
          at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:148)
          at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2184)
          at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:481)
          at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
          at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1567)
          at com.vmware.vcloud.upgrade.tasks.castor.CreateAppsForExistingVAppsTask.call(CreateAppsForExistingVAppsTask.java:108)
          ... 15 more
  Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uq_app_name_version_org_id"
    Detail: Key (name, version, org_id)=(TemplateName, TemplateVersion, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) already exists.
          at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)

Environment

VMware Cloud Director 10.x

Cause

This issue is caused by database constraint 'uq_app_name_version_org_id' that fails while trying to create entries for templates/vApps in 'app' table. 
'app' table is introduced in VCD 10.5.  During upgrade vApp/template objects are moved to app table and this task fails when there are more than one vApp/template with identical name.

Resolution

To resolve this issue, alter database table 'app' and drop the 'uq_app_name_version_org_id' constraint from VCD database.
Database modifications are required to unsubscribe a catalog from external feed in Cloud Director. To resolve this issue, please open a Support Request with Broadcom Technical Support and note this Article ID (385088) in the problem description. For more information, see Creating and managing Broadcom support cases.