ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

U00003590 uk_oh_name - ERROR: duplicate key value violates unique constraint

book

Article ID: 195144

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic Workload Automation - Automation Engine

Issue/Introduction

With the Automic Engine Utility 12.2.5 using Postgres DB.

When we unload and reload an existing object with option: -EREPLACE we  get the following error : 

20200610/113409.439 - U00021126 Client '100': Importing object 'xxxx.SCRI' of type 'SCRI'.
20200610/113409.444 - U00003590 UCUDB - DB error: 'ERROR:  duplicate key value violates unique constraint "uk_oh_name" DETAIL:  Key (oh_name, oh_client)=(xxxx.SCRI, 100) already exists.', '', '', ''

Even if we have specified option: -EREPLACE


Environment

Automic Engine  12.2.5+build.1579888229632  
Automic Engine '12.3.2+hf.1.build.1584364211340'

Resolution

This is expected behavior on POSTGRESQL DB's.


As this DB system is always rolling back the complete transaction on a duplicate key - all other supported DB vendors are only erroring the failed statement - and the utility can perform an UPDATE statement instead of the INSERT (within the same transaction) - this is not possible on PostgreSQL!


As the complete transaction is rolled back - the utility must start over again with SAVEPOINTS - this is how it works on PostgreSQL - we should not hide these messages as this might be important information - f.ex. load a huge transport case and during processing a duplicate key occurs - the huge transaction is rolled back and must start over again with SAVEPOINTS enabled - by doing this - the load process will slow down and with the information in the log, the user/customer knows what's going on and why it is not done yet...


We tested save points within the AE core and by enabling them all the time the system was not able the get any scalability and throughput - only in case of a DUPLICATE key error the transaction is re-run with savepoints to avoid performance loss.


As the AE core and UTILITIES share the same DB libraries it is expected to work the same - and a transport case must be loaded within one transaction to have consistency.