Re: Re: 4 billion record limit?

Andrew Snow wrote:
>
> > > That's an excellent point, especially considering that *sequences* use
> > > an integer to hold their max_value, which is by default 2,147,483,647.
> > > You cannot go larger than that, either. I guess it's constrained to be
> > > positive. So OIDs give you more potential unique values than sequences,
> > > far as I can tell.
>
> What about postgres' int8 type which holds up to +/- 9 billion billion or
> something.
>
> - Andrew

Yes, it is quite possible to store 8-byte integers in a Postgres table
with the INT8 datatype, even on a 32-bit platform like Intel.
Unfortunately, as stated above, sequences are locked in at 4 bytes.
Still, that's my solution for now, regarding large unique record
identifiers. I have my own INT8 "id" field, but tie it to an INT4
sequence until INT8 sequences are available. Then my table will never
need to change; I can just drop the old sequence and start a new one where
the old one left off.

But for my purposes, this is irrelevant because the hidden INT4 oid will
still get me. Since the oids span all tables and all databases on a
machine, I will "run out" of oids long before any of my individual table
id sequences. But per the discussion here, by the time this is a problem,
we will have INT8 oids and/or optional oids altogether, so all is good.

I believe you can use negative numbers in a sequence, but it's not
recommended. One good reason is that if you ever hit 0, your code may
break or misbehave. Consider a common test like "if (! $id) ..." in Perl,
C, PHP, etc. There are probably other good reasons for staying positive
[sic]. Some other databases [which shall remain nameless here] enforce
positive numbers for auto-incrementing integers.

There's a recent discussion in the pgsql-hackers list about 64-bit
sequences, so paw through the archives if you're interested. Bottom line:
same as 64-bit oids, they will come along, eventually, probably before
32-bit sequences are a problem for anyone. Over and out,

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