Localize.Ecto.Audit (Localize SQL v1.0.0)

Audits the PostgreSQL server's collation and Unicode state against the running application.

Collations are compiled into indexes: when a PostgreSQL upgrade (or an OS upgrade underneath it) links a newer ICU library whose collation data changed, every index built with an affected collation is silently inconsistent with new comparisons until it is reindexed. PostgreSQL records the collation version each collation was created with and warns on first use after a change; this module surfaces the drift proactively and generates the remediation statements.

The primary public API is report/1 — used by the mix localize.ecto.audit task — with collation_drift/1, database_collation_drift/1, remediation_sql/1 and unicode_versions/1 as the individual checks.

All functions take a started Ecto.Repo for a PostgreSQL database.

Summary

Types

database_collation_drift()

Version drift of the database default collation, or nil when the recorded version matches the collation library. The version values are the opaque strings PostgreSQL stores.

report()

The combined result of every audit, as returned by report/1.

timezone_audit()

IANA time zones known to one of the server and the application but not the other.

unicode_versions()

The server and application collation Unicode versions, and whether they have drifted apart. The individual versions are read from the server or derived from bundled data and are left as opaque terms.

Functions

collation_drift(repo)

Returns the collations whose recorded version differs from the collation library's current version.

database_collation_drift(repo)

Returns the database default collation's version drift, or nil when there is none.

remediation_sql(map)

Returns the remediation statements for one entry of collation_drift/1.

report(repo)

Runs every audit and returns the combined result.

timezone_audit(repo)

Compares the application's IANA time zone inventory with the server's.

unicode_versions(repo)

Returns the Unicode-relevant versions of the server and the application.

Types

database_collation_drift()

@type database_collation_drift() ::
  %{collation: term(), stored_version: term(), actual_version: term()} | nil

Version drift of the database default collation, or nil when the recorded version matches the collation library. The version values are the opaque strings PostgreSQL stores.

report()

@type report() :: %{
  collation_drift: [map()],
  database_collation_drift: database_collation_drift(),
  unicode_versions: unicode_versions(),
  timezone_audit: timezone_audit(),
  ok?: boolean()
}

The combined result of every audit, as returned by report/1.

timezone_audit()

@type timezone_audit() :: %{
  unknown_to_server: [String.t()],
  unknown_to_application: [String.t()]
}

IANA time zones known to one of the server and the application but not the other.

unicode_versions()

@type unicode_versions() :: %{
  server: %{postgres: term(), unicode: term(), icu_unicode: term()},
  application: %{cldr: String.t(), unicode: term()},
  drift?: boolean()
}

The server and application collation Unicode versions, and whether they have drifted apart. The individual versions are read from the server or derived from bundled data and are left as opaque terms.

Functions

collation_drift(repo)

@spec collation_drift(module()) :: [map()]

Returns the collations whose recorded version differs from the collation library's current version.

Arguments

repo is a started Ecto.Repo module for a PostgreSQL database.

Returns

A list of maps with :name, :stored_version, :actual_version and :indexes (the names of indexes depending on the collation, which need reindexing). An empty list means no drift.

Examples

Localize.Ecto.Audit.collation_drift(MyApp.Repo)
#=> []

database_collation_drift(repo)

@spec database_collation_drift(module()) :: database_collation_drift()

Returns the database default collation's version drift, or nil when there is none.

The database default collation orders every text column without an explicit collation, so drift here potentially affects every index on text columns.

Arguments

repo is a started Ecto.Repo module for a PostgreSQL database.

Returns

nil when the default collation version matches, or a map with :collation, :stored_version and :actual_version.

Examples

Localize.Ecto.Audit.database_collation_drift(MyApp.Repo)
#=> nil

remediation_sql(map)

@spec remediation_sql(map()) :: [String.t()]

Returns the remediation statements for one entry of collation_drift/1.

Reindex first, then refresh the recorded version — refreshing first would hide the drift while the indexes are still stale.

Arguments

drift is one map from collation_drift/1.

Returns

A list of SQL statements.

Examples

iex> Localize.Ecto.Audit.remediation_sql(%{name: "de-x-icu", indexes: ["idx_names"], stored_version: "153.14", actual_version: "153.120"})
[~s[REINDEX INDEX "idx_names"], ~s[ALTER COLLATION "de-x-icu" REFRESH VERSION]]

report(repo)

@spec report(module()) :: report()

Runs every audit and returns the combined result.

Arguments

repo is a started Ecto.Repo module for a PostgreSQL database.

Returns

A map with :collation_drift, :database_collation_drift, :unicode_versions, :timezone_audit and :ok? — true when nothing needs attention.

Examples

Localize.Ecto.Audit.report(MyApp.Repo).ok?
#=> true

timezone_audit(repo)

@spec timezone_audit(module()) :: timezone_audit()

Compares the application's IANA time zone inventory with the server's.

A zone name the server does not know fails at query time in AT TIME ZONE; a zone the application does not know cannot be validated by Localize.Ecto.Type.TimeZone. Small differences are normal — the server's tzdata and CLDR's zone inventory update on different schedules — but zones the application writes must exist on the server.

Arguments

repo is a started Ecto.Repo module for a PostgreSQL database.

Returns

A map with :unknown_to_server (canonical CLDR zones missing from pg_timezone_names) and :unknown_to_application (server zones absent from the CLDR inventory, excluding the posix/, Etc/ and abbreviation-style entries PostgreSQL adds).

Examples

Localize.Ecto.Audit.timezone_audit(MyApp.Repo).unknown_to_server
#=> []

unicode_versions(repo)

@spec unicode_versions(module()) :: unicode_versions()

Returns the Unicode-relevant versions of the server and the application.

Arguments

repo is a started Ecto.Repo module for a PostgreSQL database.

Returns

A map with :server (:postgres, :unicode, :icu_unicode — the latter two nil before PostgreSQL 17) and :application (:cldr, :unicode). When the server's ICU Unicode version and the application's CLDR-implied Unicode version differ, :drift? is true — collation results computed in Elixir by Localize.Collation may then order edge-case strings differently from the server.

Examples

Localize.Ecto.Audit.unicode_versions(MyApp.Repo)
#=> %{
#     server: %{postgres: "18.4", unicode: "16.0", icu_unicode: "17.0"},
#     application: %{cldr: "48.2.2", unicode: "17.0"},
#     drift?: false
#   }