Re: OID's....

"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