On Tue, Aug 17, 2021 at 1:54 PM Tom Lane <t...@sss.pgh.pa.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