Date: Thu, 10 Apr 2025 13:13:27 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: gweatherby@uchc.edu, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18886: identity duplicate key
Message-ID: <Z_en1_3NGRauUKIP@depesz.com>
Reply-To: depesz@depesz.com
References: <18886-7a7ebc1b03f6ad9c@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <18886-7a7ebc1b03f6ad9c@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/Z_en1_3NGRauUKIP%40depesz.com>
Precedence: bulk

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