public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Campbell, Lance <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Sequence Cycle question
Date: Thu, 23 Jan 2025 11:58:53 -0700
Message-ID: <CAKFQuwYugYNtWdOb4xuHa=ePY5JOb-nJYUoOVfykD8BTKVfX+w@mail.gmail.com> (raw)
In-Reply-To: <SJ0PR11MB5629E584D93EF0BCF5F984FCDEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
References: <SJ0PR11MB562940815F89B85D4FF64D25DEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>
	<[email protected]>
	<SJ0PR11MB5629E584D93EF0BCF5F984FCDEE02@SJ0PR11MB5629.namprd11.prod.outlook.com>

On Thu, Jan 23, 2025 at 11:47 AM Campbell, Lance <[email protected]> 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.

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], [email protected]
  Subject: Re: Sequence Cycle question
  In-Reply-To: <CAKFQuwYugYNtWdOb4xuHa=ePY5JOb-nJYUoOVfykD8BTKVfX+w@mail.gmail.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