test-upgrade.sh: schema snapshot should compare CHECK bodies (pg_get_constraintdef) and convalidated

[crprashant]

Follow-up from the review of PR #238 (full UUID instance/node IDs, issue #129). Pino de Candia (@pinodeca) raised this as a non-blocking suggestion to be filed separately rather than expanding that PR:

Schema snapshot doesn't compare CHECK bodies. Per the PR's own constraint-drift note, test-upgrade.sh doesn't pin pg_get_constraintdef() / convalidated. The new id-format regex is duplicated byte-for-byte in lib.rs and the upgrade script; a future drift in one but not the other would only be caught by pgspot + the functional B1/B2 path. Hardening the snapshot to include pg_get_constraintdef() is worth a follow-up issue.

Problem

scripts/test-upgrade.sh builds a deterministic schema snapshot (SCHEMA_QUERY, ~lines 516–530) and uses it for the Scenario A check (an upgraded schema must be byte-for-byte equivalent to a fresh install). The Constraints section of that snapshot is:

SELECT 'constraint' AS obj_type,
       tc.table_name,
       tc.constraint_name,
       tc.constraint_type,
       string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) AS columns,
       ...
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.table_schema = kcu.table_schema
WHERE tc.table_schema = 'df'
GROUP BY tc.table_name, tc.constraint_name, tc.constraint_type;

This captures a constraint's name, type, and key columns, but:

1. CHECK bodies are not compared. information_schema.key_column_usage does not include CHECK constraints, so each CHECK appears in the snapshot as just (name, 'CHECK', <empty columns>). The actual predicate — e.g. the id-format regex '^[0-9a-f]{8}(-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})?$' — is invisible. Two CHECKs with the same name but different expressions are treated as identical.
2. convalidated is not compared. Whether a constraint is NOT VALID vs validated is not captured.

Why it matters

The id-format regex is currently duplicated verbatim across:

• src/lib.rs — fresh-install DDL (the *_format_chk constraints, e.g. instances_id_format_chk, nodes_id_format_chk).
• sql/pg_durable--0.2.2--0.2.3.sql — the upgrade script that drops and re-adds the relaxed checks.

If those two ever drift (e.g. someone tightens the regex in one place but not the other), the Scenario A schema comparison would not catch it. The drift would surface only via the pgspot SQL gate and the functional B1/B2 backward-compat paths — a weaker, more indirect safety net than an exact schema diff.

Proposed change

Extend the Constraints portion of SCHEMA_QUERY to pin the normalized constraint definition and validation state, sourced from pg_constraint:

SELECT 'constraint_def' AS obj_type,
       rel.relname            AS table_name,
       con.conname            AS constraint_name,
       con.contype::text      AS constraint_type,
       pg_get_constraintdef(con.oid) AS definition,
       con.convalidated::text AS validated
FROM pg_constraint con
JOIN pg_class rel     ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = 'df'
ORDER BY rel.relname, con.conname;

pg_get_constraintdef() emits a canonical, normalized expression, so it is stable to compare across the upgrade vs fresh-install paths.

Acceptance criteria

• The schema snapshot includes each df constraint's normalized definition (pg_get_constraintdef()) and convalidated flag.
• Scenario A (upgraded == fresh install) fails if a CHECK body or validation state differs between the two paths.
• A deliberately introduced regex drift between src/lib.rs and the active upgrade script is caught by scripts/test-upgrade.sh — not just by pgspot / B1 / B2.
• Snapshot output stays deterministic; current passing runs remain green.

Notes / non-goals

• Both the fresh-install DDL and the upgrade script currently add the format checks NOT VALID, so adding convalidated should not introduce a spurious Scenario A diff today; it guards against future divergence.
• Test-tooling hardening only — no extension schema, upgrade script, or runtime change required.