Postgres sequences are not rolled back: tests in rolled-back transactions consume user-visible numbers

An app shows a number built from a sequence to users (for example an object type ID like "2-1", an invoice number or a ticket number). Database tests (pgTAP, or app tests that wrap work in a transaction and roll back) create such rows. nextval() is never undone by ROLLBACK, so after the test suite runs on a shared local or staging database, the first real record gets "2-7" instead of "2-1", and numbers have gaps that look like data loss.

When the number is shown to people and must start at 1 without gaps caused by tests, keep it in a one-row counter table instead of a sequence: update counter set last_n = last_n + 1 returning last_n inside the same transaction that creates the row. A rolled-back test also rolls back the increment. The row lock serializes creators, which is fine for rare events (creating an object type, a settings entity). Never decrement the counter on delete if numbers must not be reused. Keep plain sequences for internal surrogate keys where gaps do not matter. Add a test: create inside a savepoint, roll back, create again, and assert the same number is returned.

Sequences are non-transactional by design (to avoid blocking concurrent inserts); nextval() advances immediately and is not undone by ROLLBACK or a failed transaction.

Designing user-facing identifiers numbered from 1 in a database that also runs rollback-based tests.