Re: Pro et contra of preserving pg_proc oids during pg_upgrade

On Thu, Oct 12, 2023 at 11:43 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Oct 12, 2023 at 2:38 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > It's more like a lot number or surveying tract than an postal address.
> Useful for a single party, the builder or the government, but not something
> you give out to other people so they can find you.
> >
> > Whether or not we copy over oids should be done based upon our internal
> needs, not end users. Which is why the fee that do get copied exists,
> because we store them in internal files that we want to copy as part of the
> upgrade. It also isn't like pg_dump/restore is going to retain them and
> the less divergence between that and pg_upgrade arguably the better.
>
> We build the product for the end users. Their desires and needs are
> relevant. And if they're telling us we did it wrong, we need to listen
> to that. We don't have to do everything that everybody wants, but
> treating developer needs as strictly more important than end-user
> needs is self-defeating.
>

Every catalog has both a natural and a surrogate key. Developers get to
use the surrogate key while end-users get to use the natural one (i.e., the
one they provided). I see no reason to change that specification. And I
do believe there are no compelling reasons for an end-user to need to use
the surrogate key instead of the natural one. The example provided by the
OP isn't one, IMO, the overall goal can be accomplished via the natural key
(if it cannot, maybe we need to make retrieving the natural key for a
pg_proc record given an OID easier). The fact that OIDs are not even
accessible via SQL further reinforces this belief. The only reason to
need OIDs as a DBA is to perform joins among the catalogs and all such
joins are local to the database and even session executing them - the
specific values are immaterial.

The behavior of pg_upgrade only preserving OIDs that are necessary due to
the physical copying of data files from the old server to the new one seems
sufficient both in terms of effort and the principle of doing the minimum
amount to solve the problem at hand.

David J.