# Limits and operational semantics ## The Ecto sandbox is incompatible with behavioural tests `Ecto.Adapters.SQL.Sandbox` holds each test inside one outer transaction. SysTime identifies versions by that top-level transaction, so every later write in the test is correctly treated as an intermediate, externally invisible state. Behavioural tests must use an ordinary pool, run synchronously, and clean up explicitly. `SysTime.travel/4` detects the sandbox by reading `repo.config()[:pool]`; it never checks out a connection to probe it. Repository configuration does not include a pool override supplied only to `Repo.start_link/1`, and it does not describe a dynamically selected repository. SysTime therefore cannot detect a sandbox introduced only through either mechanism. Do not run behavioural tests through such an override. ## One transaction creates one visible version Each live row stores an internal `xid8` from `pg_current_xact_id()`. PostgreSQL returns the top-level transaction ID even inside a savepoint. On an update or delete, SysTime compares that ID with the current transaction. If they match, no second history row is written and the existing lower bound is retained. Other transactions could never observe the intermediate row image. The consequence is deliberate: two updates in one transaction produce one history row containing the state visible before the transaction, followed by the final live state. Version boundaries normally use `transaction_timestamp()`, so every row changed by one transaction becomes visible at the same instant. The transaction ID, not that timestamp, identifies the writer. There is one contention exception. An older transaction can wait for and then overwrite a row whose current period began after that transaction started. Its boundary is clamped to one microsecond after the predecessor's lower bound. That prevents reversed or empty periods, but rows taking this path need not share the transaction's ordinary boundary. The boundary is transaction start time, not commit time: a row trigger cannot know whether or when its transaction will commit. A long-running transaction can therefore record a boundary earlier than its eventual commit. If historical reads must reproduce exact MVCC commit visibility at arbitrary wall-clock instants, a trigger-only mechanism is insufficient. `transaction_timestamp()` is also the stable "now" returned by `sys_time.as_of()` outside a travel block. It must not identify writers: it is constant from transaction start, so an older transaction can observe and overwrite a row committed by a transaction with a newer start time. ## Positional identity is a hard invariant The trigger uses `INSERT INTO history SELECT ($1).*`. Base and history columns must therefore have identical names and PostgreSQL types at identical ordinal positions. `SysTime.Migration.sync/1` can append a missing trailing history column. It raises on any existing-position mismatch and refuses to remove trailing history columns. History tables do not inherit base-column defaults. In particular, a serial primary key in history must never draw a new value from the base table's sequence. Trigger archives provide complete row images; manual backfills and restores must do the same. Call `sync/1` in the same deployment transaction as a trailing column addition. For renames, type changes, and removals, write explicit DDL for both tables and replace the views; do not expect `sync/1` to infer intent. ## Write and storage amplification An update writes a full old row to history, changes the live heap tuple, and maintains the history exclusion constraint. Large rows and update-heavy tables can be expensive. TOAST reduces some duplication but does not turn this into a delta store. Retention and partitioning are application responsibilities. The exclusion constraint uses `btree_gist` across every primary-key column and `sys_period`. A table without a primary key receives no overlap constraint; the migration emits a PostgreSQL `NOTICE`. Retention is intentionally not a runtime API: deleting history changes the meaning of past reads and should be conspicuous deployment DDL. If policy requires pruning, run the equivalent of the following with the real cutoff: ```sql BEGIN; LOCK TABLE shareholders_history IN ACCESS EXCLUSIVE MODE; ALTER TABLE shareholders_history DISABLE TRIGGER sys_time_history_append_only; DELETE FROM shareholders_history WHERE upper(sys_period) < TIMESTAMPTZ '2024-01-01 00:00:00Z'; ALTER TABLE shareholders_history ENABLE ALWAYS TRIGGER sys_time_history_append_only; COMMIT; ``` The transaction makes failure restore the trigger automatically. Verify the cutoff and backup before running it; pruned instants cannot be reconstructed. ## Views can produce worse plans Travel replaces each live source with a `_v` view containing a filtered `UNION ALL` of live and history. PostgreSQL can push ordinary predicates into the branches, but estimates, join order, and aggregate plans can still be worse. Use `EXPLAIN (ANALYZE, BUFFERS)` on important travelled queries. Travel emits `[:sys_time, :travel, :start | :stop | :exception]` events. The instant is present in metadata and successful stops include native-unit duration, so production applications can measure travel frequency, age, and cost without instrumenting individual queries. PostgreSQL does not propagate a base table's primary-key functional dependency through the union view. A query selecting `s.name` must group both `s.id` and `s.name`; grouping only `s.id` works on the base table but fails while travelling. Ecto association joins are resolved after `prepare_query/3`, too late for a source-only rewrite. SysTime raises when an `assoc/2` target is temporal. Use an explicit schema join; ordinary preloads remain supported. Association joins to non-temporal schemas remain usable when their owner source preserves its schema, including a whole-schema subquery. A schema-less owner raises because SysTime cannot prove the target is non-temporal. Expression subqueries, CTEs, and union/intersect combinations are traversed. ## SQL inspection does not run the Repo hook `Ecto.Adapters.SQL.to_sql/3` prepares a query directly through the adapter and does not invoke the repository's `prepare_query/3`. It therefore shows the same base-table SQL inside and outside `travel/4`. To inspect travelled SQL, call the repository's `prepare_query/3` first and pass the returned query to `to_sql/3`. ## Logical replication All three per-table triggers are `ENABLE ALWAYS`; they continue firing when `session_replication_role = replica`, including logical-replication apply workers used by some major-version upgrade procedures. If both base and history tables are published, the destination can receive duplicate history writes. Publish the base tables and regenerate history at the destination, or design the publication topology explicitly. The history insert trigger closes an open period but preserves an already closed one. `COPY`, restore, ETL, and logical replication can therefore replay complete history rows without moving their upper bounds to restore time. ## Restoring into installed temporal tables The base trigger deliberately replaces caller-supplied system columns on an ordinary insert. Setting `session_replication_role = replica` does not bypass an `ENABLE ALWAYS` trigger. `pg_restore --disable-triggers` does use explicit `DISABLE TRIGGER ALL`, but restores triggers with `ENABLE TRIGGER ALL`, which would downgrade SysTime's trigger from `ALWAYS` to ordinary `origin` mode. Loading base-table data into an already installed SysTime schema therefore requires an explicit maintenance sequence: ```sql BEGIN; ALTER TABLE shareholders DISABLE TRIGGER sys_time_archive; -- Restore complete base and history row images, including sys_period. UPDATE shareholders SET sys_transaction_id = pg_current_xact_id(); ALTER TABLE shareholders ENABLE ALWAYS TRIGGER sys_time_archive; COMMIT; ``` Keep application traffic stopped throughout. If using `pg_restore --disable-triggers`, run the transaction above immediately after the restore; its first statement disables the archive trigger again before the transaction-ID reset. Also restore the two history triggers to their required mode: ```sql ALTER TABLE shareholders_history ENABLE ALWAYS TRIGGER sys_time_close_period; ALTER TABLE shareholders_history ENABLE ALWAYS TRIGGER sys_time_history_append_only; ``` Resetting `sys_transaction_id` is required because transaction IDs belong to a database cluster; preserving an ID from another cluster could make a future write look like part of the restore transaction. If the operation is interrupted outside one transaction, restore all three triggers with `ENABLE ALWAYS` before reopening traffic, then run the generated drift tests. ## Other boundaries - This is system time only, not valid time or bitemporality. - `TRUNCATE` has no row images and is not archived. - Historical data is append-only through ordinary `UPDATE` and `DELETE`; a database owner can still alter or drop it. - Nested travel raises. Two active instants on one transaction are ambiguous, and savepoint rollback interactions with `SET LOCAL` are not exposed. - Travel also raises inside an ordinary surrounding repository transaction. Otherwise `SET LOCAL` and read-only mode would remain active until that outer transaction ended. - Travel transactions are read-only and use transaction-local configuration, so the instant cannot leak through a pooled connection after commit or rollback. - Travel state and Ecto transaction ownership belong to the calling process. Queries spawned in a separate task do not inherit either. - Primary keys are immutable after insertion. Changing one would split a single version lineage across two identities. - `versions/2` and `at/3` return the association-free generated `History` schema, preventing historical values from loading current associations.