Re: 4 billion record limit?

Paul Caskey <paul@nmxs.com> writes:
> Tom Lane wrote:
>> It's only a hard limit if your application assumes OIDs are unique.
>> If you don't assume that, then I think it's not a big problem.
>>
>> It's possible (though obviously not especially likely) that you might
>> get OID collisions in the system tables after an OID-counter wraparound.

> This implies they do wrap around.  So they are reused?  Chris said no, but
> you're saying yes.

> (Maybe they wrap around "by accident", by adding one to MAXINT, which will
> give zero on an unsigned int, I believe.  Will the system choke on zero?
> Has anyone tested this wraparound?)

Yes, yes, and yes ;-).  I just committed a patch to prevent the system
from generating a zero OID during wraparound.  I think that the old code
would not have done so in any case, but correct behavior would've
depended on the stored OID counter always being a multiple of 32,
which seems a tad risky to assume over a long time span.  There's a
more direct check for wraparound in there now.

(No, I didn't test it the hard way ... just stopped the postmaster and
hacked on the OID counter in the pg_variable file by hand ...)

> Even if they do wrap, if I have some old records lying around with a low
> OIDs, they will trip me up.

No doubt about it, you're likely to get a few "duplicate key" errors and
stuff like that.  I'm just observing that it's not likely to be a
complete catastrophe, especially not if you don't rely on OIDs to be
unique in your user tables.

We have talked about offering 8-byte OIDs as a compile-time option,
and I think it'll happen eventually, but I'm not holding my breath.
Lots of other stuff seems more pressing...

            regards, tom lane