public inbox for [email protected]help / color / mirror / Atom feed
Sequence Cycle question 8+ messages / 5 participants [nested] [flat]
* Sequence Cycle question @ 2025-01-23 17:13 Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Campbell, Lance @ 2025-01-23 17:13 UTC (permalink / raw) To: pgsql-admin PostgreSQL 16 Question on how Cycle works with example: I have table X with a primary key ID which is an integer that uses a sequence. Sequence Settings: start_value=1 min_value=1 max_value=1,000,000 cycle=true Use Case: 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? Could you please add some text in the documentation to explain this Use Case? It seems very important. Thanks, Lance ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> @ 2025-01-23 17:41 ` Tom Lane <[email protected]> 2025-01-23 18:46 ` RE: Sequence Cycle question Campbell, Lance <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2025-01-23 17:41 UTC (permalink / raw) To: Campbell, Lance <[email protected]>; +Cc: pgsql-admin "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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> @ 2025-01-23 18:46 ` Campbell, Lance <[email protected]> 2025-01-23 18:58 ` Re: Sequence Cycle question David G. Johnston <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Campbell, Lance @ 2025-01-23 18:46 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> 2025-01-23 18:46 ` RE: Sequence Cycle question Campbell, Lance <[email protected]> @ 2025-01-23 18:58 ` David G. Johnston <[email protected]> 2025-01-23 19:16 ` RE: [EXT] Re: Sequence Cycle question Wong, Kam Fook (TR Technology) <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: David G. Johnston @ 2025-01-23 18:58 UTC (permalink / raw) To: Campbell, Lance <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-admin 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. ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [EXT] Re: Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> 2025-01-23 18:46 ` RE: Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 18:58 ` Re: Sequence Cycle question David G. Johnston <[email protected]> @ 2025-01-23 19:16 ` Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-23 19:24 ` RE: [EXT] Re: Sequence Cycle question Campbell, Lance <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Wong, Kam Fook (TR Technology) @ 2025-01-23 19:16 UTC (permalink / raw) To: David G. Johnston <[email protected]>; Campbell, Lance <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-admin 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 <[email protected]> Sent: Thursday, January 23, 2025 12:59 PM To: Campbell, Lance <[email protected]> Cc: Tom Lane <[email protected]>; [email protected] 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 <[email protected]<mailto:[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. ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [EXT] Re: Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> 2025-01-23 18:46 ` RE: Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 18:58 ` Re: Sequence Cycle question David G. Johnston <[email protected]> 2025-01-23 19:16 ` RE: [EXT] Re: Sequence Cycle question Wong, Kam Fook (TR Technology) <[email protected]> @ 2025-01-23 19:24 ` Campbell, Lance <[email protected]> 2025-01-23 19:37 ` RE: [EXT] Re: Sequence Cycle question Wong, Kam Fook (TR Technology) <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Campbell, Lance @ 2025-01-23 19:24 UTC (permalink / raw) To: Wong, Kam Fook (TR Technology) <[email protected]>; David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-admin That was just an example. I am reaching the max size of integers. From: Wong, Kam Fook (TR Technology) <[email protected]> Sent: Thursday, January 23, 2025 1:16 PM To: David G. Johnston <[email protected]>; Campbell, Lance <[email protected]> Cc: Tom Lane <[email protected]>; [email protected] 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 <[email protected]<mailto:[email protected]>> Sent: Thursday, January 23, 2025 12:59 PM To: Campbell, Lance <[email protected]<mailto:[email protected]>> Cc: Tom Lane <[email protected]<mailto:[email protected]>>; [email protected]<mailto:[email protected]> 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 <[email protected]<mailto:[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. ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [EXT] Re: Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> 2025-01-23 18:46 ` RE: Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 18:58 ` Re: Sequence Cycle question David G. Johnston <[email protected]> 2025-01-23 19:16 ` RE: [EXT] Re: Sequence Cycle question Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-23 19:24 ` RE: [EXT] Re: Sequence Cycle question Campbell, Lance <[email protected]> @ 2025-01-23 19:37 ` Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-23 20:22 ` Re: [EXT] Sequence Cycle question Scott Ribe <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Wong, Kam Fook (TR Technology) @ 2025-01-23 19:37 UTC (permalink / raw) To: Campbell, Lance <[email protected]>; David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-admin Lance, How about this? 1) Create a new column with bigint datatype (double the max limits of int). 2) Write a proc to copy the existing PK to this new column. 3) If #2 and #4 below have a big time gap, write a trigger to auto copy the existing PK to this new column for any new inserts/deletes/updates. 4) App deploy/changes to use the new PK/Bigint/column. 5) Then this will buy you time to consider other design options for PK. Thank you Kam From: Campbell, Lance <[email protected]> Sent: Thursday, January 23, 2025 1:24 PM To: Wong, Kam Fook (TR Technology) <[email protected]>; David G. Johnston <[email protected]> Cc: Tom Lane <[email protected]>; [email protected] Subject: RE: [EXT] Re: Sequence Cycle question That was just an example. I am reaching the max size of integers. From: Wong, Kam Fook (TR Technology) <[email protected]<mailto:[email protected]>> Sent: Thursday, January 23, 2025 1:16 PM To: David G. Johnston <[email protected]<mailto:[email protected]>>; Campbell, Lance <[email protected]<mailto:[email protected]>> Cc: Tom Lane <[email protected]<mailto:[email protected]>>; [email protected]<mailto:[email protected]> 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 <[email protected]<mailto:[email protected]>> Sent: Thursday, January 23, 2025 12:59 PM To: Campbell, Lance <[email protected]<mailto:[email protected]>> Cc: Tom Lane <[email protected]<mailto:[email protected]>>; [email protected]<mailto:[email protected]> 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 <[email protected]<mailto:[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. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [EXT] Sequence Cycle question 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Re: Sequence Cycle question Tom Lane <[email protected]> 2025-01-23 18:46 ` RE: Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 18:58 ` Re: Sequence Cycle question David G. Johnston <[email protected]> 2025-01-23 19:16 ` RE: [EXT] Re: Sequence Cycle question Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-23 19:24 ` RE: [EXT] Re: Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 19:37 ` RE: [EXT] Re: Sequence Cycle question Wong, Kam Fook (TR Technology) <[email protected]> @ 2025-01-23 20:22 ` Scott Ribe <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Scott Ribe @ 2025-01-23 20:22 UTC (permalink / raw) To: Pgsql-admin <[email protected]> > On Jan 23, 2025, at 12:37 PM, Wong, Kam Fook (TR Technology) <[email protected]> wrote: > > 1) Create a new column with bigint datatype (double the max limits of int). Double the width, ~4,000,000,000 times max limit > ... > 5) Then this will buy you time to consider other design options for PK. Like, until the sun burns out and engulfs the earth ;-) -- Scott Ribe [email protected] https://www.linkedin.com/in/scottribe/ ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-01-23 20:22 UTC | newest] Thread overview: 8+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-23 17:13 Sequence Cycle question Campbell, Lance <[email protected]> 2025-01-23 17:41 ` Tom Lane <[email protected]> 2025-01-23 18:46 ` Campbell, Lance <[email protected]> 2025-01-23 18:58 ` David G. Johnston <[email protected]> 2025-01-23 19:16 ` Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-23 19:24 ` Campbell, Lance <[email protected]> 2025-01-23 19:37 ` Wong, Kam Fook (TR Technology) <[email protected]> 2025-01-23 20:22 ` Scott Ribe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox