Document (and maybe support) testing ALTER EXTENSION UPDATE via test/install

[jnasbyupgrade]

Best practice: run the test suite against an ALTER EXTENSION UPDATE'd database via test/install

While working on cat_tools we found a clean way to use pgxntool's test/install
feature to verify that an upgraded extension database behaves identically to
a fresh install — a high-value test that catches update-script bugs (wrong
mappings, missing REVOKEs, drifted objects) that a "does the update script run"
sanity check misses. Filing this to document it as a best practice (and maybe to
support it first-class in pgxntool).

The pattern

• The extension is loaded once, committed, before the suite by a
  test/install/ file, in one of two modes selected by a variable:
  • fresh: CREATE EXTENSION <ext>
  • upgrade: CREATE EXTENSION <ext> VERSION '<oldest installable>' then
    ALTER EXTENSION <ext> UPDATE (to the current default_version)
• test/deps.sql no longer does the per-test CREATE EXTENSION; the committed
  install in test/install persists into every (rolled-back) test.
• The same test/sql/* suite and the same expected output run in both
  modes. Identical expected output passing in upgrade mode is exactly the
  fresh-vs-upgrade equivalence assertion.

Why it MUST be test/install (committed), not test/deps.sql (per-test)

This is the key insight and the reason it belongs in test/install
specifically: if the update runs ALTER TYPE ... ADD VALUE, PostgreSQL forbids
using the newly added enum value in the same transaction that added it
(ERROR 55P04 unsafe use of new value). pgTAP wraps each test/sql/ file in a
BEGIN … ROLLBACK, so performing the upgrade inside a test's transaction can
never work if the suite then uses those new values. test/install runs
committed, before the suite, in its own session — which both fixes this and
faithfully models a real production upgrade (ALTER EXTENSION UPDATE commits,
later transactions use the new values).

Bonus

Because the committed install is shared across all tests (they're read-only
w.r.t. the extension), this also removes the per-test re-install cost — a real
speedup for extensions with many/large dependencies.

Ask

At minimum, document this in the pgxntool docs (README / a testing guide) as the
recommended way to test extension updates. Optionally, provide first-class
support — e.g., a TEST_LOAD_SOURCE=fresh|upgrade style toggle and a stock
test/install loader — so extensions get fresh-vs-upgrade equivalence testing
with minimal boilerplate.

Worked example: Postgres-Extensions/cat_tools PR #16 (test/install/load.sql,
test/deps.sql, and the TEST_LOAD_SOURCE Makefile plumbing).