Re: 4 billion record limit?

Tom Lane wrote:
>
> Paul Caskey <paul(at)nmxs(dot)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 ;-).

Good answer. :-) Thanks for looking into it.

> > 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.

I don't rely on OID uniqueness, but I assumed Postgres does!

I don't use the OID for anything; I maintain my own "id" field with a
sequence for every table. I can catch a "duplicate key" error in my code,
but I'll treat it as if my own "id" caused a problem. It will totally
confuse me and my code if there's a
"duplicate key" error related to a hidden system field I never create or
examine. I won't know if/how to re-insert with a different key to solve
the problem.

> 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...

Fair enough, and thanks for all your work so far.

--
Paul Caskey paul(at)nmxs(dot)com 505-255-1999
New Mexico Software 5041 Indian School NE Albuquerque, NM 87110
--