Finds and repairs phoenix_kit_* tables whose uuid column is not a proper
primary key.
The invariant the chain claims — every PhoenixKit table has a non-null uuid
of type uuid, defaulted to uuid_generate_v7(), as its primary key — was
violated on a production install, and by three separate migrations in
sequence. See PhoenixKit.Migrations.Postgres.V163 for how.
Why the SQL lives here rather than in the migration
Two callers need exactly the same repair with different execution and different risk appetite:
V163runs duringmix ecto.migrate, inside a transaction, viaEcto.Migration.execute/1, and defers tables large enough that the rewrite'sACCESS EXCLUSIVElock would be an outage.mix phoenix_kit.repair_uuidruns when an operator chooses, outside a transaction, via the repo directly, with no size limit and with the index builtCONCURRENTLYwhere that is possible.
Writing the statements twice would guarantee they drift, and drift in a primary-key repair is how a table ends up half-fixed. This module owns the SQL; the callers own how and when it runs.
Detection is catalog-driven
broken_tables/2 asks PostgreSQL which tables are actually wrong rather than
consulting a hardcoded list. Every earlier attempt used a list, and the table
that prompted this work was absent from all of them — including the list in
the migration that was specifically written to repair its class of problem.
Summary
Functions
Every phoenix_kit_* base table whose uuid column needs work.
Whether every non-null value in the column can be cast to uuid.
Human-readable summary of what is wrong with one table, for logs and output.
Rows that de-duplication would DELETE, counted before any DDL runs.
Estimated row count, from the planner's statistics rather than a count(*).
Whether this table's uuid column is not a proper primary key.
Fully-qualified, QUOTED table name.
The ordered SQL statements that repair one table.
Whether repairing this table requires rewriting it (a type change).
Functions
Every phoenix_kit_* base table whose uuid column needs work.
Returns maps of %{name, type, nullable, has_pk}. A table is included when
the column is the wrong type, is nullable, or the table has no primary key —
the predicate the earlier guards should have used. column_exists?/3 is true
for a character varying uuid, which is precisely why V40 skipped the table
it was listed in.
Whether every non-null value in the column can be cast to uuid.
Checked BEFORE any DDL: ALTER … USING uuid::uuid aborts the surrounding
transaction on the first malformed value, which in a migration means taking
every other table's repair down with it.
Human-readable summary of what is wrong with one table, for logs and output.
Rows that de-duplication would DELETE, counted before any DDL runs.
The delete in repair_statements/4 is the only destructive step here, so both
callers announce it rather than discovering it afterwards. lower(uuid::text)
matches what the eventual cast to uuid collapses — two varchar rows
differing only in case are one row after the rewrite — and works whatever the
column's current type is.
@spec estimated_rows(Ecto.Repo.t(), String.t(), String.t()) :: non_neg_integer() | :unknown
Estimated row count, from the planner's statistics rather than a count(*).
A sequential scan to decide whether to avoid an expensive operation is
self-defeating, so this reads the planner's reltuples.
Returns :unknown when the table has never been vacuumed or analyzed
(reltuples = -1 on PostgreSQL >= 14) or the catalog read fails. That used to
be reported as 0 on the reasoning that "being wrong in that direction leaves
the table correct" — true of the outcome, false of the cost, which is the only
thing the caller is asking about. A freshly restored 200k-row table read as
zero and took ACCESS EXCLUSIVE for a full rewrite mid-deploy, which is the
exact event the size limit exists to prevent.
Whether this table's uuid column is not a proper primary key.
has_pk is "the table has a primary key", not "the primary key is on
uuid". A table keyed on some other column is therefore reported healthy
once its uuid column is typed and non-null, even though uuid is still not the
key. Promoting it would mean dropping the existing key, which this migration
will not do unasked; no table in the chain is in that shape (V74 removed the
last of the legacy id keys), so the gap is recorded rather than coded around.
Fully-qualified, QUOTED table name.
The prefix is validated upstream, but the table name comes from the catalog and is interpolated into DDL. Quoting it means a legal-but-unusual identifier — a space, a capital, a reserved word — produces valid SQL rather than a syntax error that aborts the run. PostgreSQL escapes an embedded quote by doubling it.
The ordered SQL statements that repair one table.
Order is load-bearing and each step depends on the one before:
- type — before anything is built on the column, so the index and the
primary key are built on
uuid, not on thevarcharit replaces. - default — so rows inserted between here and the backfill get a value.
- backfill — before
SET NOT NULL, or the constraint aborts on exactly the NULLs it exists to prevent. - NOT NULL — before the primary key, which requires it.
- de-duplicate — a table that has run without a key can hold duplicate
uuids, and
ADD PRIMARY KEYaborts on them. Rows sharing a uuid are the same logical row twice;ctidpicks one deterministically. - primary key — last, once uniqueness and NOT NULL both hold.
concurrent_index: true splits the key into a CREATE UNIQUE INDEX CONCURRENTLY plus ADD PRIMARY KEY USING INDEX, which holds the exclusive
lock only for the attach rather than the whole build, and drops any leftover
index of that name first so an interrupted run is retryable. It cannot run
inside a transaction, so it is available to the mix task and not to the
migration.
Whether repairing this table requires rewriting it (a type change).