PostgreSQL Read Committed vs Repeatable Read Snapshots

Separate the writer's grouping from the reader's view

A relational extraction may assemble one input record from several database reads. Whether those components belong to one database state depends on the reads' visibility boundary. Saying that they run inside one transaction leaves that boundary unspecified.

PostgreSQL's transaction tutorial explains the writer's all-or-nothing grouping: incomplete changes remain hidden from other transactions, and the committed changes become visible as a unit. Its concurrency documentation describes a multiversion model, or MVCC, in which statements see database snapshots. A snapshot selects a visible database version; it need not match the database's current state when the application later assembles its result.

Here, database view means the state visible to a reader, rather than an SQL VIEW object. The question is whether the extraction's separate statements use the same visible state.

References: [PT18][PM18]

PG-59: one commit between two component reads

PG-59 is an original hypothetical chronology, not an executed database test. Fix one primary PostgreSQL 18 database with ordinary base-table rows A and B. Initially, their committed values are A=10 and B=20. A separate writer transaction W changes both to A=100 and B=200 and successfully commits them together.

Reader transaction R first reads A before W commits. That read is R's first non-transaction-control statement. W then commits, and R's second statement reads B afterward. Both reader statements complete successfully within the same R transaction.

R is read-only, with no earlier writes of its own. The example excludes sequences, user-defined or external functions, locking SELECTs and any other writer. Required access and application support are stipulated. Compare independent runs from the same initial state and chronology under the two isolation levels; the Repeatable Read run does not follow the Read Committed run.

The only committed A/B states in this history are 10/20 and 100/200. That fixed history lets us evaluate the pair R assembles without assuming a partially successful writer.

Read Committed returns 10, then 200

In the Read Committed run, the first statement selects the pre-commit state and returns A=10. The second statement starts after W commits, so its new snapshot includes W's changes and returns B=200. R assembles 10/200.

That pair appears nowhere in the stipulated committed history. Before W's commit, B was 20; afterward, A was 100. The assembled result crosses the commit boundary even though W never exposes a partly applied update.

Neither component is a dirty read: both values were committed in the state selected by their respective statements. The mismatch is created by combining results from two snapshots.

For an extraction requiring A and B from one common state, this run fails that requirement while respecting both the writer's atomicity and the reader's stated visibility rules.

References: [PI18][PT18]

Repeatable Read returns 10, then 20

In the PostgreSQL Repeatable Read run, R's first read establishes the snapshot while the database still has A=10 and B=20. The later B read uses that same snapshot and returns 20. R assembles 10/20, the earlier committed pair.

W has committed 100/200 by the second read. Returning 20 therefore meets this example's common-view requirement while leaving the newer B value outside R's selected view.

The timing anchor matters. The PostgreSQL 18 manual specifies the first non-transaction-control statement, rather than merely the time BEGIN is issued. In a different chronology where W commits after BEGIN but before R's first such statement, with no intervening statement establishing R's snapshot, R's selected state would include 100/200.

The useful explanation names the statement that establishes the view. A transaction-start timestamp alone cannot resolve which side of this commit the selected snapshot represents.

References: [PI18]

Choose between a common view and fresh statement values

Suppose an analytical input computes the fictional feature B divided by A. Both committed states in PG-59 produce 2: 20 divided by 10 and 200 divided by 100. The mixed pair A=10 and B=200 produces 20, ten times the ratio in either committed state. If the feature must describe one common database state, the input pair A=10, B=20 is acceptable and the mixed pair A=10, B=200 is not. The issue is the relationship between the values, even though both were committed when read.

A different requirement might deliberately ask each component read for the committed state visible when that statement starts. Under the same chronology, 10 followed by 200 then answers that requirement. Holding B at 20 would supply the shared earlier view instead of the newly visible B value. Neither requirement can be inferred from the phrase transactional extraction.

Ask the appointed application or database provider to identify which values form one logical input, whether their reads share one actual database transaction, the applicable isolation behavior and the statement establishing any shared snapshot. Have the explanation connect those facts to the returned values across an intervening commit. A transaction label or two successful query receipts cannot establish that relationship on its own.

Assess the proposed extraction against its requested property: shared-state membership and per-statement freshness call for different returned results in PG-59.

Separate stable reads, serializability and retained inputs

PostgreSQL Repeatable Read's stable view does not establish universal serializability. The manual explicitly distinguishes a stable snapshot from a result equivalent to every concurrent transaction running one at a time in some order. PG-59's single-writer, read-only deduction should not be expanded into a guarantee for arbitrary business rules or concurrent workflows.

A transaction snapshot also makes no promise here about retaining an input for a later rerun. PG-59 establishes which values R sees during these reads; it supplies no historical backup, retained export or future recovery result.

If the input also includes S3 artifacts, a model package or another database, their relationship to R's selected state needs separate evidence. The related S3 release guide addresses coherent object generations and location. The Iceberg guide addresses a selected, retained table snapshot.

References: [PI18]

Scope any separate physical cabling project

If the wider platform project also needs physical storage or network connections, Leviathan's Structured Cabling & Fiber Installation service and the related project inquiry provide a route to discuss agreed fiber and copper installation, endpoint labels and connection records.

Share a nonconfidential equipment and site summary, approved endpoints and routes, material-supply responsibilities and the required physical handoff. That inquiry concerns separately scoped cabling work. Database administration, extraction integration and data validation remain with the appointed software and database providers.

References: [L1]