Re: BUG #18886: identity duplicate key

On Wed, Apr 09, 2025 at 11:04:20PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18886
> Logged by: Gerard Weatherby
> Email address: gweatherby(at)uchc(dot)edu
> PostgreSQL version: 17.2
> Operating system: Ubuntu 24
> Description:
>
> I observed this:
>
> registry=# SELECT last_value, is_called FROM performance.vm_load_id_seq;
> last_value | is_called
> ------------+-----------
> 12935994 | t
> (1 row)
>
> registry=# \d performance.vm_load
> Table "performance.vm_load"
> Column | Type | Collation | Nullable |
> Default
> -----------+--------------------------+-----------+----------+------------------------------
> id | integer | | not null | generated
> always as identity
> vm_id | integer | | not null |
> load15 | double precision | | not null |
> idle | double precision | | not null |
> stat_time | timestamp with time zone | | not null |
> Indexes:
> "vm_load_pk" PRIMARY KEY, btree (id)
> "vm_load_id_vm_id_stat_time_index" btree (id, vm_id, stat_time)
> "vm_load_stat_time_index" btree (stat_time)
> Foreign-key constraints:
> "vm_load_virtual_machines_id_fk" FOREIGN KEY (vm_id) REFERENCES
> performance.virtual_machines(id) ON UPDATE RESTRICT ON DELETE RESTRICT
>
> registry=# INSERT INTO performance.vm_load(vm_id,load15,idle,stat_time)
> values(437,0.0,94.31128026560856,'2025-04-09T22:25:26.326639+00:00'::timestamptz)
> ;
> ERROR: duplicate key value violates unique constraint "vm_load_pk"
> DETAIL: Key (id)=(1314208) already exists.
>
> Unable to figure out what was going on, I did this, and it seemed to fix the
> issue:

You must have inserted some rows specifying value for id column. In such
case identity/sequence is not updated, so afterwards you will get
duplicate errors, as sequence returns (as new/next) value that already
exists in table.

Solution:

select setval('performance.vm_load_id_seq'::regclass, max(id) + 1 ) from performance.vm_load;

and find whatever was doing these inserts, and fix it.

Best regards,

depesz