Thread: OID's....

  I know that this topic comes up fairly often, so I tried to search the
archives, but the search engine doesn't appear to have info on messages
after 1999, so forgive me for repeating this topic.

   Recently, our OID usage has started to jump dramatically - today, we're
using ten thousand or more in a few minutes.  We're trying to figure out
just what is using so many, without any luck.  Aside from doing a lot of
inserts (which we don't do many of), what would cause this?  Are they
pre-allocated for transactions and not freed?

   Also, when the OID's reach the limit of an int4, if I recall correctly,
they simply wrap around, and things keep working unless you depend on
unique OID's.  Is that correct?

steve

On Wed, 30 May 2001, Steve Wolfe wrote:

>
>   I know that this topic comes up fairly often, so I tried to search the
> archives, but the search engine doesn't appear to have info on messages
> after 1999, so forgive me for repeating this topic.
>
>    Recently, our OID usage has started to jump dramatically - today, we're
> using ten thousand or more in a few minutes.  We're trying to figure out
> just what is using so many, without any luck.  Aside from doing a lot of
> inserts (which we don't do many of), what would cause this?  Are they
> pre-allocated for transactions and not freed?
>
>    Also, when the OID's reach the limit of an int4, if I recall correctly,
> they simply wrap around, and things keep working unless you depend on
> unique OID's.  Is that correct?

That's correct.

cheers,
t.

p.s. and rumor has it that the universe will start shrinking as soon as this happens ;-)

> >    Also, when the OID's reach the limit of an int4, if I recall
correctly,
> > they simply wrap around, and things keep working unless you depend on
> > unique OID's.  Is that correct?
>
> That's correct.
>
> cheers,
> t.
>
> p.s. and rumor has it that the universe will start shrinking as soon as
this happens ;-)

    Actually, in our case, it may happen more soon than I had thought.  We
were in the tens of millions not long ago, and are now over 100 million.
At the rate we're going, we may very well be doing 5 million OID's per day
in the very near future, which would give us about 6 months to wrap
around.

   I'm not terribly worried about the wrap-around, but I would like to be
as informed as possible regarding this situation.  And since we do very
few inserts relative to our selects (probably a 1:5,000 ratio), we
probably don't need to be churning through them quite so fast.  It appears
to be pre-allocating 30 or 32 OID's per select, which in our case, is far
too many, as it's a very rare case indeed where we insert more than one
record at a time.  Is there a way to change that behavior?

steve

"Steve Wolfe" <steve@iboats.com> writes:
>    Recently, our OID usage has started to jump dramatically - today, we're
> using ten thousand or more in a few minutes.  We're trying to figure out
> just what is using so many, without any luck.  Aside from doing a lot of
> inserts (which we don't do many of), what would cause this?  Are they
> pre-allocated for transactions and not freed?

What PG version are you using?

IIRC, in pre-7.1 code, backends allocate OIDs in blocks of 32 (?? more
or less anyway); so if a backend uses one OID and then exits, you wasted
31 OIDs.  This does not happen anymore with 7.1, though.

Another possibility is that you're creating lots of temp tables --- each
one will cost you a dozen or so OIDs, depending on the number of columns.

>    Also, when the OID's reach the limit of an int4, if I recall correctly,
> they simply wrap around, and things keep working unless you depend on
> unique OID's.  Is that correct?

That's the theory, anyway.  After the wrap, you could see occasional
failures due to OID conflicts in the system catalogs --- for example, if
you try to create a table but the assigned OID duplicates some existing
table's OID, you'd get a can't-insert-duplicate-into-unique-index kind
of failure.  The solution if that happens is just to try again;
eventually you'll get an OID that doesn't conflict.  But the odds of a
conflict like that should be very low in practice.

            regards, tom lane

> What PG version are you using?
>
> IIRC, in pre-7.1 code, backends allocate OIDs in blocks of 32 (?? more
> or less anyway); so if a backend uses one OID and then exits, you wasted
> 31 OIDs.  This does not happen anymore with 7.1, though.
>
> Another possibility is that you're creating lots of temp tables --- each
> one will cost you a dozen or so OIDs, depending on the number of
columns.

   Thanks, Tom.  We are using 7.0.x, and they do appear to be used in
chunks of 32.  One more reason for me to upgrade. : )

steve

> >    Also, when the OID's reach the limit of an int4, if I recall correctly,
> > they simply wrap around, and things keep working unless you depend on
> > unique OID's.  Is that correct?
>
> That's the theory, anyway.  After the wrap, you could see occasional
> failures due to OID conflicts in the system catalogs --- for example, if
> you try to create a table but the assigned OID duplicates some existing
> table's OID, you'd get a can't-insert-duplicate-into-unique-index kind
> of failure.  The solution if that happens is just to try again;
> eventually you'll get an OID that doesn't conflict.  But the odds of a
> conflict like that should be very low in practice.

Seems we could avoid that by getting another oid and retrying.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026