# PostgreSQL Sequence Recovery Controls

Seven small native controls on PostgreSQL 16.15, captured twice on fresh private
clusters on one host. These are designed software observations, not fourteen
independent samples, a crash test, or an incidence estimate.

Start with summary.json and the complete SQL/output in results-1.json and
results-2.json. The row-copy control compares all rows but then rejects a new
omitted-ID insert with SQLSTATE 23505. This is constraint enforcement, not silent
data loss. The fixture did not run pg_dump or logical replication.

## Verify and reproduce

All Python code uses only the standard library. From the extracted directory:

    sha256sum -c SHA256SUMS
    python3 -B verify_record.py results-1.json results-2.json
    python3 -B -m unittest -v test_probe.py test_record.py
    python3 -B -O -m unittest -v test_probe.py test_record.py
    python3 -B probe.py --bindir /path/to/postgresql/16/bin --output replay.json
    python3 -B verify_record.py results-1.json results-2.json replay.json

Use --client-bin for a separate psql installation and --temporary-root for an
approved scratch directory. Run as an unprivileged account with about 0.2 GiB
temporary capacity. The probe accepts no DSN or existing database directory:
it creates a private cluster with TCP disabled, fsync and synchronous_commit on,
timeouts and checksums, then stops and removes only that cluster. It refuses
an existing output filename. Unexpected outcomes require investigation, not
rewriting expectations.

Recorded version: PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1), x86-64,
GCC 13.3.0. Both captures retain executable hashes, settings, SQL and stdout.
The source explanation uses upstream revision
7d3e000c5961a544302072058a1184e9a588837b, not a byte-identical Ubuntu rebuild.

The original captures and probe are unchanged. Publication strengthens the
verifier to compare every result-bearing SQL output, including ordered cached
allocations, with the reported cases. Hashes establish byte identity, not the
scientific truth of a claim. No new license grant is made; upstream documents
are linked, not redistributed.

## Keep isolation and allocation separate

Ports and Grittner's PVLDB 2012 paper, Serializable Snapshot Isolation in
PostgreSQL, explains read-write dependency checks, safe snapshots and deferrable
read-only transactions (sections 3 and 4). Its evaluation compares PostgreSQL 9.1
concurrency-control modes using SIBENCH, modified DBT-2 and RUBiS (section 8).
It does not establish sequence restoration or reproduce the seven controls here.
The current synthesis is an interpretation: a row-isolation guarantee is not
an inventory of allocator state. PostgreSQL 16's explicit sequence exception
supplies that contract; stronger isolation does not turn nextval/setval into
ordinary rollbackable row updates.

Paper: https://www.vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf
Contract: https://www.postgresql.org/docs/16/transaction-iso.html
All other primary sources and exact scopes are in sources.json.

## Limits

One build and host, ascending noncycling sequences, ordered sessions and small
constructed tables. No power loss, network failure, live replica, full restore,
benchmark, external identity consumer or online repair was tested. Aligning to
max(id) was an isolated control, not a production recommendation. Recovery must
also consider direction, bounds, cycling, outstanding reservations and already
externalized identifiers.

Narrative: https://hesela.com/analyses/backup-verification-is-not-a-restore-test/#postgresql-sequence-recovery
Definition: https://hesela.dev/terms/postgresql-sequence/