A constraint's deferrability is neither declarable nor introspected, so the round trip is a fixpoint only because both sides are blind to it

[jryannel]

Found needing a deferred UNIQUE constraint for a catalog invariant. The missing declaration is the small half; the interesting half is that nothing notices it is missing.

The shape

A variant is identified by its combination of option values, and that combination must be unique within its product. The combination lives in a child table, so no UNIQUE index can span it — the standard answer is a denormalised signature column maintained by a trigger, plus UNIQUE (product_id, option_signature).

That constraint has to be DEFERRABLE INITIALLY DEFERRED, and finding out why cost a run. A variant is inserted before the option values that identify it — they reference it — so every new variant passes through a state where its signature is still the empty default. Two variants of one product therefore collide on ('') at INSERT time, and creating any multi-variant product fails:

ERROR:  duplicate key value violates unique constraint "variants_product_option_combination_key"
DETAIL: Key (product_id, option_signature)=(…, ) already exists.

The rule is about the committed state, not about the middle of a transaction. INITIALLY DEFERRED says exactly that, and it is the only spelling that does — the alternatives (a partial index excluding the empty signature, or a signature computed before insert) either weaken the rule or move it into application code where two concurrent writers interleave.

Half one: it cannot be declared

schema.Unique is {Name, Columns}. There is no deferrability, on Unique, on Field.Unique(), or on TableDef.UniqueNamed(). schema/exclusion.go:81 mentions DEFERRABLE in a list of things exclusions refuse, so the concept is known and deliberately out of scope there; for UNIQUE there is no spelling at all.

That is a gap I can live with. Foreign keys have the same need less often, and most schemas never want a deferred constraint.

Half two: the round trip cannot see it, and that is the part worth filing

My workaround was to declare the constraint in the schema and alter it in a hand-written migration:

ALTER TABLE variants DROP CONSTRAINT variants_product_option_combination_key;
ALTER TABLE variants ADD CONSTRAINT variants_product_option_combination_key
    UNIQUE (product_id, option_signature) DEFERRABLE INITIALLY DEFERRED;

I expected sqlb migrate to report drift, since the declaration says one thing and the database another. It does not:

sqlb: replayed 3 migration(s), 70 statement(s)
sqlb: the history already builds the declared schema; nothing to write

Grepping confirms why: neither introspect/ nor migrate/ mentions deferrable or condeferrable. The introspector does not read pg_constraint.condeferrable, the differ has no field to compare, and the renderer emits none.

So the fixpoint holds because both sides are blind to the same thing. That is stable in my favour today — my workaround survives sqlb check — and it is the failure mode ADR-0016 already describes, in its own words, about the v0.8.0 exclusion work:

with the import silently dropping exclusions, one test failed and named the constraint while the fixpoint test passed, because both registries had dropped the same thing

Same shape. The consequence here is narrower than dropping a whole constraint, but it points the same way: a migration that recreated this constraint without DEFERRABLE would silently break every multi-variant product create, and sqlb check would stay green through it. The drift gate is what I would otherwise rely on to catch that.

What would help, roughly in order

1. Introspect condeferrable / condeferred and compare them, even without a way to declare them. That alone turns my workaround from invisible to reported — a diff that says "the database defers this constraint and the schema does not" is useful information even if the resolution is "yes, on purpose, in a hand-written migration". It is also the half that protects the guarantee the README leads with.

2. A Deferrable() on the unique-constraint declarations, so the workaround is unnecessary. UniqueNamed(...).Deferrable() or a field on schema.Unique, whichever fits — I have no view, and (1) matters more than (2) since (1) is what makes the absence of (2) visible.

3. If neither: a line in the docs naming deferrability as something the round trip does not carry, next to whatever else is known not to be modelled. The README's "requires the round trip to be a fixpoint" is a strong claim and the thing I trusted; knowing its edges would have saved me the experiment.

Not a complaint about the workaround

Declaring the constraint in the schema and altering it in a migration is a reasonable arrangement and I am happy with it — the manifest and the emitters still know the constraint exists, which is most of what I wanted. I have a test that asserts the constraint refuses at commit rather than at the statement, so the deferral is checked on my side. Filing because I only know that arrangement is stable by having tried it, and because the drift gate agreeing with me for the wrong reason is worth someone else knowing about.