PostgreSQL database 23505 is a common error which can be seen along with the error message "duplicate key violates unique constraint".
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related database queries.
In this context, we shall look into methods to resolve this PostgreSQL error.
Sometimes we may get the following message when trying to insert data into a PostgreSQL database:
ERROR: duplicate key violates unique constraint
This happens when the primary key sequence in the table we're working on becomes out of sync. And this might likely be because of a mass import process.
Here we have to manually reset the primary key index after restoring it from a dump file.
To check whether the values are out of sync, we can run the following commands:
SELECT MAX(the_primary_key) FROM the_table;
If the first value is higher than the second value, our sequence is out of sync.
We can back up our PG database and then run the following command:
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);
This will set the sequence to the next available value that's higher than any existing primary key in the sequence.
When vpxd process crashes randomly after upgrading to vCenter Server 6.5 with the following error:
ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";
Panic: Unrecoverable database error. Shutting down VC
In the vpxd.log file, we can see entries similar to the one given below:
error vpxd[7F8DD228C700] [Originator@6876 sub=InvtVmDb opID=HB-host-476@72123-38e1cc31] >[VpxdInvtVm::SaveGuestNetworkAndDiskToDb] Failed to insert guest disk info for VM id = 976because of database error: "ODBC error: >(23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";
–> Error while executing the query” is returned when executing SQL statement “INSERT INTO VPX_GUEST_DISK (VM_ID, PATH, CAPACITY, >FREE_SPACE) VALUES (?, ?, ?, ?)”
And in the postgresql.log file, you see entries similar to the one given below:
VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk"
VCDB vc DETAIL: Key (vm_id, path)=(976, /tmp) already exists.
vCenter Services can be given a service restart. If the starting fails to initialize the service and shows the same crash reason, we can fix this by removing the impacted guest disk entry from vCenter Server Database. This information is safe to remove as it will be re-populated from the host.
1. First, take a snapshot of the vCenter Server machine before proceeding.
2. Then connect the vCenter Database.
3. And identify the guest disk entry using the following query:
select FROM vc.vpx_guest_disk where vm_id=<vm id> and path='/tmp';For example:select FROM vc.vpx_guest_disk where vm_id='976' and path='/tmp';
4. For deleting the duplicate value we can use the following command:
delete FROM vc.vpx_guest_disk where vm_id=<vm id> and path='/tmp';
select FROM vc.vpx_guest_disk where vm_id='976' and path='/tmp';
We can get the VM id from the vpxd.log error entry
5. Finally start the Service.
We can delete the snapshot after observing the stability of the vCenter Server.
This article covers methods to fix PostgreSQL Error code 23505 for our customers. This issue happens when you are trying to insert a value in a column that already exists there. If you have a sequencer on the field (like with a serial column type), it is likely out of sync with the table because you have done an insert and supplied a value (rather than letting the "default" mechanism handle it by getting the nextval from the sequencer). If this is the case, reset the sequencer with a setval statement to the max value of the field.