Design: how (if at all) to auto-detect a binary pg_upgrade and trigger OID repair

[jnasbyupgrade]

Summary

Tracking issue for a design question, not something to fix now. Follow-up to #24: even once the repair logic itself is fixed to handle stale (not just missing) OID rows, something still needs to notice a binary pg_upgrade happened and trigger that repair — right now nothing does, so a database that's gone through pg_upgrade silently carries stale OID references for any tracked function/trigger/constraint/cast/default until someone happens to run repair manually.

Mechanisms surveyed

• Event triggers during pg_upgrade's restore phase: ruled out. There's no SQL-visible way to distinguish "this DDL is happening inside pg_upgrade's binary-upgrade restore" from any other DDL — pg_upgrade's internal binary-upgrade-mode flag isn't visible to event triggers, and the binary_upgrade.* OID-preassignment calls are plain SELECTs, not DDL. Separately, even if a reliable hook point existed, the actual row data isn't necessarily physically present yet at schema-restore time — pg_upgrade links/copies data files as a distinct step after the schema DDL replay, not interleaved with it.
• Lazy check-on-access (extend what's already partially there): object__getsert() already self-heals the "OID row missing" case inline on every call, but not the "OID row present but stale" case (which hard-RAISEs today, see object_reference cannot survive binary pg_upgrade: _sentry_mv crashes it, and repair logic can't fix stale OIDs anyway #24). Extending it to cover staleness too is possible, ideally guarded by a cheap generation-counter so most calls stay O(1). Doesn't bound the staleness window on its own — could be "ages" before the API happens to be called, exactly the risk that prompted this.
• pg_cron-scheduled periodic check+repair: bounds staleness to a schedule interval regardless of API usage. Notably cheaper here than it would normally be — this org already provisions pg_cron in its shared CI base image, and the task extension (which depends on object_reference) already builds on it — but it's still an environment assumption for any given production cluster (nothing forces pg_cron to be configured everywhere object_reference runs).
• Background worker: ruled out as disproportionate. This extension has no C code at all today; a bgworker would mostly reinvent scheduling that pg_cron already solves, for the cost of introducing a whole new build/packaging story.
• ON login event trigger (PostgreSQL 17+): real, confirmed feature (CREATE EVENT TRIGGER ... ON login, added in PG17 via commit e83d1b0c4, "Add support for event triggers that fire at connection time"). This is the mechanism proposed below.
• No comparable extension auto-detects this. TimescaleDB, PostGIS, and pg_partman all rely on a documented, manually-invoked post-upgrade/post-restore function rather than automatic detection.

Proposed direction: optional PG17+ login trigger comparing a stored "last seen" signal

On login, compare some stored "last known" signal against its current value; if it's changed since last seen, something's up (at minimum a pg_upgrade, possibly a restore) — flag it, and/or trigger a check/repair. Candidate signal to compare: server_version_num (changes exactly when a binary major-version upgrade happens, not during ordinary operation) — needs picking precisely, this is the main open design question for whoever picks this up.

Must be strictly opt-in, not default-on, for two independent reasons:

1. Only usable on PG17+ — this extension currently supports PG12+, so older supported majors can't have it at all.
2. A login trigger fires on every authenticated connection, including standbys (per PostgreSQL's own docs), which is a real performance/behavior cost, and a broken login trigger can lock people out of the database entirely (Postgres's own docs note a recovery mechanism exists for exactly this failure mode, which underlines how much care an opt-in default requires).

Non-negotiable regardless of what (if anything) gets automated

An explicit, documented command that a human/DBA/script can call manually must exist no matter which automatic mechanism (if any) is chosen — matching the convention every comparable extension already uses. This already exists today (object_reference.post_restore()) but depends on #24's fixes to actually work correctly for the pg_upgrade case.

Status

Not being worked on right now — this is a design/options-gathering issue for future work.