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.EE480CF...
Postgres sequences can skip 32 unexpectedly
71–80 of 81 posts
Re: Postgres sequences can skip 32 unexpectedly
#72I'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, 'B…
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.
Re: Postgres sequences can skip 32 unexpectedly
#73Yeah, the main purpose is just distinctness. Allowing it to jump ahead helps it guarantee this.
Re: Postgres sequences can skip 32 unexpectedly
#74Earlier quoted context omitted.
It can still leak information, like if a companies customers have sequential id's and your id is 590 then it is reasonable to assume that the company has had around 500-600 customers. It is usually not something to worry about, but in some cases you want to avoid leaking that info.
Or Jira tickets. When you submit two support cases in a few days and the number increased by 20 while the company is still a startup, you are pretty sure to have no decent answer.
So is that a bug or a feature ;)
Re: Postgres sequences can skip 32 unexpectedly
#75as 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?
Re: Postgres sequences can skip 32 unexpectedly
#76I'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, 'B…
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.
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.
Re: Postgres sequences can skip 32 unexpectedly
#77Why do customers notice this? Does the I'd have significance to their use case?
Re: Postgres sequences can skip 32 unexpectedly
#78Earlier quoted context omitted.
Nothing stops you from using the "...or more series" part to generate numbers that are specific to the day, the hour or, if you feel like it, the minute. Today's first invoice could be 2021-07-16-001, the second one 2021-07-16-002, etc. If you really don't want people to be able to guess your invoice volume from numbers alone, there are various ways to do that while still being compliant to EU laws.
I think e.g. 2021-07-16-123 followed by 2021-07-17-001 wouldn't fall under "sequential number" though because, well, they're not sequential. The Italian authorities seem to see this the same way - https://vatdesk.eu/en/eu-vat-news/italy-mandatory-mentions-o... (Annoyingly the directive doesn't give a definition for "a sequential number" itself.)
They are sequential for all intents and purposes, they are simply using two separate series within the same number – you are perfectly able to determine the order of the invoices.
I do agree that (as is so often the case) the directive is not specific enough to determine whether the Italian interpretation is correct or not, though I can provide some context from the German side as provided by the ministry of finance: "Eine lückenlose Abfolge der ausgestellten Rechnungsnummern ist nicht zwingend"[1] (~ "it is not required for the invoice numbers to be gapless"), which directly contradicts the Italians as far as I understand it.
[1] https://www.bundesfinanzministerium.de/Content/DE/Downloads/... - p. 522, 14.5 (10) 4
Re: Postgres sequences can skip 32 unexpectedly
#79Kevin 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?
Re: Postgres sequences can skip 32 unexpectedly
#80Earlier quoted context omitted.
I think e.g. 2021-07-16-123 followed by 2021-07-17-001 wouldn't fall under "sequential number" though because, well, they're not sequential. The Italian authorities seem to see this the same way - https://vatdesk.eu/en/eu-vat-news/italy-mandatory-mentions-o... (Annoyingly the directive doesn't give a definition for "a sequential number" itself.)
They are sequential for all intents and purposes, they are simply using two separate series within the same number – you are perfectly able to determine the order of the invoices. I do agree that (as is so often the case) the directive is not specific enough to determine whether the Italian interpretation is correct or not, though I can provide some context from the German side as provided by the ministry of finance:…
> you are perfectly able to determine the order of the invoices.
But you can't determine if there are any gaps which is what the Italian ruling seemed to be concerned with (as best I could follow the Google translation which was pretty bad) and the Germans aren't bothered about.
I think if it were me, I'd ere on the side of caution and have them be sequential (ordered, gapless).