An AI-generated business briefing often executes several SQL statements: totals, grouped figures and exception details. Updates between those statements can produce an answer that never corresponded to any complete database state. Giving every chart the same generation timestamp does not fix the problem. Calculation and publication boundaries must actually align.
Separate definition errors from reads of different versions
Reliable enterprise Text-to-SQL requires agreed metrics, grain, permissions and time definitions. Here those are assumed consistent. The remaining problem is that individually correct queries read different database versions. It differs from join fanout or missed pagination records: the acceptance object is the complete assembled result.
Consider a constructed report_stock table with east=100 and west=0. A single write transaction transfers stock by changing east to zero and west to 100, then commits. Total stock remains 100. A report first reads east as 100, the writer commits, and the report then reads west as 100. Its assembled regional total becomes 200.
The writer did not expose a partially completed transfer or actually raise stock to 200. The report combined old east with new west. If it first reads the total as 100 and obtains each regional figure separately, the page can display a total of 100 alongside details summing to 200. Each number alone may come from a correctly executed query.
First establish whether the report requires one data view. Some live dashboards legitimately refresh cards independently, but should show their respective data boundaries instead of implying strict reconciliation. Briefings, exports and answers whose totals must match their details usually benefit from a fixed view for the whole set. That is a business design recommendation, not a database requirement.
BEGIN does not automatically fix every SELECT to one snapshot
In PostgreSQL 18's default Read Committed mode, ordinary SELECT statements read committed state as of statement start; successive statements in one transaction can see different commits. Repeatable Read instead retains the snapshot established by its first non-transaction-control statement.[1] Merely adding BEGIN and COMMIT therefore does not repair the example.
For ordinary table reads within one database, a controlled execution service can use BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY; on a dedicated connection, execute all related queries, collect their results and COMMIT. Set transaction modes before querying.[2] This illustrates execution flow, not permission for a model to inject arbitrary transaction controls, privilege changes or unapproved functions.
Connection pooling commonly breaks the intended boundary. If the first query borrows connection A and the second borrows B, independent transactions do not share a snapshot merely because they serve one HTTP request. An ORM's transaction object must reach every actual query; an outer transaction declaration does not help a nested helper that uses its default connection.
Inspect autocommit, reconnect behavior and read-routing too. A replacement connection starts a new boundary. Do not retain an old total and query missing details in a new transaction. Combining ordinary table aggregates into one SQL statement reduces boundaries, but cannot turn arbitrary external functions or multiple sources into one database snapshot.
Parallel reads need a shared snapshot, not simultaneous starts
Workers independently opening Repeatable Read transactions can still obtain different snapshots. PostgreSQL lets a coordinator call pg_export_snapshot() and pass its returned identifier to other sessions, which import it at the start of eligible transactions.[3] Nearly simultaneous starts establish temporal proximity, not a shared version.
One coordination sequence is: A opens a read-only Repeatable Read transaction and exports its snapshot; B and C open read-only transactions at that level and use SET TRANSACTION SNAPSHOT before data queries; once imports succeed, they run in parallel. Use the actual returned identifier, not a value copied from documentation.[2]
Keep the exporting transaction open at least until every worker needing that snapshot has imported it. After the exporter ends, new imports cannot use the identifier.[3] Workers that have imported it complete reads within their own transactions. If a worker disconnects, fails to import or needs a replacement connection, apply a whole-group recovery policy instead of silently using a current snapshot for the missing portion.
This is not a general time machine across databases, clusters or third-party APIs, and it does not share uncommitted changes between sessions. If sequential reads on one connection meet the deadline, adding workers and a coordinator creates avoidable connection, recovery and identifier-lifecycle costs. Establish a need for parallelism before accepting that complexity.
A stable view does not establish freshness or business completeness
A stable snapshot may already lag the business. Replica replay progress affects visible data when reading a replica. If orders and refunds arrive through separate asynchronous pipelines, a database snapshot can consistently contain arrived orders and missing refunds. Stronger local isolation does not manufacture absent upstream data.
Record the business period, source synchronization progress, data batch and report calculation version separately. If several sources must complete one batch, confirm that through the data-publication mechanism and read the published version. Equal wall-clock timestamps across sources do not establish equal business coverage.
PostgreSQL also distinguishes a stable snapshot from serializable behavior: Repeatable Read does not eliminate every serialization anomaly.[1] Reports depending on complex concurrent business invariants need evaluation together with writer design; read-only does not remove that discussion. SERIALIZABLE READ ONLY DEFERRABLE can wait to acquire an appropriate snapshot, and the documentation identifies it as an option for long-running reports and similar work.[2]
That is not a default upgrade for every report. Assess waiting cost, writer isolation policies, referenced objects and business invariants. Our stock example performs the transfer in one atomic write transaction, so a common stable view prevents mixed-version assembly. If the business commits its two changes separately, the intermediate state is itself visible; a reader snapshot cannot repair that workflow design.
Keep snapshots short and publish results as a complete set
Holding a snapshot for a long time can retain old row versions that transactions may still see. PostgreSQL's VACUUM documentation explains why potentially visible versions cannot yet be removed.[4] Avoid waiting inside a database transaction for lengthy model writing, user input or slow external services.
Instead, complete necessary reads within a controlled transaction, preserve a validated result set and its metadata, end the transaction, and then have the model explain that fixed set. Limit stored data according to permissions and retention rules. Preserving results supports later reconciliation; saving only SQL, a timestamp or an expired exported-snapshot identifier does not guarantee reproduction of historical data.
Associate all cards, details, metric versions and authorization scope with one report_run_id. Write an unpublished staging version first. Make the complete result visible only after all queries finish, key totals reconcile and explanatory references are correct. This is an application publication design; database snapshots do not perform the release for it.
If a query times out, expose an incomplete state or discard the undelivered version and recalculate the whole group with the same task parameters. Do not combine an old total with newly queried details under the original version number. If partial delivery is permitted, identify missing items and each data boundary so readers know what cannot be reconciled.
Reproduce with two sessions, then inspect the real connection path
In an isolated test database, create the two stock rows. Session R opens Read Committed and reads east=100. Session W changes east to zero and west to 100 in one transaction, then commits. R next reads west and can observe 100. Repeat with R at Repeatable Read, ensuring its initial read establishes the snapshot before W commits: west should remain zero in R, and the assembled total should be 100.
In another case, let W commit before R establishes its snapshot. R should see east=0 and west=100. Both versions can be correct; mixing them cannot. These are reproducible test designs and expected outcomes derived from documented behavior, not claims of execution on the reader's system or in production.
Then deliberately route the queries through different connections or reconnect before the second query. Check whether the application recognizes that its group boundary was lost. For parallel execution, add premature exporter termination, failed worker imports and already-staged partial results. Verify connection ownership, isolation level, query order and failure handling as well as returned values.
Finally test late upstream data, metric-definition changes, stale cached results and failed explanation generation. Prevent mixing across report_run_id or authorization scopes, and include applicable data and definition versions in cache keys. Snapshot consistency, correct permissions, complete batches and faithful explanations are four independent checks. Passing one does not establish a trustworthy complete business report.