5 ms·

In Postgres, sequences are guaranteed to be monotonically increasing even in the presence of failed transactions. So retrieving an index using nextval()[2] will

In Postgres, sequences are guaranteed to be monotonically increasing even in the presence of failed transactions. So retrieving an index using nextval()[2] will never collide[1]. [1] A malicious pedant could create situations where it wraps around or collides. [2] http://www.postgresql.org/docs/current/static/functions-sequence.html http://www.postgresql.org/docs/current/static/functions-sequ...

chriswarbo 10y ago
> In Postgres, sequences are guaranteed to be monotonically increasing even in the presence of failed transactions. Well, I was talking about what happens in case of failure; not the chances of a failure to begin with. If, hypothetically, Postgres forgot to update a sequence number, then a collision would be guaranteed, since that's a property of sequential numbering: all updates are contending for the same "next" number, and must be managed in some way. Random numbers spread across a large range, so work even without management. Even if we ignore the possibility of bugs in Postgres, or cosmic rays flipping bits in RAM (which may have a similar probability to a random ID scheme colliding), etc. that's still only true inside the confines of Postgres. Once data starts working its way through layer after layer of shell scripts, caches, proxies, etc. there's a lot of scope for things to go awry. Plus, since we're talking about longevity, there's no guarantee that we'll still be using Postgres in 20 years' time, or maybe we need to migrate or integrate with some other system, etc. I'm reminded of the "one in a billion" chances that used to be claimed for DNA evidence in court. It is certainly true that the probability of two non-twins having identical DNA is that low, but that doesn't really mean much; even if the probability of identical DNA were zero, that doesn't tell us anything about the collison rate of the sub-set of bases which were used, or the error rate of the sampling process, or the mislabelling rate of the lab, or the corruption rate of the evidence handlers, etc. :)

chriswarbo 10y ago

> In Postgres, sequences are guaranteed to be monotonically increasing even in the presence of failed transactions. Well, I was talking about what happens in case of failure; not the chances of a failure to begin with. If, hypothetically, Postgres forgot to update a sequence number, then a collision would be guaranteed, since that's a property of sequential numbering: all updates are contending for the same "next" number, and must be managed in some way. Random numbers spread across a large range, so work even without management. Even if we ignore the possibility of bugs in Postgres, or cosmic rays flipping bits in RAM (which may have a similar probability to a random ID scheme colliding), etc. that's still only true inside the confines of Postgres. Once data starts working its way through layer after layer of shell scripts, caches, proxies, etc. there's a lot of scope for things to go awry. Plus, since we're talking about longevity, there's no guarantee that we'll still be using Postgres in 20 years' time, or maybe we need to migrate or integrate with some other system, etc. I'm reminded of the "one in a billion" chances that used to be claimed for DNA evidence in court. It is certainly true that the probability of two non-twins having identical DNA is that low, but that doesn't really mean much; even if the probability of identical DNA were zero, that doesn't tell us anything about the collison rate of the sub-set of bases which were used, or the error rate of the sampling process, or the mislabelling rate of the lab, or the corruption rate of the evidence handlers, etc. :)