Re: 4 billion record limit?

Tom Lane wrote:
>
> 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 ;-).

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@nmxs.com        505-255-1999
New Mexico Software    5041 Indian School NE    Albuquerque, NM 87110
--