10 ms·

Postgres sequences can skip 32 unexpectedly

134 points by sjwhitworth 5y ago

combatentropy 5y ago
Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost. I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen. It is just an artifact of "sequences", the database object in Postgres that autogenerates the "next" ID number for a column --- automatically set up if you declare a column of type "serial", but that's all a serial column is. Serial just means: make the column of type integer, and create a sequence for it, and set the column's default value to nextval(sequence). You can mitigate gaps somewhat, like if you're testing and retesting a bunch of inserts, rolling back each time. Well, after a few tests, the next number in the sequence is far beyond the last number in the table. So you can call another sequence function, setval, to reset it. Something like: select setval('sequence_name', (select max(id) from table)); Further reading: https://www.postgresql.org/docs/current/functions-sequence.html https://www.postgresql.org/docs/current/functions-sequence.h...
- merb 5y ago
  btw. it's easy to use FOR UPDATE to create custom sequences in a table that do not have the batch reservation feature. it's just slower if you can afford that. the problem with resetting the sequence is that it would need a full blown serilizable transaction which is worse thatn just using for update and read commited.
  - combatentropy 5y ago
    Right. It isn't something I recommend running in production before every insert. It's just something I have done sometimes, like as part of a big database change, and only to "reference" tables. Like suppose there is a table of 10 rows to populate a dropdown menu. Now I need to add an 11th, but for some reason the sequence got out of wack, and the next sequence value is 15. So I manually set it back to 11.
- marcosdumay 5y ago
  At this point, you can just add a key to the column, set it to max(column) + 1 in an update, and forget about the sequence. Either way, it's better done in a column that isn't the primary key.
- CodesInChaos 5y ago
  That's what I'd run as a one time cleanup in the OP's place, to minimize the customer visible impact, since it avoids the gap for everybody who was affected by the skip but didn't have an incident since then.
bsder 5y ago
Isn't creating a sequence a bad idea in general, anyway? Aren't there a zillion ways to compromise things if you know that some field is a sequence?
- combatentropy 5y ago
  Do you mean enumeration, whereby an attacker starts at some ID and tries several in sequence? It has never been a problem for me and my applications. Just because you know a record exists, doesn't mean you can see it. For example, if you are authorized to view https://www.example.com/records/100 https://www.example.com/records/100, and you decide to try https://www.example.com/records/101 https://www.example.com/records/101, then the code will check to see if you're authorized to see record 101. If not, then you will get Unauthorized. I suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps.
  - bsder 5y ago
    > I suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps. Well, it's things like say, an invoice number. I can buy something from you. And then 7 days later I buy something else from you. If the invoice numbers are in sequence, I just gained quite a bit of information about how fast you are selling things. That's the kind of information leak that sequences can create.
    - wokkel 5y ago
      Nice idea, but please check your local tax office. At least here in the Netherlands, you are required to use a monotically increasing series for invoice numbers. So I wouldn't do this if I were you.
      - silon42 5y ago
        You can't stop anyone from doing what he describes if you allow customers to see the invoice number. IMO, your sequential sequence number should be for tax audit purposes only.
josep-panadero 5y ago
> Sequences felt like a good use case for this when we started, [...] We’ve since moved to a different approach which enforces the behaviour we want more explicitly when creating incidents, and we learned something along the way. The entire article shows that the author has a very good grasp as much of the technical side of development as of the business side. And the conclusion makes a lot of sense. Sequences are a technical solution for a technical problem, how to uniquely identify new elements in a fast and reliable manner. And it is optimized for such use case. But, in the real world, users have expectations and when their mental model conflicts with the inner working of an application you end creating confusing and lack of trust. Depending on the situation, to educate the user can be the path to follow, but here it seems reasonable to just adapt the inner workings to the mental model of the users. It's faster and scales easier as the number of customers increases.
- peterejhamilton 5y ago
  Author here - thanks for the kind words. You're right, this was a case of pragmatic decisions revisited at a later date, something I'm a big fan of (although it would have been nice to not fall foul of the issue at all!) We were very open, and our customers in this case were really understanding - just one of the reasons we love working with them!
devit 5y ago
The initial design was quite flawed, in addition to not using sequences they should not use one DB object per organization, but rather a single object with an "organization" field.
- wheybags 5y ago
  That's a little unfair, I don't think there's enough information in the article to come to a conclusion either way.
- scarmig 5y ago
  That's just a design choice: single tenancy vs multitenancy. I agree that multitenant databases tend to be more pleasant to work with as a developer of dependent services, but there are plenty of reasons (data isolation; resource allocation guarantees) one might want a single tenant db.
- peterejhamilton 5y ago
  Hey - author here! I'm not totally sure I follow on this one. Happy to chat more if there's any context missing from the article that you'd find interesting :)
- CodeWriter23 5y ago
  > but rather a single object with an "organization" field. In your opinion. There are advantages of sharding tables by tenant boundaries. Data isolation and query speed to name a couple.
grandinj 5y ago
Oracle does this too, so do some other databases. It's quite a natural optimisation.
agent327 5y ago
This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.
- advisedwang 5y ago
  You would run into this problem even if you aren't using the database number as your primary key. We don't want to force the user to generate the incident handle (slowing down incident creation) so a sequence of number is useful. Generating the sequence in application logic can make it challenging to guarantee we don't duplicate numbers if two requests come in at once (you don't want locks/global synchronous state in the application if you can avoid it), so having the database generate them is a good fit.
- peterejhamilton 5y ago
  Hey, author here :) Normally I'd agree with you, but in this case we're explicitly dealing with external IDs that we want to be incrementing. Our internal IDs and API IDs actually follow a totally different approach, and hold no meaning other than as a reference :)
- dragontamer 5y ago
  Customer IDs should have at least one or two checksum digits to help spotcheck for data entry errors anyway.
  - t0mas88 5y ago
    Indeed, if you skip this you're going to have a weird "bug" some time in the next years where a customer accidentally got the ID wrong and the system accepted it. Google Analytics either doesn't do this or got really unlucky. But some day I had to troubleshoot an issue where the data for a totally unrelated website ended up in someone's GA data set. Not just the usual spam, but millions of visits to the wrong website which had an account ID very similar to the client's.
- bluenose69 5y ago
codr7 5y ago
Yep, already walked that path with invoice numbers. The only thing you can say for sure about a sequence is the next number will be greater, which is not good enough for many kinds of identifiers.
- gmfawcett 5y ago
  You can't even say that for sure. Sequences can be exhausted, and can be configured to cycle (wrap around when max value is reached).
leftnode 5y ago
We have a similar system (multi-tenant database) where each tenant (account) has objects that have unique identifiers for that specific account (customers, locations, jobs, invoices, etc). Customer #C1010 may have 2 locations #L1899 and #L8443 and many invoices #IN1940 and #IN2399 for example. When we first built the system, I considered using native Postgres sequences to track these, but decided against them because of how they are affected during a rollback. In our system, each account has a record in a table that controls the next value of the sequence. We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. Sure, it requires locking the sequence record but it's a very small table and generating a sequence is quick. We wrapped everything up in a stored procedure named generate_sequence() which returns the next value of the sequence and increments it. It's scaled to millions of records quite well without issue.
- peterejhamilton 5y ago
  Nice - we're using a very similar approach now (procedure that runs just before creation) which I think will last us a good while. Glad to know it's worked out well for you :)
  - leftnode 5y ago
    Another added benefit is that you can build a simple interface to allow end users to adjust their sequences (or our support staff in this instance). In our system, by default, all objects start at 1000. If a new account is created, and they want to increase a sequence to some value (say they already have 5000 invoices in QuickBooks and they want to start all new invoices at 10000 so they know every invoice #IN10000 and higher was created in our system), we have a simple interface that one of our support staff can go to arbitrarily increase the next value.
- garblegarble 5y ago
  >We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. I assume that means you also have to only allow one TX to be in-flight at a time adding a new record whose ID is generated from a given sequence?
CodesInChaos 5y ago
> we don’t just want a monotonically increasing sequence Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions. One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach an out-of-order id would lead to missed events.
- lawrjone 5y ago
  Yep, this is particularly relevant when building pagination. If you use the primary key, which itself is sortable and based on a sequence, you might be surprised when you skip over rows that were yet to be committed because the IDs won't respect transaction commit order.
- peterejhamilton 5y ago
  Super interesting, and that makes a lot of sense! Luckily our _internal_ IDs don't rely on sequences at all, and for ordering I'd always use a field specifically for that purpose like `created_at` timestamps etc (vs inferring ordering from IDs). Best if IDs just remain references! This could have been another interesting bug though, in a way glad we hit this one instead and that it's now fixed so we don't hit this one in future!
  - wongarsu 5y ago
    Chances are that your created_at timestamps reflect transaction start, not transaction commit. That would leave you open to issues as described by GP as well
  - CodesInChaos 5y ago
    Timestamps have monotonicity issues as well: 1. If you create it in the application, clocks need to be synced well enough between all application servers. If you create it in the database, this shouldn't be an issue. 2. The transaction completes some time after the the timestamp was created, and that time can vary between concurrent transactions. This is a fundamental problem. The MAX + 1 approach should guarantee strict monotonicity, but might lead to scalability issues for highly contented counters.
CodesInChaos 5y ago
Another interesting cause of skipped sequence numbers in postgres is that INSERT ON CONFLICT increments the sequence number even if the row already exists. If the UPDATE is more common than the INSERT case, this will waste more values that it uses. This can be undesirable, even when the absence of gaps isn't strictly required.
adeel_siddiqui 5y ago
Nice write-up. Something does not add up here though. Primary writes 32 ahead to the WAL when fetched the first time, then keeps a counter (log_cnt) which it decreases each time nextval is called. So, when sequence was initialized, nextval is 1 and WAL has 32. The replica sees 32 as fetched offset. How does incident sequence switch from 7 to 39? Shouldn't it be 33 when the replica was made the primary? Same for incident 20 -> 52, shouldn't it be 33 when replica was made primary? Unless I am missing something here, i.e, 32 is added to the nextval and nextval is logged each time (20 or 7).
- rcthompson 5y ago
  Maybe it "tops up" the logged values whenever the database is idle?
dabinat 5y ago
The article was interesting but I was disappointed it didn’t go into more detail on what the final solution was.
CapriciousCptl 5y ago
Good writeup! It's an interesting gotcha because Postgres and SQLite docs expressly disclaim that their sequences/AUTOINCREMENT are gapless but experienced and talented programmers still use them as such. Is the type of thing that doesn't bite you until production. Postgres docs-- https://www.postgresql.org/docs/13/sql-createsequence.html https://www.postgresql.org/docs/13/sql-createsequence.html > Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.
- deleted 5y ago
  [deleted]
Merad 5y ago
I'm sure someone will correct me if I'm wrong, but isn't it simpler to manage this sort of functionality by putting a counter in a table and using a CTE to increment it along with the insert? Something like with u as ( update organizations set last_external_id = last_external_id + 1 where id = 123 returning last_external_id ) insert into incidents (organization_id, external_id, title) select 123, last_external_id, 'Blah blah blah' from u; The risk here of course is running into lock contention around the organization table there's a high volume of incident creation for the same organization, but considering the context (incident management) that seems pretty low risk.
- stubish 5y ago
  This approach depends on the level of transaction isolation being used, and relying on the higher levels is a foot gun best avoided unless you really know what you are doing and the consequences you are... erm... locking yourself into.
  - Merad 5y ago
    Can you elaborate? The Postgres documentation leads me to believe this would work fine with the default (READ COMMITTED) isolation level. If multiple transactions attempt to modify the same row (incrementing the counter) they're forced to line up until previous updates to that row have been committed or rolled back, hence the risk of lock contention, but each transaction should consistent view of the counter.
    - stubish 5y ago
      Yes, I think you are right. The update locks the row containing the last id, and concurrent transactions block if they too need to grab the lock. (so performance wise, a sequence is still better due to less locking, but if you can't have holes it seems fine)
anonu 5y ago
This was raised as a bug back in 2004, but the thread shows the contributors didn't consider it so: https://www.postgresql.org/message-id/20040419231413.EE480CF567F@www.postgresql.com https://www.postgresql.org/message-id/20040419231413.EE480CF...
juped 5y ago
Yeah, the main purpose is just distinctness. Allowing it to jump ahead helps it guarantee this.
8eye 5y ago
as someone who is somewhat anxious and apparently prone to hopefully temporarily hairless when stress hits the fan, what is the patch or best practices to avoid such failure? outside of using uuid for ids?
xupybd 5y ago
Why do customers notice this? Does the I'd have significance to their use case?
cafard 5y ago
Kevin Loney once gave an excellent talk, "Controlled Flight into Terrain", at an Oracle users group conference. As I recall, one of the cases he mentioned of self-inflicted damage involved the company that wanted strict, gapless order for an ID column. It would have been entirely possible to avoid ID conflicts by using the INCREMENT BY feature of Oracle sequences, but the company had European systems fetching the next ID value from a server in the US. I sympathize with customers who don't expect gaps of 20 (Oracle) or 32 in ID sequences. But the relational model is one of unordered tuples, isn't it?

134 points by sjwhitworth 5y ago

Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost. I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen. It is just an artifact of "sequences", the database object in Postgres that autogenerates the "next" ID number for a column --- automatically set up if you declare a column of type "serial", but that's all a serial column is. Serial just means: make the column of type integer, and create a sequence for it, and set the column's default value to nextval(sequence). You can mitigate gaps somewhat, like if you're testing and retesting a bunch of inserts, rolling back each time. Well, after a few tests, the next number in the sequence is far beyond the last number in the table. So you can call another sequence function, setval, to reset it. Something like: select setval('sequence_name', (select max(id) from table)); Further reading: https://www.postgresql.org/docs/current/functions-sequence.html https://www.postgresql.org/docs/current/functions-sequence.h...

merb 5y ago
btw. it's easy to use FOR UPDATE to create custom sequences in a table that do not have the batch reservation feature. it's just slower if you can afford that. the problem with resetting the sequence is that it would need a full blown serilizable transaction which is worse thatn just using for update and read commited.
- combatentropy 5y ago
  Right. It isn't something I recommend running in production before every insert. It's just something I have done sometimes, like as part of a big database change, and only to "reference" tables. Like suppose there is a table of 10 rows to populate a dropdown menu. Now I need to add an 11th, but for some reason the sequence got out of wack, and the next sequence value is 15. So I manually set it back to 11.
marcosdumay 5y ago
At this point, you can just add a key to the column, set it to max(column) + 1 in an update, and forget about the sequence. Either way, it's better done in a column that isn't the primary key.
CodesInChaos 5y ago
That's what I'd run as a one time cleanup in the OP's place, to minimize the customer visible impact, since it avoids the gap for everybody who was affected by the skip but didn't have an incident since then.

bsder 5y ago

Isn't creating a sequence a bad idea in general, anyway? Aren't there a zillion ways to compromise things if you know that some field is a sequence?

combatentropy 5y ago
Do you mean enumeration, whereby an attacker starts at some ID and tries several in sequence? It has never been a problem for me and my applications. Just because you know a record exists, doesn't mean you can see it. For example, if you are authorized to view https://www.example.com/records/100 https://www.example.com/records/100, and you decide to try https://www.example.com/records/101 https://www.example.com/records/101, then the code will check to see if you're authorized to see record 101. If not, then you will get Unauthorized. I suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps.
- bsder 5y ago
  > I suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps. Well, it's things like say, an invoice number. I can buy something from you. And then 7 days later I buy something else from you. If the invoice numbers are in sequence, I just gained quite a bit of information about how fast you are selling things. That's the kind of information leak that sequences can create.
  - wokkel 5y ago
    Nice idea, but please check your local tax office. At least here in the Netherlands, you are required to use a monotically increasing series for invoice numbers. So I wouldn't do this if I were you.
    - silon42 5y ago
      You can't stop anyone from doing what he describes if you allow customers to see the invoice number. IMO, your sequential sequence number should be for tax audit purposes only.

josep-panadero 5y ago

> Sequences felt like a good use case for this when we started, [...] We’ve since moved to a different approach which enforces the behaviour we want more explicitly when creating incidents, and we learned something along the way. The entire article shows that the author has a very good grasp as much of the technical side of development as of the business side. And the conclusion makes a lot of sense. Sequences are a technical solution for a technical problem, how to uniquely identify new elements in a fast and reliable manner. And it is optimized for such use case. But, in the real world, users have expectations and when their mental model conflicts with the inner working of an application you end creating confusing and lack of trust. Depending on the situation, to educate the user can be the path to follow, but here it seems reasonable to just adapt the inner workings to the mental model of the users. It's faster and scales easier as the number of customers increases.

peterejhamilton 5y ago
Author here - thanks for the kind words. You're right, this was a case of pragmatic decisions revisited at a later date, something I'm a big fan of (although it would have been nice to not fall foul of the issue at all!) We were very open, and our customers in this case were really understanding - just one of the reasons we love working with them!

devit 5y ago

The initial design was quite flawed, in addition to not using sequences they should not use one DB object per organization, but rather a single object with an "organization" field.

wheybags 5y ago
That's a little unfair, I don't think there's enough information in the article to come to a conclusion either way.
scarmig 5y ago
That's just a design choice: single tenancy vs multitenancy. I agree that multitenant databases tend to be more pleasant to work with as a developer of dependent services, but there are plenty of reasons (data isolation; resource allocation guarantees) one might want a single tenant db.
peterejhamilton 5y ago
Hey - author here! I'm not totally sure I follow on this one. Happy to chat more if there's any context missing from the article that you'd find interesting :)
CodeWriter23 5y ago
> but rather a single object with an "organization" field. In your opinion. There are advantages of sharding tables by tenant boundaries. Data isolation and query speed to name a couple.

grandinj 5y ago

Oracle does this too, so do some other databases. It's quite a natural optimisation.

agent327 5y ago

This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.

advisedwang 5y ago
You would run into this problem even if you aren't using the database number as your primary key. We don't want to force the user to generate the incident handle (slowing down incident creation) so a sequence of number is useful. Generating the sequence in application logic can make it challenging to guarantee we don't duplicate numbers if two requests come in at once (you don't want locks/global synchronous state in the application if you can avoid it), so having the database generate them is a good fit.
peterejhamilton 5y ago
Hey, author here :) Normally I'd agree with you, but in this case we're explicitly dealing with external IDs that we want to be incrementing. Our internal IDs and API IDs actually follow a totally different approach, and hold no meaning other than as a reference :)
dragontamer 5y ago
Customer IDs should have at least one or two checksum digits to help spotcheck for data entry errors anyway.
- t0mas88 5y ago
  Indeed, if you skip this you're going to have a weird "bug" some time in the next years where a customer accidentally got the ID wrong and the system accepted it. Google Analytics either doesn't do this or got really unlucky. But some day I had to troubleshoot an issue where the data for a totally unrelated website ended up in someone's GA data set. Not just the usual spam, but millions of visits to the wrong website which had an account ID very similar to the client's.
bluenose69 5y ago

codr7 5y ago

Yep, already walked that path with invoice numbers. The only thing you can say for sure about a sequence is the next number will be greater, which is not good enough for many kinds of identifiers.

gmfawcett 5y ago
You can't even say that for sure. Sequences can be exhausted, and can be configured to cycle (wrap around when max value is reached).

leftnode 5y ago

We have a similar system (multi-tenant database) where each tenant (account) has objects that have unique identifiers for that specific account (customers, locations, jobs, invoices, etc). Customer #C1010 may have 2 locations #L1899 and #L8443 and many invoices #IN1940 and #IN2399 for example. When we first built the system, I considered using native Postgres sequences to track these, but decided against them because of how they are affected during a rollback. In our system, each account has a record in a table that controls the next value of the sequence. We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. Sure, it requires locking the sequence record but it's a very small table and generating a sequence is quick. We wrapped everything up in a stored procedure named generate_sequence() which returns the next value of the sequence and increments it. It's scaled to millions of records quite well without issue.

peterejhamilton 5y ago
Nice - we're using a very similar approach now (procedure that runs just before creation) which I think will last us a good while. Glad to know it's worked out well for you :)
- leftnode 5y ago
  Another added benefit is that you can build a simple interface to allow end users to adjust their sequences (or our support staff in this instance). In our system, by default, all objects start at 1000. If a new account is created, and they want to increase a sequence to some value (say they already have 5000 invoices in QuickBooks and they want to start all new invoices at 10000 so they know every invoice #IN10000 and higher was created in our system), we have a simple interface that one of our support staff can go to arbitrarily increase the next value.
garblegarble 5y ago
>We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. I assume that means you also have to only allow one TX to be in-flight at a time adding a new record whose ID is generated from a given sequence?

CodesInChaos 5y ago

> we don’t just want a monotonically increasing sequence Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions. One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach an out-of-order id would lead to missed events.

lawrjone 5y ago
Yep, this is particularly relevant when building pagination. If you use the primary key, which itself is sortable and based on a sequence, you might be surprised when you skip over rows that were yet to be committed because the IDs won't respect transaction commit order.
peterejhamilton 5y ago
Super interesting, and that makes a lot of sense! Luckily our _internal_ IDs don't rely on sequences at all, and for ordering I'd always use a field specifically for that purpose like `created_at` timestamps etc (vs inferring ordering from IDs). Best if IDs just remain references! This could have been another interesting bug though, in a way glad we hit this one instead and that it's now fixed so we don't hit this one in future!
- wongarsu 5y ago
  Chances are that your created_at timestamps reflect transaction start, not transaction commit. That would leave you open to issues as described by GP as well
- CodesInChaos 5y ago
  Timestamps have monotonicity issues as well: 1. If you create it in the application, clocks need to be synced well enough between all application servers. If you create it in the database, this shouldn't be an issue. 2. The transaction completes some time after the the timestamp was created, and that time can vary between concurrent transactions. This is a fundamental problem. The MAX + 1 approach should guarantee strict monotonicity, but might lead to scalability issues for highly contented counters.

CodesInChaos 5y ago

Another interesting cause of skipped sequence numbers in postgres is that INSERT ON CONFLICT increments the sequence number even if the row already exists. If the UPDATE is more common than the INSERT case, this will waste more values that it uses. This can be undesirable, even when the absence of gaps isn't strictly required.

adeel_siddiqui 5y ago

Nice write-up. Something does not add up here though. Primary writes 32 ahead to the WAL when fetched the first time, then keeps a counter (log_cnt) which it decreases each time nextval is called. So, when sequence was initialized, nextval is 1 and WAL has 32. The replica sees 32 as fetched offset. How does incident sequence switch from 7 to 39? Shouldn't it be 33 when the replica was made the primary? Same for incident 20 -> 52, shouldn't it be 33 when replica was made primary? Unless I am missing something here, i.e, 32 is added to the nextval and nextval is logged each time (20 or 7).

rcthompson 5y ago
Maybe it "tops up" the logged values whenever the database is idle?

dabinat 5y ago

The article was interesting but I was disappointed it didn’t go into more detail on what the final solution was.

CapriciousCptl 5y ago

Good writeup! It's an interesting gotcha because Postgres and SQLite docs expressly disclaim that their sequences/AUTOINCREMENT are gapless but experienced and talented programmers still use them as such. Is the type of thing that doesn't bite you until production. Postgres docs-- https://www.postgresql.org/docs/13/sql-createsequence.html https://www.postgresql.org/docs/13/sql-createsequence.html > Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

deleted 5y ago
[deleted]

Merad 5y ago

I'm sure someone will correct me if I'm wrong, but isn't it simpler to manage this sort of functionality by putting a counter in a table and using a CTE to increment it along with the insert? Something like with u as ( update organizations set last_external_id = last_external_id + 1 where id = 123 returning last_external_id ) insert into incidents (organization_id, external_id, title) select 123, last_external_id, 'Blah blah blah' from u; The risk here of course is running into lock contention around the organization table there's a high volume of incident creation for the same organization, but considering the context (incident management) that seems pretty low risk.

stubish 5y ago
This approach depends on the level of transaction isolation being used, and relying on the higher levels is a foot gun best avoided unless you really know what you are doing and the consequences you are... erm... locking yourself into.
- Merad 5y ago
  Can you elaborate? The Postgres documentation leads me to believe this would work fine with the default (READ COMMITTED) isolation level. If multiple transactions attempt to modify the same row (incrementing the counter) they're forced to line up until previous updates to that row have been committed or rolled back, hence the risk of lock contention, but each transaction should consistent view of the counter.
  - stubish 5y ago
    Yes, I think you are right. The update locks the row containing the last id, and concurrent transactions block if they too need to grab the lock. (so performance wise, a sequence is still better due to less locking, but if you can't have holes it seems fine)

anonu 5y ago

This was raised as a bug back in 2004, but the thread shows the contributors didn't consider it so: https://www.postgresql.org/message-id/20040419231413.EE480CF567F@www.postgresql.com https://www.postgresql.org/message-id/20040419231413.EE480CF...

juped 5y ago

Yeah, the main purpose is just distinctness. Allowing it to jump ahead helps it guarantee this.

8eye 5y ago

as someone who is somewhat anxious and apparently prone to hopefully temporarily hairless when stress hits the fan, what is the patch or best practices to avoid such failure? outside of using uuid for ids?

xupybd 5y ago

Why do customers notice this? Does the I'd have significance to their use case?

cafard 5y ago

Kevin Loney once gave an excellent talk, "Controlled Flight into Terrain", at an Oracle users group conference. As I recall, one of the cases he mentioned of self-inflicted damage involved the company that wanted strict, gapless order for an ID column. It would have been entirely possible to avoid ID conflicts by using the INCREMENT BY feature of Oracle sequences, but the company had European systems fetching the next ID value from a server in the US. I sympathize with customers who don't expect gaps of 20 (Oracle) or 32 in ID sequences. But the relational model is one of unordered tuples, isn't it?