RE: [EXT] Re: Sequence Cycle question

["Campbell, Lance" <lance@xxxxxxxxxxxx>]

That was just an example. I am reaching the max size of integers.

 

From: Wong, Kam Fook (TR Technology) <kamfook.wong@xxxxxxxxxxxxxxxxxx>
Sent: Thursday, January 23, 2025 1:16 PM
To: David G. Johnston <david.g.johnston@xxxxxxxxx>; Campbell, Lance <lance@xxxxxxxxxxxx>
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxx
Subject: RE: [EXT] Re: Sequence Cycle question

 

Lance,

 

Why can’t you change the max_value to 2,000,000 or higher?  I can’t think of a reason if this will cause any other problems, including performance.  Other please correct otherwise. 

Thank you

Kam

 

 

From: David G. Johnston <david.g.johnston@xxxxxxxxx>
Sent: Thursday, January 23, 2025 12:59 PM
To: Campbell, Lance <lance@xxxxxxxxxxxx>
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxx
Subject: [EXT] Re: Sequence Cycle question

 

External Email: Use caution with links and attachments.

 

On Thu, Jan 23, 2025 at 11:47 AM Campbell, Lance <lance@xxxxxxxxxxxx> wrote:

Lets say I was to set the cycle=true.  Once the IDs start back at 1, lets say we get to an ID of 5 where there is a duplicate. 

Is there a trigger or something else, that I could associate with the table that would "catch/detect" the insert error.  If an error occurs it would then do a "fresh" insert with no specified ID so the sequence would naturally be incremented?

 

You are really fighting against the design of the system here.  I suggest you avoid doing inserts to this table concurrently and put logic in the insertion code to simply find what would be the next identifier and use it.  Sequences are meant to be used for performance and simplicity - your requirements are incompatible with both.

 

The better option if you can manage it is to increase your identifier space to bigint and forget about wrap-around.  Re-using identifiers is simply not a good practice.

 

David J.