Thread: Re: [SQL] maximum number of rows in table - what about oid limits?
John Scott <jmscott@yahoo.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.
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.
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.
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.
regards, tom lane
<snip> If it does happen, you can just retry the failed object-creation command until you get a nonconflicting OID assignment. <snip> Tom, or anyone who'd know for that matter, what is the exact error I would need to look for? Tim ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: <john@august.com> Cc: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>; <pgsql-general@postgresql.org>; <pgsql-sql@postgresql.org> Sent: Wednesday, June 06, 2001 3:37 PM Subject: [GENERAL] Re: [SQL] maximum number of rows in table - what about oid limits? > John Scott <jmscott@yahoo.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. > > 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. > > 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. > > 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. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
"Tim Barnard" <tbarnard@povn.com> writes:
> <snip>
> If it does happen, you can just retry the failed object-creation
> command until you get a nonconflicting OID assignment.
> <snip>
> Tom, or anyone who'd know for that matter, what is the
> exact error I would need to look for?
It'd be a complaint about attempt to insert a duplicate key into
the unique index on one of several system catalogs' OID columns.
There wouldn't be any earlier detection of the problem than that.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > John Scott <jmscott@yahoo.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/
Paul Tomblin <ptomblin@xcski.com> writes: > I'm doing some postgres stuff in perl. I haven't done much database stuff > in many years, and back then I was doing Oracle in C. I'm doing a major > query that gets a bunch of fields including a key, and then does other > queries with that key on other tables. Now with Oracle I would have set > up and compiled a query for the subqueries, and then only substituted in > the key variable and do the query, and repeat ad-nauseum. (Forgive the > fact that I've forgotten the terminology) There doesn't seem to be any > way to do that in Pg.pm, unless I'm missing something, and my subqueries > are making the whole thing slow as molasses. I think the query I just did > took about 45 minutes when I did the subqueries, and about 30 seconds > without. It's hard to say what might be going on, but I doubt that the query-compile overhead is hitting you that hard. Care to post a schema and EXPLAIN output for the queries you're doing?? -Doug (a former Monastery inmate..) -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan