Re: preserving db/ts/relfilenode OIDs across pg_upgrade (was Re: storing an explicit nonce)

On Tue, Aug 17, 2021 at 1:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Right. If pg_upgrade explicitly ignores template0 then its OID
> need not be stable ... at least, not unless there's a chance it
> could conflict with some other database OID, which would become
> a live possibility if we let users get at "WITH OID = n".

Well, that might be a good reason not to let them do that, then, at
least for n<64k.

> > In fact, I'd probably go so far as to
> > hardcode that in such a way that even if you drop those databases and
> > recreate them, they get recreated with the same hard-coded OID.
>
> Less sure that this is a good idea, though. In particular, I do not
> think that you can make it work in the face of
> alter database template1 rename to oops;
> create database template1;

That is a really good point. If we can't categorically force the OID
of those databases to have a particular, fixed value, and based on
this example that seems to be impossible, then there's always a
possibility that we might find a value in the old cluster that doesn't
happen to match what is present in the new cluster. Seen from that
angle, the problem is really with databases that are pre-existent in
the new cluster but whose contents still need to be dumped. Maybe we
could (optionally? conditionally?) drop those databases from the new
cluster and then recreate them with the OID that we want them to have.

> > The only hard requirement for this feature is if we
> > use the database OID for some kind of encryption or integrity checking
> > or checksum type feature.
>
> It's fairly unclear to me why that is so important as to justify the
> amount of klugery that this line of thought seems to be bringing.

Well, I think it would make sense to figure out how small we can make
the kludge first, and then decide whether it's larger than we can
tolerate. From my point of view, I completely understand why people to
whom those kinds of features are important want to include all the
fields that make up a buffer tag in the checksum or other integrity
check. Right now, if somebody copies a page from one place to another,
or if the operating system fumbles things and switches some pages
around, we have no direct way of detecting that anything bad has
happened. This is not the only problem that would need to be solved in
order to fix that, but it's one of them, and I don't particularly see
why it's not a valid goal. It's not as if a 16-bit checksum that is
computed in exactly the same way for every page in the cluster is such
state-of-the-art technology that only fools question its surpassing
excellence.

--
Robert Haas
EDB: http://www.enterprisedb.com