ergon_migration (ergon v0.5.0)

View Source

DDL generators for host tables that want Ergon's patterns.

Ergon's own schema is not built with these. It lives in priv/migrations/ and is applied by ergon_migrate. This module is for the host: the same bi-temporal shape ergon.jobs uses, the same property graph element tables, the same monthly partition lifecycle, generated for tables Ergon knows nothing about.

Every function returns SQL as iodata and executes nothing. Put the output in a .sql file, register the directory with ergon_migrate, and it is applied and journalled alongside Ergon's own:

{ergon, [{ergon_migrate, [
    {extra_sources, [
        #{namespace => ~"my_app",
          sources => [{once, {priv, my_app, "migrations"}}]}
    ]}
]}]}

script/1 joins a statement list into something writable to such a file.

What used to be here

Roughly half of the original helper set has been absorbed elsewhere, and the short answer to "where did X go" is that Ergon now installs its own schema:

  • extension installation, now priv/migrations/bootstrap.
  • the temporal_versioning() function itself, now priv/migrations/functions. It is column-agnostic by design, so a host needs only the trigger, which is versioning_trigger_sql/1.
  • pgmq queue creation and notification, now ergon_pgmq:create_queue_sql/1 and ergon_pgmq:enable_notify_sql/1,2.
  • the job notifier tick, now priv/migrations/cron, since it is Ergon's own.

A note on schemas

These generate unqualified names, so the objects land wherever the host's search_path points, which is the host's business. The one qualified reference is ergon.temporal_versioning(), deliberately: it belongs to Ergon and naming it bare would resolve against the caller's search_path, which is exactly the bug that put every Ergon routine in the wrong schema before Phase 2.

Summary

Types

edge_opts()
endpoint()
vertex_opts()

Functions

bitemporal_table_sql(Table, DataColumns)

The full bi-temporal table shape: table, history twin, time-travel index, and versioning trigger.

edge_table_sql(Table, Source, Dest)

A bi-temporal edge table between two vertex tables.

edge_table_sql/4

A bi-temporal edge table between two vertex tables, with history.

history_twin_sql(Table)

The history twin and its time-travel index for an existing table.

partition_lifecycle_sql(Table)

The weekly cron job that keeps a partitioned table's horizon ahead of ingestion.

partitioned_table_sql(Table, PartitionColumn)

The monthly partition lifecycle for a RANGE-partitioned table.

script(Statements)

Join generated statements into a script, ready to write to a .sql file.

versioning_trigger_sql(Table)

Attach Ergon's system-time versioning trigger to a table.

vertex_table_sql(Table)

A vertex table with a generated identity.

vertex_table_sql(Table, Opts)

A vertex table for a property graph.

Types

edge_opts()

-type edge_opts() :: #{check => iodata(), cascade_source => boolean()}.

endpoint()

-type endpoint() :: {Column :: binary(), Table :: binary()}.

vertex_opts()

-type vertex_opts() :: #{references => endpoint(), extra_columns => iodata()}.

Functions

bitemporal_table_sql(Table, DataColumns)

-spec bitemporal_table_sql(binary(), iodata()) -> [iodata()].

The full bi-temporal table shape: table, history twin, time-travel index, and versioning trigger.

DataColumns is a raw SQL fragment for the host's own columns, inserted between the generated id and the two period columns. It is passed through verbatim, so it is the one argument here that is not validated: a host writing its own column definitions is writing SQL either way.

Two periods, as in ergon.jobs. valid_time is application time, when the row is true in the world, split by UPDATE ... FOR PORTION OF. system_time is belief time, maintained by the trigger, with superseded rows archived into the history twin. The primary key is temporal: an id is unique at any instant, but the same id may own many non-overlapping historical versions.

ergon_migration:bitemporal_table_sql(~"assets", ~"name text NOT NULL, tag text")

edge_table_sql(Table, Source, Dest)

-spec edge_table_sql(binary(), endpoint(), endpoint()) -> [iodata()].

A bi-temporal edge table between two vertex tables.

edge_table_sql/4

-spec edge_table_sql(binary(), endpoint(), endpoint(), edge_opts()) -> [iodata()].

A bi-temporal edge table between two vertex tables, with history.

Source and Dest are {Column, VertexTable} pairs. The uniqueness constraint is temporal, UNIQUE (src, dst, valid_time WITHOUT OVERLAPS), so the same edge may exist, end, and exist again without colliding with its own history.

Options:

  • check => SQL adds a CHECK constraint, e.g. ~"from_id <> to_id" to ban self-loops.
  • cascade_source => true cascades deletes from the source too. Only the destination cascades by default, on the reasoning that removing a thing should remove the edges pointing at it, while edges pointing from it are usually worth keeping until deliberately cleared.

history_twin_sql(Table)

-spec history_twin_sql(binary()) -> [iodata()].

The history twin and its time-travel index for an existing table.

LIKE ... INCLUDING DEFAULTS INCLUDING CONSTRAINTS copies columns, CHECKs and NOT NULLs but deliberately not indexes or generated columns. History is an append-only log the trigger writes verbatim with INSERT ... SELECT (old).*, so a generated column there would refuse the write and a copied temporal PK would reject the very overlaps history exists to record.

partition_lifecycle_sql(Table)

-spec partition_lifecycle_sql(binary()) -> iodata().

The weekly cron job that keeps a partitioned table's horizon ahead of ingestion.

Separate from partitioned_table_sql/2 because a cron schedule is not schema: re-running it is harmless but it belongs with the host's other scheduling rather than in a once migration. Guarded and idempotent through ergon_cron.

One job per table is correct here. See ergon_cron for why this is not the same situation as the pgmq notifier.

partitioned_table_sql(Table, PartitionColumn)

-spec partitioned_table_sql(binary(), binary()) -> [iodata()].

The monthly partition lifecycle for a RANGE-partitioned table.

Emits auto_manage_partitions_<table>(months_ahead int DEFAULT 2), which creates any missing monthly partitions named <table>_YYYYMM from the current month through the horizon, plus one call to establish the initial horizon.

The parent table is not created here. CREATE TABLE ... PARTITION BY RANGE is the host's, because the column list is. PartitionColumn is documentation only: the generated body assumes monthly ranges.

Three things call the emitted function, which is why it exists rather than being inlined: this initial call, the weekly partition-lifecycle-<table> cron job from partition_lifecycle_sql/1, and ergon_partition_boot_check at boot.

script(Statements)

-spec script([iodata()]) -> binary().

Join generated statements into a script, ready to write to a .sql file.

Statement-terminated and blank-line separated, because migraterl hands a whole file to the simple query protocol and lets PostgreSQL split it.

versioning_trigger_sql(Table)

-spec versioning_trigger_sql(binary()) -> iodata().

Attach Ergon's system-time versioning trigger to a table.

The function it attaches, ergon.temporal_versioning(), is installed by Ergon's own migrations and inspects TG_TABLE_NAME at fire time to find the history twin by naming convention, so it serves any table with a <table>_history twin and a system_time column. A host defines no function of its own.

Qualified as ergon. on purpose: a bare name would resolve against the caller's search_path.

vertex_table_sql(Table)

-spec vertex_table_sql(binary()) -> [iodata()].

A vertex table with a generated identity.

vertex_table_sql(Table, Opts)

-spec vertex_table_sql(binary(), vertex_opts()) -> [iodata()].

A vertex table for a property graph.

A vertex table is an identity registry, one row per logical entity, kept in step with a domain table by a trigger the host writes. Ergon emits the shape but not that trigger, because what counts as a new vertex is domain-specific.

Options:

  • references => {Column, ParentTable} makes id a foreign key into a domain table with ON DELETE CASCADE. Omit it when the vertex table owns its own identity, which is also the only choice when the parent's primary key is temporal and therefore not referenceable.
  • extra_columns => SQL for additional columns.

One lesson from Ergon's own graph is worth repeating here: whatever a vertex table's key is, it must be unique. ergon.workflow originally keyed on ergon.jobs (id), which is not unique under a temporal primary key, and every historical version of a job became its own vertex. Point the graph at a view filtered to live rows if the underlying table is bi-temporal.