Re: 4 billion record limit?

Tom Lane wrote:
>
> Paul Caskey <paul(at)nmxs(dot)com> writes:
>
> >> 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!
>
> Only in the system tables, and not even in all of them. From the
> system's point of view, there's no real need to assign OIDs to
> user table rows at all --- so another possible answer is not to
> do that, unless the user requests it.

That's interesting (almost shocking) to know, but it's probably too late
to put the lid back on that bottle. I imagine a lot of software has been
written especially for postgres, like pgaccess, which use the oid field
along with cursors, etc. A lot of people have probably also relied on the
oid as a convenient unique record identifier. I know I was tempted. If
the system is doing it anyway, why maintain my own with a serial or
sequence?

So if we make the OID optional in user tables, they need to be left in by
default, and only *omitted* with a compile-time option. Otherwise we'll
break many more things than we solve with this Y2K-like problem.

Dave Burbidge wrote:
>
> Ummm ... I'm a newbie to this list, but hasn't this evolved into a hacker
> issue?

Yes, we should move any further detailed discussion over there. I asked
this question initially in the hacker list and got no response, so I came
over here. But we did have a related discussion on 64-bit sequences in
the hacker list.

FWIW, I checked into MySQL, and as far as I can tell, they have nothing
like this implicit 4 billion transactional "limit". So maybe competitive
spirit will drive the postgres hackers to fix this problem sooner than
later. ;-)

If nothing else, this needs to be documented. Whether by design or not,
people need to know there is a hard limit of 4 billion records on a
server, and a strange fuzzy sorta-limit of 4 billion inserts on a server.
Regardless of whether they use a 64-bit compiler.

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