Re: [SQL] maximum number of rows in table - what about oid limits?

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> John Scott <jmscott(at)yahoo(dot)com> writes:
> > my question is, since oid's are used by the
> > postgres kernel, what effect does oid wrapping have upon
> > the stability of the database.
>
> It doesn't affect stability, if by that you mean potential crashes.

yes and know. an application workaround is ok,

>
> What can happen after a wrap is that the OID generated for a
> newly-created object might conflict with some already-existing object's
> OID. If that happens, you get a duplicate-key-insertion error on the
> OID index of the relevant system catalog (pg_class, pg_type, etc).
> There is a unique index on OID for each system catalog wherein OID
> is used to identify objects. It doesn't really matter whether the
> same OID is reused in different catalogs or in user tables.

depends on how your are changing the catalog.
but does retrying fix the problem? wouldn't the second try just often
see the same oid again. does a failed attempt increment the oid????

>
> The odds of this happening seem pretty low, if you've got reasonable
> numbers of objects (eg, with a couple thousand tables in a database,
> you'd have a 2000/4G = 1 in 2 million chance of collision on pg_class
> OID). If it does happen, you can just retry the failed object-creation
> command until you get a nonconflicting OID assignment.

same argument as above.

all this sounds like a reasonable halfway solution.
i'm comfortable NOT using OID's at the application level for many reasons.
that's not a problem. i just don't want my imported, test database of 20B
tuples crashing for mysterious reasons, obviously, right out of the gate,
with me mumbling and
waving my hands at the wall. i can do that now with
commercial systems.

>
> This is certainly not ideal, but it's not nearly as big a problem as
> transaction ID wraparound. You can live with it, whereas right now
> xact ID wraparound is catastrophic. That we gotta work on, soon.

yep, 1000tx/sec ~~ 50 days before wrap.

by the way, have you written anything up on the txid wrapping problem?
we talked about this at osdn last year in san jose. i proposed
64 bits, and you discussed a clever wrapping algorithm involving 48 bits and,
if memory serves me, backward compatiablility of the disk format.
i was the one driving the car.

probably time to take this thread private?

cheers-john

>
> regards, tom lane

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/