ROLLBACK Does Not Undo a PostgreSQL Sequence Reseed

A maintenance transaction ends with ROLLBACK, the edited business row has returned to its earlier value, and the wrapper reports that rollback completed. That is a tempting success signal. It is not enough to conclude that a pre-existing PostgreSQL sequence was restored. The PostgreSQL 18 sequence-functions documentation is explicit: sequence-state changes made by setval are immediately visible to other transactions and are not undone when the calling transaction rolls back. Ordinary row changes and sequence state therefore need separate evidence.

The operator decision is not “did ROLLBACK run?” It is whether the reseed procedure, abort behavior, next allocation, and ownership boundary have all been demonstrated well enough to approve recovery. This playbook uses a disposable owned database, psql -X, ordinary committed schema-qualified sequences, ascending increments of one, NO CYCLE, and CACHE 1. PostgreSQL 18 is the fixed documentation baseline, not a claim about an installed patch. The SQL examples below have not been executed for this article. Every result is an expectation derived from the documented behavior, not an observed pass.

The eight cases deliberately separate row rollback, setval, is_called, nextval, transactional ALTER SEQUENCE ... RESTART, a consumed allocation, cross-session visibility, and a blocked concurrent allocator. The deliverable is a reviewed decision: ACCEPT only a tested and authorized procedure with allocator ownership controlled; REPAIR a procedure that assumes setval rolls back; RERUN when session or blocker evidence is missing; and HOLD when outstanding allocations or a safe forward boundary remain unknown.

Name the state a rollback is supposed to restore

Before touching a sequence, write down what “restore” means. A maintenance ticket can accidentally collapse three different states into one phrase: the contents of ordinary rows, the sequence generator’s state, and identifiers already returned to callers. PostgreSQL does not give those states identical rollback semantics. nextval advances and returns a sequence value atomically, but values obtained by nextval are not reclaimed when the calling transaction aborts; setval changes also survive rollback.

That means a reseed review needs at least three separate questions. First, did the row that maintenance changed return to its exact pre-maintenance content? Second, what sequence state exists after the transaction boundary, including last_value and is_called at the observation point? Third, which identifiers have actually been handed to sessions or external consumers, regardless of whether a corresponding row eventually committed? A correct answer to one does not answer the others.

The neighboring Refonte article on sequence allocation versus commit order uses sequence nonrollback to examine an incremental-reader completeness problem. This article keeps only the shared database fact and applies it to a different operational question: whether a deliberate reseed and its recovery path may be approved. It does not design a watermark, lookback, or closed-prefix reader.

Define the maintenance promise in values, not adjectives. For this fixture, the row contract is id=1, quantity=7; the allocator contract names a particular sequence and the first value that a subsequent nextval is expected to return; the concurrency contract names which session may allocate while maintenance is open. A saved last_value is evidence about one observation, not authorization to reuse that number later.

The review should also distinguish a diagnostic pass from a maintenance approval. Q2 is supposed to demonstrate that setval persists through rollback. If it does, the diagnostic has passed while the candidate procedure that relied on rollback has failed its safety premise. “Test passed” is therefore not a useful final verdict unless the record also says what proposition the test proved.

Create and commit the disposable sequence fixture

The fixture must isolate the behavior under review. Use a database that is explicitly disposable and owned for this test, one known owner role, and a fresh working directory. psql -X is significant because PostgreSQL documents -X/--no-psqlrc as preventing system and user startup files from being read; that removes local psqlrc customization from the command path. The script enables verbose error reporting and sets ON_ERROR_STOP, for which noninteractive psql stops processing on an error and returns exit code 3; other documented nonzero statuses cover fatal client errors or lost server connections.

Capture environment identity rather than filling it in from the documentation selector. The first query asks the server for version(), current_database(), current_user, and pg_backend_pid(), then records the transaction isolation level. PostgreSQL documents pg_backend_pid() as the process identifier of the backend attached to the current session. A future run should separately retain psql --version, the process exit status, SQLSTATE for server-reported failures, and the complete transcript.

All seven sequences are ordinary objects created in the same committed schema. PostgreSQL documents sequence ownership as belonging to the user issuing CREATE SEQUENCE; the fixture uses that owner so a permission error does not hide the state behavior being tested. Each sequence starts at 101, increments by one, uses NO CYCLE, and fixes CACHE 1. With a cache of one, only one value is generated at a time; larger caches introduce preallocation and cross-session effects that this first laboratory intentionally excludes. Exclude other allocators from these fixture sequences throughout the run.

Save the following as reseed_lab.sql in UTF-8 without a byte-order mark, using LF line endings and exactly one final newline. The SHA-256 for those bytes is 39678a3859bfdf9729eb24bd0d689b7e83450544c5e1182930ce9c40eb77f01a.

\set ON_ERROR_STOP on
\set AUTOCOMMIT on
\set VERBOSITY verbose
\pset pager off
SELECT version(), current_database(), current_user, pg_backend_pid();
SHOW transaction_isolation;
-- Disposable database only. An existing schema stops the run; no DROP.
BEGIN;
CREATE SCHEMA refonte_reseed_lab;
CREATE TABLE refonte_reseed_lab.control (
 id integer PRIMARY KEY, quantity integer NOT NULL
);
INSERT INTO refonte_reseed_lab.control VALUES (1,7);
CREATE SEQUENCE refonte_reseed_lab.s2
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
CREATE SEQUENCE refonte_reseed_lab.s3
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
CREATE SEQUENCE refonte_reseed_lab.s4
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
CREATE SEQUENCE refonte_reseed_lab.s5
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
CREATE SEQUENCE refonte_reseed_lab.s6
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
CREATE SEQUENCE refonte_reseed_lab.s7
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
CREATE SEQUENCE refonte_reseed_lab.s8
 START 101 INCREMENT 1 CACHE 1 NO CYCLE;
COMMIT;
\echo Q1 row-only rollback
BEGIN;
UPDATE refonte_reseed_lab.control SET quantity=9 WHERE id=1;
ROLLBACK;
DO $$ BEGIN
 IF (SELECT quantity FROM refonte_reseed_lab.control WHERE id=1)
    IS DISTINCT FROM 7 THEN
  RAISE EXCEPTION 'Q1 failed';
 END IF;
END $$;
\echo Q2 setval with implicit is_called=true survives rollback
BEGIN;
UPDATE refonte_reseed_lab.control SET quantity=9 WHERE id=1;
SELECT setval('refonte_reseed_lab.s2',500);
ROLLBACK;
SELECT last_value, is_called FROM refonte_reseed_lab.s2;
DO $$ BEGIN
 IF (SELECT quantity FROM refonte_reseed_lab.control WHERE id=1)
    IS DISTINCT FROM 7 THEN
  RAISE EXCEPTION 'Q2 row rollback failed';
 END IF;
 IF nextval('refonte_reseed_lab.s2') <> 501 THEN
  RAISE EXCEPTION 'Q2 allocation failed';
 END IF;
END $$;
\echo Q3 setval with is_called=false survives rollback
BEGIN;
SELECT setval('refonte_reseed_lab.s3',500,false);
ROLLBACK;
SELECT last_value, is_called FROM refonte_reseed_lab.s3;
DO $$ BEGIN
 IF nextval('refonte_reseed_lab.s3') <> 500 THEN
  RAISE EXCEPTION 'Q3 failed';
 END IF;
END $$;
\echo Q4 transactional restart rollback
BEGIN;
ALTER SEQUENCE refonte_reseed_lab.s4 RESTART WITH 500;
ROLLBACK;
DO $$ BEGIN
 IF nextval('refonte_reseed_lab.s4') <> 101 THEN
  RAISE EXCEPTION 'Q4 failed';
 END IF;
END $$;
\echo Q5 transactional restart commit
BEGIN;
ALTER SEQUENCE refonte_reseed_lab.s5 RESTART WITH 500;
COMMIT;
DO $$ BEGIN
 IF nextval('refonte_reseed_lab.s5') <> 500 THEN
  RAISE EXCEPTION 'Q5 failed';
 END IF;
END $$;
\echo Q6 ordinary allocation is consumed despite rollback
BEGIN;
SELECT nextval('refonte_reseed_lab.s6') AS expected_101;
ROLLBACK;
DO $$ BEGIN
 IF nextval('refonte_reseed_lab.s6') <> 102 THEN
  RAISE EXCEPTION 'Q6 failed';
 END IF;
END $$;
\echo EXPECTATIONS_CONFIRMED_Q1_TO_Q6_ONLY_IF_THIS_LINE_IS_ACTUALLY_EMITTED

-- Q7/Q8 use separate coordinated sessions described below.

Run it only from the directory containing the source, with LAB_DSN already pointing to the approved disposable database:

psql -X --echo-all --dbname="$LAB_DSN" --file=reseed_lab.sql \
 > reseed-single-session.log 2>&1

Record the psql exit status immediately after this command, before another shell command replaces it. A nonzero status is a failed or incomplete evidence package, not a completed run. Preserve the log before doing anything to the fixture. The final \echo is useful only if it is actually emitted in a real transcript; its presence in source code is not evidence that Q1–Q6 executed.

Keep object creation outside the measured transaction

The setup transaction commits the schema, the control row, and every sequence before Q1 begins. That ordering is essential. Rolling back CREATE SEQUENCE would test the lifecycle of an object created inside the aborted transaction. The maintenance problem here is different: an operator mutates a sequence that already existed and had been committed before maintenance started.

The schema creation is intentionally not idempotent. PostgreSQL will report an error if the named schema already exists, and ON_ERROR_STOP should stop the scripted run. Do not add DROP SCHEMA, IF NOT EXISTS, or cleanup-before-setup behavior to make reruns convenient. A pre-existing name means the fixture's history is no longer independently known. Preserve the evidence, move to a fresh disposable database or fresh approved fixture name, and start again.

That discipline also protects the meaning of 101. Q4 and Q6 depend on a sequence that has never allocated its first value before the measured case. If a prior attempt already called nextval, merely restoring visible data would not reconstruct the same starting state because sequence allocations are not rolled back.

Prove the ordinary row rollback control

Q1 is the negative control that keeps the later explanation honest. The committed baseline contains one exact row: refonte_reseed_lab.control(id=1, quantity=7). The measured transaction changes that row to quantity 9 and then explicitly rolls the transaction back. The following DO block raises an exception unless the same row again has quantity 7.

That assertion matters because Q2 will mix an ordinary row update with a sequence mutation. Without Q1, an unexpected row result could be misattributed to sequence behavior, a connection mistake, or a script problem. With Q1, the intended baseline proposition is narrow and testable: in this fixture, that ordinary update is expected to disappear after ROLLBACK.

Do not reduce the evidence to “the script kept going.” A future run should retain the UPDATE command status, the rollback status, any server error, and the assertion outcome. An UPDATE 0 followed by a successful rollback would not prove that the intended row was changed and restored. The fixture's primary key and exact predicate make the identity explicit, but the transcript still needs to show that the measured commands ran against the expected database and role.

Q1 also prevents an incorrect explanation of Q2. PostgreSQL's documented setval behavior does not imply an implicit commit of the row update around it. The sequence manual says the setval sequence-state change is not undone by rollback; the ordinary row update remains governed by the surrounding transaction. The expected mixed outcome in Q2 is therefore “row restored, sequence mutation retained,” not “everything before setval committed.”

If Q1 fails, stop interpreting subsequent sequence cases. Preserve the failed transcript and any supplied SQLSTATE, record the exact server/client versions and backend identity, and classify the run for RERUN after the cause is understood. Do not edit the row back to 7 and continue. That would create a different fixture history and erase the very evidence needed to diagnose the baseline failure.

A passing Q1 remains deliberately modest. It validates one ordinary row rollback in this synthetic environment. It does not certify an application's exception handling, connection-pool behavior, or every kind of database object. Its job is to make the sequence-specific contrasts falsifiable.

Roll back a transaction containing setval

Q2 is the central failure test. It starts from fresh committed s2, changes the control row from 7 to 9, calls two-argument setval('refonte_reseed_lab.s2',500), and then rolls the transaction back. PostgreSQL documents that the two-argument form sets last_value to the supplied number and sets is_called=true; with an increment of one, the next nextval advances before returning a value. PostgreSQL also documents that a setval state change is immediately visible to other transactions and is not undone by rollback.

The expected post-rollback account is therefore intentionally asymmetric. The control row should again be quantity 7. Before the verification allocation, SELECT last_value, is_called FROM refonte_reseed_lab.s2 is expected to show 500 and true. The one nextval evaluated inside the assertion is expected to return 501. Those three facts answer three separate questions: row state, allocator state, and first subsequent allocation.

That is why engine-specific rollback boundaries are relevant only as a comparison in review discipline, not as a technical explanation for this PostgreSQL case. The MySQL article investigates implicit transaction boundaries around DDL. Q2 must show the opposite pattern for the ordinary PostgreSQL row: the row rolls back while the setval effect remains.

If the future run matches those expectations, the right verdict is not simply “Q2 passed.” It is: the diagnostic confirmed that the candidate procedure's assumption is unsafe. A maintenance wrapper can honestly issue ROLLBACK and still leave the pre-existing sequence at the state established by setval. The procedure therefore needs REPAIR before approval.

Do not attempt to repair the demonstration by restoring s2 in place. The Q2 assertion itself intentionally consumes the next allocation. Once that happens, the fixture is no longer at the post-rollback checkpoint it was testing. Preserve the state evidence and move forward in the scripted case sequence; use a fresh sequence or fresh fixture for any revised hypothesis.

Read the next value rather than guessing from a command status

The value returned by setval is only its second argument; PostgreSQL says so explicitly. It is not a promise that the same number will be returned by the next nextval. With two arguments, is_called becomes true, so this lab expects 501 next. With three arguments and false, Q3 expects 500 next.

Observation order therefore belongs in the evidence ledger. In Q2 the direct last_value, is_called read occurs after rollback but before the assertion's nextval. The assertion then mutates the sequence by allocating 501. A later query of last_value would describe a different moment. Treating nextval as an inert inspection is a common way to turn a correct test into an off-by-one story.

Similarly, a ROLLBACK command tag does not enumerate every category of state that has been restored. The operator must read the row oracle, sequence-state fields, and next allocation independently. If any required observation is missing, the result is RERUN, not a guess based on the command status.

Change is_called and test the next allocation

Q3 isolates the is_called boundary by using a fresh sequence and the three-argument form setval('refonte_reseed_lab.s3',500,false). PostgreSQL documents that when the third argument is false, the next nextval returns exactly the specified value and sequence advancement begins with the following call. The rollback still does not undo the setval sequence-state change.

The expected direct state after rollback is therefore last_value=500, is_called=false, and the assertion's single nextval is expected to return 500. Q2 and Q3 differ by one Boolean input, but that input changes the first value handed to a caller. The maintenance requirement “reseed to 500” is ambiguous unless the procedure says whether 500 is already considered called or is the next value that may be issued.

These values are expectations derived from the PostgreSQL 18 contract and the fixed unit increment; they are not captured results. The future transcript must replace expectation with actual output.

There is another subtlety: after Q3's verification call successfully returns 500, the sequence has now been called. The visible numeric value can remain 500 while the calling state has changed. A reviewer who records only “last value 500” can miss that the first 500 allocation has already happened. That is precisely why last_value, is_called, and the next allocation are distinct evidence fields.

Do not run nextval twice merely to get a prettier transcript. The first call is the tested allocation. A second call would be expected to return 501 and would change the fixture again. If a future evidence standard requires a printed row rather than an assertion-only result, create and checksum a separately reviewed script revision that captures the allocation once; do not silently modify this source after the run.

Finally, correctness of the off-by-one arithmetic does not imply that 500 is safe to reuse in a real system. setval(...,false) can precisely produce an unauthorized duplicate boundary. Sequence mechanics answer what will be issued; ownership and issued-value inventory decide whether issuing it is acceptable.

Compare transactional RESTART with setval

Q4 and Q5 test ALTER SEQUENCE ... RESTART WITH 500 on separate fresh sequences. PostgreSQL 18 documents a deliberate contrast: RESTART changes the current value so the restart value is returned by the next nextval, is transactional, and blocks concurrent transactions from obtaining numbers from the same sequence. The documentation explicitly contrasts that behavior with setval.

Q4 begins with s4 committed at its untouched start state of 101. Inside a transaction it executes ALTER SEQUENCE refonte_reseed_lab.s4 RESTART WITH 500, then rolls back. The assertion expects the first allocation after rollback to be 101. This is a sequence restart rollback test: if the command executed and the transaction aborts as scheduled, the expected next value comes from the pre-restart state.

Q5 is the positive control. Fresh s5 receives the same restart, but the transaction commits. Its assertion expects 500. That case is important because an unchanged Q4 result by itself could otherwise be explained by a command that never succeeded, a wrong object, or insufficient permissions. The pair makes the intended contrast explicit: rolled-back RESTART versus committed RESTART under the same simple sequence settings.

This does not turn RESTART into a universal production reseed prescription. Transactionality changes the failure model, but the safe number still depends on outstanding allocations and ownership. Blocking also creates an operational obligation: the maintenance transaction can hold allocators waiting, so the procedure needs time bounds, identifiable sessions, an observer, and an intentional release gate. Q8 exists to verify that concurrency evidence rather than infer it from Q4's final number.

Permissions are part of the rehearsal as well. PostgreSQL requires ownership of a sequence to use ALTER SEQUENCE. The lab deliberately uses the same owner in order to keep permission failure from masking the command semantics; a production maintenance role must still be tested with its actual authorization model.

Keep RESTART distinct from other sequence alterations

Do not generalize the documented RESTART properties to every ALTER SEQUENCE option. The same statement family can change increment, minimum and maximum values, caching, cycle behavior, ownership, and the recorded start value. PostgreSQL specifically says START WITH changes the recorded start value but has no effect on the current sequence value, while RESTART WITH changes the current value used for subsequent allocation.

Likewise, PostgreSQL notes that other backends with preallocated cached values may continue using those values before noticing altered generation parameters such as CACHE. That is one reason the lab fixes CACHE 1 and one reason a production review cannot project this fixture onto cached sessions without an inventory.

Keep the approval language narrow: Q4/Q5 test the rollback and commit behavior of RESTART WITH on fresh, unit-increment, noncycling, cache-one sequences. They do not establish that every ALTER SEQUENCE clause has the same rollback, visibility, or blocking implications.

Retain the consumed-allocation negative control

Q6 contains no reseed at all. It exists to prove that an allocated value can be consumed even when the transaction that obtained it rolls back. Fresh s6 starts at 101. The transaction calls nextval, labels the returned column expected_101, then rolls back. The following assertion expects the next allocation to be 102. PostgreSQL documents exactly this nonreclamation rule for nextval: the obtained value is not reclaimed for reuse if the calling transaction later aborts.

The alias is not an assertion. PostgreSQL will print whatever value the function actually returns under the name expected_101. A real evidence review must read the numeric result. The later DO block does assert that the next allocation is 102, but a successful block does not itself print 102 as a result row. Preserve both forms of evidence accurately rather than converting the source labels into fictional observed output.

The purpose of Q6 is narrow. A gap does not, by itself, demonstrate that a committed row was lost, and rollback does not make the number 101 available again. That is enough for this reseed review. Reconstructing reader completeness from identifier gaps belongs to the separate watermark problem and is intentionally excluded here.

Q6 also demonstrates why failed diagnostics should not be “cleaned up” by reseeding the same sequence and trying again. A verification statement may already have consumed a number before the error became visible to the operator. An attempted reset changes history again. Preserve the failure, identify what executed, and rerun the case with a fresh committed sequence if the evidence is ambiguous.

This is not a gapless-numbering recipe. PostgreSQL explicitly warns that sequence objects are not suitable for gapless assignment because nextval and setval calls are not rolled back. The operational conclusion here is only that issued and committed identifiers are different populations and must not be conflated during recovery.

Observe setval from a second session before rollback

Q7 moves the setval test across a session boundary. Use three independent psql -X connections to the same disposable database and already committed schema. Session A is the operator, B is the allocator, and O is reserved for the later observer role. Use the same owner in this lab so permission differences do not obscure the behavior. Record each backend PID before beginning; PostgreSQL's session-information functions expose pg_backend_pid() specifically for the current session identity.

Execute the following actions in order. Wait for each numbered action to finish before beginning the next; do not replace the handoff with sleeps.

-- 1. A
\set ON_ERROR_STOP on
\set AUTOCOMMIT on
\set VERBOSITY verbose
\pset pager off
SET application_name='refonte-reseed-A';
SELECT current_database(), current_user, pg_backend_pid() AS a_pid;
BEGIN;
SELECT setval('refonte_reseed_lab.s7',700,false);
-- A remains open.
-- 2. B (separate session, same database)
\set ON_ERROR_STOP on
\set AUTOCOMMIT on
\set VERBOSITY verbose
\pset pager off
SET application_name='refonte-reseed-B';
SELECT current_database(), current_user, pg_backend_pid() AS b_pid;
SELECT nextval('refonte_reseed_lab.s7') AS expected_700;
-- 3. A, only after B has returned its allocation
ROLLBACK;
-- 4. B
SELECT nextval('refonte_reseed_lab.s7') AS expected_701;

The expected Q7 conclusion follows from the documented setval visibility rule and the chosen false calling state: B should be able to obtain 700 while A's transaction remains open, then A rolls back, and B's next allocation should be 701. The aliases expected_700 and expected_701 are labels, not assertions; actual values must be retained from the B transcript.

This schedule adds evidence that Q3 alone cannot provide. Q3 shows what the same script expects after a rollback; Q7 asks whether another backend can act on the changed allocator before that rollback. PostgreSQL says setval changes are immediately visible to other transactions, so a correct future run should make that visibility concrete.

Preserve A and B transcripts separately with their PIDs and application names. If either connection is replaced, the new PID must be recorded and the case should be treated as a new coordinated run. A result with the right numbers but uncertain session identity is incomplete evidence.

Do not treat another session’s allocation as reversible

Once B has successfully received 700, A's later ROLLBACK cannot turn that earlier return into an event that never occurred. PostgreSQL's sequence documentation advises committing the transaction containing nextval before using its value for persistent purposes outside the database. A cluster crash before commit can leave the recovered sequence state uncertain. The operational point is that an identifier can cross the transaction boundary into another system's knowledge.

The lab does not simulate an external API, queue, or payment processor, and it must not pretend to. The proposed schedule illustrates why a recovery plan needs an issued-value inventory. A row table can be back at its baseline while another session has already learned a number. Database rollback is not a recall mechanism for information already handed to another actor.

If B's first allocation is missing, blocks unexpectedly, or cannot be attributed to the recorded B PID, classify Q7 RERUN. Do not infer success from A's eventual rollback or from a later value alone.

Capture a blocked allocator during RESTART

Q8 tests the concurrency property that distinguishes transactional RESTART from setval. Initialize O before starting the timed schedule, and keep the recorded A and B connections from Q7. A then executes ALTER SEQUENCE ... RESTART WITH 800 and leaves the transaction open. B then starts nextval with bounded lock and statement timeouts. O, the read-only observer, must capture that the recorded B PID is blocked by the recorded A PID before A is released. PostgreSQL documents that RESTART blocks concurrent transactions from obtaining values from that sequence, and separately notes that ALTER SEQUENCE blocks concurrent sequence function calls.

-- Before Q8: initialize O in its own connection.
\set ON_ERROR_STOP on
\set AUTOCOMMIT on
\set VERBOSITY verbose
\pset pager off
SET application_name='refonte-reseed-O';
SET default_transaction_read_only=on;
SELECT current_database(), current_user, pg_backend_pid() AS o_pid;
-- Use the same recorded A and B connections from Q7.
-- 1. A
BEGIN;
ALTER SEQUENCE refonte_reseed_lab.s8 RESTART WITH 800;
-- 2. B, while A remains open
SET lock_timeout='25s';
SET statement_timeout='30s';
SELECT nextval('refonte_reseed_lab.s8') AS expected_101_after_a_rollback;
-- B waits here; continue in O while that statement is pending.
-- 3. O, read-only coordination
SELECT pid, application_name, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database()
  AND application_name IN ('refonte-reseed-A','refonte-reseed-B')
ORDER BY application_name;
-- Repeat this read until recorded B is blocked by recorded A.
-- 4. A, only after the blocker relationship has been retained
ROLLBACK;
-- 5. B: retain its completed result, then reset the session limits.
RESET lock_timeout;
RESET statement_timeout;

pg_blocking_pids(pid) is the decisive relationship query here: PostgreSQL defines it as returning the PIDs of sessions blocking the specified server process from acquiring a lock. pg_stat_activity supplies each backend's PID, application name, state, and wait information. PostgreSQL cautions that state and wait-event fields are independent and can show ephemeral discrepancies, so the protocol keys approval on the blocker PID relationship rather than a hard-coded wait-event string.

Keep O out of a long transaction while repeating the observation. PostgreSQL's monitoring documentation notes that activity information requested inside a transaction can remain fixed for that transaction's snapshot; repeated autocommit reads avoid mistaking an old view for current coordination evidence.

Only after the blocker row has been retained should A roll back. B is then expected to complete with 101, the untouched pre-restart first value of fresh s8. If B times out or fails before returning its post-rollback allocation, invalidate the case even if O already captured the blocker. Retain the error, roll back A if it is still open, and RERUN Q8 with a fresh committed sequence. A final 101 without the observed blocker relationship is also incomplete: the required proof is both “B waited behind A” and “rollback restored the pre-restart allocation state.”

The lock lesson must remain object-specific. PostgreSQL's explicit locking documentation describes table lock modes and lock monitoring, but a lock on an application table is not proof that every direct nextval caller is excluded. The relevant coordination here is the sequence operation's documented blocking behavior.

Refonte's article on session and transaction ownership is useful as process context because it requires ownership and lifetime to be proved rather than inferred. Q8 does not recreate its advisory-lock experiment, and an advisory lock would not protect against a caller that does not participate in that convention.

Reconcile row state, sequence state and issued values

After Q1–Q8, reconcile evidence in a ledger that keeps different state classes separate. Each case should identify the source checksum, actual server and client versions, database and role, backend PIDs, statement order, transaction outcome, exact row content where relevant, direct sequence-state observation where relevant, every nextval result, SQLSTATE for server-reported errors, client diagnostics where applicable, and the disposition. Mark each field as expected, observed, assertion-derived, missing, or divergent. Do not convert an expected value into “observed” because the script contains that literal.

The table above is an expectation register, not a results table. An eventual run must attach the actual transcript locations to each row. A passing DO block establishes only the Boolean condition encoded in that block; it does not retroactively print every intermediate state. Conversely, a direct SELECT last_value, is_called is a point-in-time observation and does not prove that no other allocation occurred immediately afterward.

A cached value can outlive your inspection

CACHE 1 is a deliberate laboratory restriction, not a general production assumption. PostgreSQL explains that with cache sizes greater than one, sessions preallocate ranges; last_value can reflect a value reserved but not yet returned, values unused when a session ends are lost, and another session can continue returning previously cached values before noticing a later setval.

These CACHE 1 cases do not test preallocated ranges. For a cached production sequence, rehearse the exact maintenance command with live sessions that already hold reserved values, and define any required reconnect or cache-discard steps before reopening allocation. A single last_value snapshot cannot establish that every allocation path is ready to resume.

A table maximum is not a safe reuse boundary either. It says something about rows visible to that query, not identifiers already handed to application memory, queued work, another session's cache, or external integrations. Inference from MAX(id) to “the next reusable number” is therefore outside this playbook and specifically rejected.

The evidence ledger should end with an ownership question: who can attest that every allocation path represented in the intended uniqueness domain has been stopped or reconciled? If no one can answer, the correct state is HOLD, regardless of how cleanly Q1–Q8 behave in the disposable fixture.

Recover forward without blindly restoring an old number

Recovery should be forward-looking. A saved pre-maintenance last_value is not automatically the value to restore, because identifiers may have been issued between the snapshot and the failure, or may still exist in caches or external work. PostgreSQL's documentation itself warns that last_value can become obsolete immediately under concurrent nextval, and cache sizes greater than one make it reflect reservations rather than merely values already returned.

Start the real recovery design by pausing every known allocation path through an authorized maintenance protocol. “Lock the table” is not an adequate substitute: direct sequence callers can exist independently of a particular target table, and the relevant PostgreSQL lock semantics depend on the object and operation actually being used. The pause needs evidence from the database owner, application owner, and integration owner for the paths they control.

Then inventory issued identifiers and cached or in-flight work. Include committed rows, uncommitted work that may still complete, values already returned to application sessions, queued payloads carrying identifiers, cached sequence ranges where applicable, and values exposed to external systems. The purpose is not to manufacture a perfect historical list when one is impossible; it is to identify what is known, what is unknown, and whether the unknowns prevent reuse.

Choose an authorized non-reusing forward bound only after that inventory has been reviewed. The bound must fit the configured sequence range and consuming data types, and it must be above every identifier that could legitimately reappear in the same uniqueness domain. This article intentionally does not publish a universal SQL statement for that decision. The command depends on the reviewed boundary and on whether the approved procedure requires transactional blocking behavior.

Rehearse the command and abort path against a disposable environment with the relevant settings. Keep uniqueness enforcement in place when allocation resumes. A unique constraint can reject a duplicate row if one is attempted; it cannot revoke an identifier already disclosed to another system or prove that every caller inserts into that table.

If the only proposal is “put the sequence back where it was because maintenance rolled back,” classify it REPAIR. If outstanding allocations cannot be bounded, classify it HOLD. A backward reseed is not justified merely because current rows appear below the proposed number. The recovery question is whether future allocations can avoid colliding with all still-valid issued identifiers, not whether the table looks tidy.

Choose accept, repair, rerun or hold

Approval belongs to the procedure and evidence package, not to a vague statement that “the sequence test passed.” Use four decisions with distinct triggers.

These decisions can apply at different layers. Q2 may be a successful diagnostic and still force REPAIR of the maintenance wrapper. Q8 can end with the expected numeric value and still require RERUN if O never captured B blocked by A. A corrected procedure can remain HOLD for production because application ownership has not yet been reconciled.

The broader practice of staged database change validation is relevant because it separates multiple acceptance claims instead of collapsing them into one successful DDL status. This reseed playbook applies that discipline to a different object: allocator state, concurrency evidence, and authorization each need their own proof.

Assign ownership of every allocation path

The database administrator owns the sequence definition, maintenance permissions, database-side evidence, and the approved sequence command. Application owners identify every code path that calls nextval directly or indirectly and decide how active sessions are drained or reconnected. Integration owners account for identifiers embedded in messages, exports, external requests, or delayed work that can return after maintenance.

The reopening decision needs an explicit owner as well. Someone must attest that the chosen boundary accounts for those populations and that reconnect/cache handling has occurred as designed. This is separate from having permission to run ALTER SEQUENCE; PostgreSQL ownership authorizes the database statement, while operational ownership authorizes the business consequence of issuing future identifiers.

When an allocation path has no accountable owner, record that uncertainty rather than approximating it away. Unknown ownership means HOLD. The cost of pausing is visible; the cost of reissuing an identifier that another system still considers valid may not be visible until later.

Rehearse the reseed before changing a live allocator

A production change should be preceded by a rehearsal of the exact procedure revision, not just a generic sequence experiment. Preserve the failed version that motivated the repair, then test the revised command order, permissions, abort path, first allocation, and concurrency behavior in a disposable environment whose relevant sequence settings match the intended procedure. Never overwrite the failed transcript with a clean rerun. The before-and-after evidence explains why the procedure changed.

The actual maintenance role should be exercised before approval. This article's core lab deliberately uses one owner to avoid permission errors masking semantics, but PostgreSQL requires sequence ownership for ALTER SEQUENCE and particular privileges for sequence functions. A production design may separate application USAGE from maintenance ownership; rehearse those identities intentionally rather than assuming the privileged lab predicts authorization behavior.

Verify bounds outside the sequence object too. The chosen forward value must fit consuming column types and application contracts. It may also interact with validation or business rules that the sequence itself knows nothing about. The reseed review should name those interfaces and their approvers, while keeping the experiment narrowly focused on sequence and transaction semantics.

Rehearse failure at the point that matters. For a RESTART-based procedure, that includes keeping the transaction open, proving the expected allocator wait from another session, and releasing it through both the intended commit and the tested abort path on fresh sequences. For a setval-based procedure, the recovery document must state explicitly that transaction rollback will not reverse the sequence-state change.

Capture SQLSTATE and exact error timing for server-reported failures. psql exposes SQLSTATE for the last SQL query and can render verbose server errors; ON_ERROR_STOP makes scripted termination observable through the documented exit status. For client or connection failures, retain the diagnostic and process exit status, and record SQLSTATE as unavailable when none was supplied. Client-generated libpq errors typically contain only severity and a primary message. A connection loss after sending a statement can leave its outcome uncertain; investigate before retrying.

Finally, distinguish code rollback from data and identifier recovery. Reverting an application deployment can stop future bad calls without undoing sequence state already changed or identifiers already issued. Restoring a row, changing the allocator's future position, and reconciling an external identifier are separate recovery actions with separate owners.

Keep the disposable schema after the run until review finishes. Cleanup should be a separate, explicit action after all sessions have ended, confined to the lab database. The durable artifact is the evidence package: checksummed source, environment identities, per-session transcripts, failures, actual values, reviewer decisions, and the authorization record for any later live maintenance.

Develop recovery discipline with Database Administrator Essentials

Sequence reseed validation is a compact exercise in database administration discipline: define the state that must be restored, separate documented behavior from expected experiment output, observe concurrent ownership, preserve failed evidence, and authorize recovery only after a safe continuation boundary is known. Refonte's broader article on database administration foundations provides context on the wider operational role; the sequence contracts in this playbook remain grounded in PostgreSQL's primary documentation rather than in that overview.

For structured study, Refonte Learning's Database Administrator Essentials page currently lists a three-month programme at 12–14 hours per week. Its published competencies include database design and architecture, SQL query optimization, backup and recovery, performance tuning, security, cloud database management, migration and integration, disaster recovery, role-based access control, and monitoring and maintenance. The page also describes guidance, a “Potential Internship” element, and certificates on successful completion. The page does not specify coverage of this PostgreSQL sequence laboratory or PostgreSQL 18 sequence maintenance. Confirm that specific coverage with the programme team if it is your reason for enrolling.

The operational standard is narrower and stricter than “rollback succeeded”: ACCEPT only after behavior, authorization, allocator ownership, and the forward boundary agree; otherwise REPAIR, RERUN, or HOLD. That is the recovery discipline a sequence maintenance procedure must survive before it is trusted with a live allocator.