Re: OID's....

"Steve Wolfe" <steve(at)iboats(dot)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