ConnectAll upgrade fails with PSQLException duplicate key constraint on properties_pkey

Products

• ConnectAll On-Prem
• ConnectALL

Issue/Introduction

You encounter a PSQLException during a ConnectAll upgrade. The error indicates a duplicate key value violates the unique constraint properties_pkey because a specific ID already exists. This happens when the PostgreSQL ID sequence for the properties table returns a value lower than the actual maximum ID present in the table

Environment

• ConnectAll upgrade
• PostgreSQL DB

Cause

The properties_id_seq sequence is out of sync with the data in the properties table. When the installer attempts to insert new records, the sequence generates an ID that is already in use, leading to a constraint violation

Resolution

To resolve this issue and synchronize the sequence with your table data, perform the following steps:

1. Log in to your PostgreSQL database.
2. Review this command before running it to reset the sequence to the current maximum ID:
   sql

   SELECT setval('properties_id_seq', (SELECT MAX(id) FROM properties));

3. Run the ConnectAll installer again. The upgrade should now proceed successfully

Pre-Upgrade Check

Before performing upgrades in other environments (such as Production or UAT), you should verify the sequence health by running these queries:

1. SELECT MAX(id) FROM properties;
2. SELECT last_value FROM properties_id_seq;

Ensure that the last_value from the sequence is greater than or equal to the MAX(id) from the table. If it is lower, apply the fix in step 2 of the Resolution section before starting the upgrade