From: "Campbell, Lance" <lance@illinois.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: RE: Sequence Cycle question
Thread-Topic: Sequence Cycle question
Thread-Index: AdttuIX44wZyJi9+TnOL2E9ifFYCSQABYkAAAAElviA=
Date: Thu, 23 Jan 2025 18:46:52 +0000
Message-ID: 
 <SJ0PR11MB5629E584D93EF0BCF5F984FCDEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
References: 
 <SJ0PR11MB562940815F89B85D4FF64D25DEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
 <202380.1737654100@sss.pgh.pa.us>
In-Reply-To: <202380.1737654100@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?BZeuT2dspp/b1KEiZb4pWGKm7YhmYc0GAtPepfnDOD6prLP9bZKkoLGP3zw+?=
 =?us-ascii?Q?Pr4OjPEK/H00r50ZrS1r4A8RoiZXlJ8MHW0o8HemE4+TNAD7Po9EIFxKm+6G?=
 =?us-ascii?Q?RCLs32eSjGVpE1rK9xxS0ceHeK1/ipSjODzoXLzPhxo1p+hDWf3p5XUsH8Jz?=
 =?us-ascii?Q?xhUjZBOWhakEt7y1w4rY9ZTLU8kdtzfT3rJsHgZZkYj48dbohz5I3gyDcF3R?=
 =?us-ascii?Q?OhAtSlkkO9+ffyYDtMLxa1Few3Raow4x7l8+LhXBzaQus/z144jrJLXO+diZ?=
 =?us-ascii?Q?oM861ahMexr5QLw7RISGU48NLW32uAIiaicgs7O+DmJNfnYFGGwFZiwbc/Nt?=
 =?us-ascii?Q?gQdQeEHwEhXqLvtysk0OyAzW9gi7dyH1CNSqjdUlY9+U/OuiuedKOLGohG9u?=
 =?us-ascii?Q?VzeyulOjPwqP9Leo4lrhUGMOXwDljoPLbh91TNKDdtNTQ5zYh9LJo/9O70Zu?=
 =?us-ascii?Q?3dt4eeqBResuMSIXBdtrPKowKHCrluvfFlWKEYRG2p/8UAEmCslr/JsrMyUJ?=
 =?us-ascii?Q?I2JQ4wHOQNpTaG/+EBSEuG4ayjVcWx7Mzwh9KEK/lNBJQQ79+OTGz/TjQ2zI?=
 =?us-ascii?Q?KAqAN44B/xfWm8uuFn4hulX+dzshQKJq1xdpRhKt4ZPcoEfU3qEAfPcSrocH?=
 =?us-ascii?Q?/XRi2a87fY/cXRYHJwgu3Tiaf6ssAo2ny7LJKmTHakde8CrTxkmSlT0HtBC3?=
 =?us-ascii?Q?gabP1QGQ8oo9YsOGCpe2MmXTp/5imj3gRCHPwwBTi22nQ9qMR4kisTwFMcIS?=
 =?us-ascii?Q?ZTAv7AWpcPa6KlqrQvzF1T837wNZqH/ROQ109BidYIo/RtrsEFap3zG63w9g?=
 =?us-ascii?Q?gxxCW+QtEbNOji44eFbcH4n4Pj9tRky1t7IcrjsVzceE2oshE/pOIdHItXea?=
 =?us-ascii?Q?ZY8LUkQB2kdxmce1Op3gB655EqVd0BH+Re1ctyJuWn6lEox+lCWERytiGUE6?=
 =?us-ascii?Q?k/0VnAuEXGxHOJbXScw9OaHTpmCU5vvOh0QoR5JZl7+btF6hmtcIDp9QH+pQ?=
 =?us-ascii?Q?vyTi5WYDl+vOH1v7GppR+bJ/jUAdeoNgpSkGFLlFrWDAhjE7iHhR+korX489?=
 =?us-ascii?Q?HvIoDsS/GloYHtpxGUbHMn7pS7vBEWlKhHesTq6ZRKKQI5vweZywtFuHAA9s?=
 =?us-ascii?Q?tS6yaL4d2ENYDUJuPIvqR8UwCVerfKDGFtlyaHdag6fmteC39gU6W3kUF5mv?=
 =?us-ascii?Q?Al4DU8HT5tsJ+A0tOoOOnITiG4rE1Q+2ds4CqNt2yQ6tyIPDx9OFQv8x7q50?=
 =?us-ascii?Q?7C4KnjyiKePwy6dFWlP42SFZwnNZBPVyxx4X2T9b4/RiR4Avelzy6TZyHzDU?=
 =?us-ascii?Q?V6Vo49TAtt6+kzl7OMoGmte/ckk3XFocFWhrhEqB3xvtf3VXBlNEjlfdHXG6?=
 =?us-ascii?Q?kBQaDnBwHf7B5ISyDlr4P+puZUdG4sIKBxUaF/D+fO2CYjMyWQDkNGWiJLs6?=
 =?us-ascii?Q?ddLeimDou0Gkqxx4M9MSpgK7875kayTzfRGnboIl89Ens0TAjqsVixDwWVYj?=
 =?us-ascii?Q?pzhur6DT8WQqWk3jgWQIphUvbm/lZyVFQDy9kGaRjyIcPVp3wMHGMLBSWzPb?=
 =?us-ascii?Q?wLL4FWYQGnM60AUu+eFKdaVHuwiIq9PEoi7QWLVl?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: illinois.edu
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: SJ0PR11MB5629.namprd11.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 0d889df9-738f-46ce-4196-08dd3bde4da5
X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Jan 2025 18:46:52.7696
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 44467e6f-462c-4ea2-823f-7800de5434e3
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 LPjLS4+wCDp0m3Fb9zezQJG4jvLMnqfCj0gFzrd79maWpwL61gMl+bef3A9SnFuBhKRDZI41+0iv8P5oCiJOzA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH0PR11MB4918
X-Proofpoint-ORIG-GUID: -ETqUN_ldjZpJei3F9RdnS31-CsSSYVD
X-Proofpoint-GUID: -ETqUN_ldjZpJei3F9RdnS31-CsSSYVD
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1057,Hydra:6.0.680,FMLib:17.12.68.34
 definitions=2025-01-23_08,2025-01-23_01,2024-11-22_01
X-Spam-Details: rule=cautious_plus_nq_notspam policy=cautious_plus_nq score=0
 malwarescore=0 impostorscore=0 adultscore=0 clxscore=1011 mlxscore=0
 spamscore=0 bulkscore=0 mlxlogscore=999 suspectscore=0 phishscore=0
 priorityscore=1501 lowpriorityscore=0 classifier=spam adjust=0 reason=mlx
 scancount=1 engine=8.19.0-2411120000 definitions=main-2501230136
X-Spam-Score: 0
X-Spam-OrigSender: lance@illinois.edu
X-Spam-Bar: 
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/SJ0PR11MB5629E584D93EF0BCF5F984FCDEE02%40SJ0PR11MB5629.namprd11.prod.outlook.com>
Precedence: bulk

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

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 t=
hen do a "fresh" insert with no specified ID so the sequence would naturall=
y be incremented?

I hope that made sense.


Thanks,

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>=20
Sent: Thursday, January 23, 2025 11:42 AM
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Sequence Cycle question

"Campbell, Lance" <lance@illinois.edu> 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 violat=
ion.

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

			regards, tom lane