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@uchc.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