From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Campbell, Lance" <lance@illinois.edu>
cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Sequence Cycle question
In-reply-to: 
 <SJ0PR11MB562940815F89B85D4FF64D25DEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
References: 
 <SJ0PR11MB562940815F89B85D4FF64D25DEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
Comments: In-reply-to "Campbell, Lance" <lance@illinois.edu>
	message dated "Thu, 23 Jan 2025 17:13:05 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <202379.1737654100.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 23 Jan 2025 12:41:40 -0500
Message-ID: <202380.1737654100@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/202380.1737654100%40sss.pgh.pa.us>
Precedence: bulk

"Campbell, Lance" <lance@illinois.edu> writes:
> Table X has records that have been removed over time randomly.  There ar=
e 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