Limits and operational semantics

Copy Markdown View Source

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:

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:

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:

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.