object_reference cannot survive binary pg_upgrade: _sentry_mv crashes it, and repair logic can't fix stale OIDs anyway #24

Summary

object_reference cannot currently survive a binary pg_upgrade, and the repair logic that's supposed to fix stale OID references after one has its own bugs, independent of that.

Root cause 1: _sentry_mv crashes the upgrade outright

_sentry_mv (CREATE MATERIALIZED VIEW _object_reference._sentry_mv AS SELECT _object_reference._repair();, marked via pg_extension_config_dump()) exists to auto-run _repair() whenever a logical pg_dump/pg_restore happens. Under binary pg_upgrade it fails with:

pg_class heap OID value not set when in binary upgrade mode

Confirmed as a general PostgreSQL limitation, not specific to this extension's code (reproduced with a bare, non-extension materialized view under the same pg_extension_config_dump marking): restoring a config-dumped materialized view triggers a REFRESH, which builds a brand-new heap — and binary-upgrade mode only pre-assigns OIDs for the objects its own schema-restore script explicitly creates, not for a heap created via a later REFRESH.

Root cause 2: real repair work is needed after every binary pg_upgrade — not an edge case

Verified empirically across two independent upgrade pairs (PG 12→13, PG 16→17), with _sentry_mv temporarily worked around purely to let the upgrade complete: of 14 tracked object types spanning nearly every supported cat_tools.object_type, table/index/sequence/view/materialized-view/column/type stay valid (pg_upgrade's OID-preservation covers pg_class/pg_type/pg_enum), but constraint, function, cast, default-value, and trigger — anything backed by a different catalog — get a brand-new OID every single upgrade, unconditionally. This isn't a rare corner case; it's certain to happen on any tracked function/trigger/constraint/cast/default across any binary upgrade.

Root cause 3: the repair functions themselves can't fix that case today

• _repair() (what _sentry_mv invokes) calls _object_oid__add(), which does a plain INSERT INTO _object_oid(...) with no ON CONFLICT. The moment a row already exists for that object_id — exactly what pg_upgrade leaves behind, since it preserves table data physically rather than wiping it the way a logical restore does — it crashes: duplicate key value violates unique constraint "_object_oid_pkey".
• _object_reference.fix_refs()'s "extraneous ID information" branch (the one that should handle "row exists but is stale") references r_object.object_id instead of r_object_v.object_id — a typo that makes it crash (missing FROM-clause entry for table "r_object") instead of emitting its intended warning, for both fix_refs(true) and fix_refs(false).

Net effect

Today: a binary pg_upgrade of a database with object_reference installed fails outright (root cause 1). Even after that's fixed, the extension has no working way to repair the OID staleness that pg_upgrade genuinely introduces for several object types (root causes 2+3) — _repair()/fix_refs() need real fixes to handle "OID row present but stale," not just "OID row missing," regardless of how/when they get invoked.

Also worth fixing while in this area: _object_v__for_update()'s retry loop predates ON CONFLICT

_object_v__for_update()'s current get-or-create for the object row uses a hand-rolled retry loop (SELECT, conditionally INSERT ... ON CONFLICT DO NOTHING, SELECT again, up to 10 times) predating INSERT ... ON CONFLICT support. This can be collapsed to a single INSERT ... ON CONFLICT DO NOTHING RETURNING object_id plus one follow-up SELECT if no row comes back — no loop needed, since ON CONFLICT already waits out a concurrent inserting transaction. Independent of, and simpler than, the _object_oid reconciliation the rest of this issue is about — but the two need to compose cleanly, since _object_v__for_update is a view (joined against _sanity()), so the _object_oid resolution can't be folded into the same RETURNING clause regardless.

Scope note

This issue is about the underlying bugs (the crash, the missing-repair-path bugs, and the ON CONFLICT modernization above) — not about when/how repair gets automatically triggered after an upgrade. That design question is tracked separately in #38.