Diff renders no CREATE EXTENSION, so the first schema-first bootstrap fails once per table naming a missing function instead of once naming the missing extension

[jryannel]

Bootstrapping an introspected registry into an empty database — the round trip sqlb-survey (#113) runs, and the same path an adopter runs the first time the schema owns the DDL — fails like this:

FAIL create table activity_logs   — ERROR: function uuid_generate_v4() does not exist (SQLSTATE 42883)
FAIL create table ai_conversations — ERROR: function uuid_generate_v4() does not exist (SQLSTATE 42883)
FAIL create table ai_messages      — ERROR: function uuid_generate_v4() does not exist (SQLSTATE 42883)
… 225 more

228 failures, one per table. With uuid-ossp and vector created by hand first, the identical 329 statements applied with zero failures and the round trip left 8 residuals — all unrelated.

So the schema was correct the whole time. What was missing was two CREATE EXTENSION statements that Diff does not know exist.

Why this is worse than a missing feature

An extension is not in the Report either. introspect skips a construct it cannot model and says so — that contract is the whole of ADR-0014, and it holds for types, indexes and constraints. An extension is not skipped, because it was never read: it is invisible on both sides, so a clean Report and a clean Diff both say "everything is represented" about a schema that cannot be created.

The failure then surfaces at the furthest possible point from its cause, in the wrong vocabulary. function uuid_generate_v4() does not exist names a function; the missing thing is an extension; and the message repeats once per dependent table, so the signal is a wall of identical errors with the actual cause named nowhere. That is the opposite of ADR-0011's bar — the error does not say what to do, and there is no single line to read.

Who hits it

Every adopter whose schema uses uuid-ossp, pgvector, pg_trgm, citext or hstore, on the first bootstrap. pgvector is the sharp one: sqlb ships schema.Vector and ADR-0026, so a schema sqlb positively supports declaring cannot be created by sqlb's own DDL without an out-of-band step that is documented nowhere.

schema.UUIDv7 has the same shape wherever the generator is an extension rather than built in.

Suggested shape, smallest first

1. Read them. introspect reads pg_extension and records the non-plpgsql extensions on the registry. Cheap, and it makes the omission visible rather than silent.
2. Emit them. Diff renders CREATE EXTENSION IF NOT EXISTS "<name>" as the first changes, before any table. IF NOT EXISTS keeps it idempotent for the overwhelmingly common case where the extension is already installed.
3. Or, if emitting is wrong — and there is a real argument that it is, since CREATE EXTENSION usually needs a superuser and a production runner may deliberately not have one — then skip them loudly: an entry per extension in the Report, so the adopter learns the list from the tool instead of from 228 identical errors.

(3) alone would have turned this from an hour into a minute, so it is worth doing even if (2) is rejected. Dropping an extension is not proposed under any option: a diff that proposes DROP EXTENSION is a footgun with no upside.

Related

Adjacent to the trigger question — the census note that a trigger, an enum type and an extension are all invisible to Diff rather than dropped by it, and that invisible is the right default but the wrong story when no document says so. This issue is the one member of that family that makes the schema uncreatable rather than merely under-described, which is why it is filed on its own.

Found by sqlb-survey (#113) over a 68-table production schema using uuid-ossp and vector.

[jryannel]

Fixed in #126 (4a56ff3) — options (1) and (3) from this issue, not (2).

introspect reads pg_extension and Report carries the list, rendered as the statements to run rather than as a list of names, because the list is useless as trivia and load-bearing as the step before a bootstrap:

the database has extensions installed, and no generated DDL creates them.
Create them in the target database first, or the first bootstrap fails
once per dependent table naming a function instead of the extension:
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

sqlb-survey prints it first, ahead of the skips, since its Phase C is a bootstrap into a scratch database and it is the command that found this.

Emitting into a migration (option 2) is deliberately not done. That is the arguable half — creating an extension usually needs a superuser a production runner deliberately does not have — so the statements are printed for a person rather than generated for a runner. Worth revisiting with a decision attached; not worth deciding as a side effect of fixing the diagnosis. Dropping is not proposed under any option, as you said.

Empty() and Err() are untouched, and both halves of that are asserted: they answer whether the registry describes the tables, and folding extensions in would fail every pgvector adoption on a gap it has no way to close — a worse bug than the one being fixed.

The pg_extension query is asserted against real Postgres in the fixpoint rather than against canned rows, because a query returning nothing looks exactly like a database with no extensions.

[jryannel]

Closing against #126, which is open for review. Reopen if the branch does not land.