Re: 4 billion record limit?

Tom Lane wrote:
>
> Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au> writes:
> > Paul Caskey wrote:
> >> 1. This implies a hard limit of 4 billion records on a server, right?
>
> > Basically, yes.
>
> 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?)

I will not have 4 billion records in one table or even one database. But
on a large server with many databases, it is conceivable to have 4 billion
records on one machine. With a lot of insert/delete activity, over a few
years, it is certainly conceivable to have 4 billion inserts. If the oids
don't wrap, I have a problem. I can ignore it for a long time, but it
will loom, like Y2K. :-)

Even if they do wrap, if I have some old records lying around with a low
OIDs, they will trip me up. Like you said, these are "the outer limits",
but I'm thinking ahead.

Someone suggested in private that I pg_dump/restore all my data to
"repack" the oids which start around 1700 on a fresh database. Thanks for
that idea. Also thanks, Tom, for the sanity check Re: terabytes of data
with 4 billion records. It's still possible, especially in coming years.
It would be a big feather in PG's cap to "fully support" 64-bit platforms
such as IRIX and Solaris (finally) and, coming soon to a theater near you,
Linux on IA-64.

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