Re: [SQL] maximum number of rows in table - what about oid limits?

John Scott <jmscott(at)yahoo(dot)com> writes:
> my question is, since oid's are used by the
> postgres kernel, what effect does oid wrapping have upon
> the stability of the database.

It doesn't affect stability, if by that you mean potential crashes.

What can happen after a wrap is that the OID generated for a
newly-created object might conflict with some already-existing object's
OID. If that happens, you get a duplicate-key-insertion error on the
OID index of the relevant system catalog (pg_class, pg_type, etc).
There is a unique index on OID for each system catalog wherein OID
is used to identify objects. It doesn't really matter whether the
same OID is reused in different catalogs or in user tables.

The odds of this happening seem pretty low, if you've got reasonable
numbers of objects (eg, with a couple thousand tables in a database,
you'd have a 2000/4G = 1 in 2 million chance of collision on pg_class
OID). If it does happen, you can just retry the failed object-creation
command until you get a nonconflicting OID assignment.

This is certainly not ideal, but it's not nearly as big a problem as
transaction ID wraparound. You can live with it, whereas right now
xact ID wraparound is catastrophic. That we gotta work on, soon.

regards, tom lane