What if I do the insert that gets ID 4 in a transaction that doesn’t commit for several minutes? What ID should inserts in other transactions taking place at the same time get? What if the transaction where I inserted ID 4 eventually rolls back?
“Max ID” is not a straightforward concept once MVCC and transactions are involved. See the notes in the Postgres docs for CREATE SEQUENCE for additional thoughts. Big takeaway:
Because
nextvalandsetvalcalls 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.
(emphasis mine)
