public inbox for [email protected]  

help / color / mirror / Atom feed

From: Campbell, Lance <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Sequence Cycle question
Date: Thu, 23 Jan 2025 18:46:52 +0000
Message-ID: <SJ0PR11MB5629E584D93EF0BCF5F984FCDEE02@SJ0PR11MB5629.namprd11.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <SJ0PR11MB562940815F89B85D4FF64D25DEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
	<[email protected]>

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?

I hope that made sense.


Thanks,

-----Original Message-----
From: Tom Lane <[email protected]> 
Sent: Thursday, January 23, 2025 11:42 AM
To: Campbell, Lance <[email protected]>
Cc: [email protected]
Subject: Re: Sequence Cycle question

"Campbell, Lance" <[email protected]> writes:
> Table X has records that have been removed over time randomly.  There are IDs that cover a wide range of values between 1 and 1,000,000.
> When the primary key ID, which is a sequence, reaches 1,000,000 then the next sequence value will start back at 1.
> What would happen if I had a primary key for ID of 5 still in use?  When I reach 5 will the sequence skip that number and go to 6 instead?

No, the sequence has no idea about what is in the table.  It will generate "5" when it's time to, and then your insert will get a duplicate-key violation.

You could work around that by retrying the insert, but it might be better to reconsider whether you want a cycling sequence for this application.

			regards, tom lane

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: RE: Sequence Cycle question
  In-Reply-To: <SJ0PR11MB5629E584D93EF0BCF5F984FCDEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox