Re: 4 billion record limit?

Tom Lane wrote:
>
> Paul Caskey <paul@nmxs.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@nmxs.com        Software Engineer
New Mexico Software    5041 Indian School NE    Albuquerque, NM 87110
--