Postgres Duplicate key error while upgrading


we are upgrading from 9.7 (build 61563) to 9.9 LTS.

When we start Sonar, it is triggering the database migrations. It fails almost instantly on:

2023.04.07 09:28:26 INFO web[DbMigrations] #6700 ‘Move live measure variations to values’…

Caused by: java.sql.BatchUpdateException: Batch entry 93 update live_measures set value = 1.0 where uuid = 'AXySSMtyBvGib7J0z-Sf' was aborted: ERROR: duplicate key value violates unique constraint "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AXxf8kadBvGib7J0p7N_, AXZSQNFc-Er--PeCmTqi) already exists.  Call getNextException to see other errors in the batch.
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "live_measures_component"

How can we solve this issue?

Fixed it by deleting duplicates:

     live_measures a
         USING live_measures b
     a.updated_at < b.updated_at
     AND (a.component_uuid = b.component_uuid AND a.metric_uuid = b.metric_uuid);
1 Like