Re: Solving the OID-collision problem

Tom Lane <tgl <at> sss.pgh.pa.us> writes:
>
> I was reminded again today of the problem that once a database has been
> in existence long enough for the OID counter to wrap around, people will
> get occasional errors due to OID collisions, eg
>
> http://archives.postgresql.org/pgsql-general/2005-08/msg00172.php
>

I'm a coworker of the original reporter. I tracked down the cause of the toast
table unique constraint violation to the use of OIDs as chunk_id. After the OID
wraparound, it tried to use an already used chunk_id. That table has lots of
toast records which greatly increases the probability of a collision for the
current section of the OID counter.

> Getting rid of OID usage in user tables doesn't really do a darn thing
> to fix this. It may delay wrap of the OID counter, but it doesn't stop
> it; and what's more, when the problem does happen it will be more
> serious (because the OIDs assigned to persistent objects will form a
> more densely packed set, so that you have a greater chance of collisions
> over a shorter time period).
>
> We've sort of brushed this problem aside in the past by telling people
> they could just retry their transaction ... but why don't we make the
> database do the retrying? I'm envisioning something like the attached
> quick-hack, which arranges that the pg_class and pg_type rows for tables
> will never be given OIDs duplicating an existing entry. It basically
> just keeps generating and discarding OIDs until it finds one not in the
> table. (This will of course not work for user-table OIDs, since we
> don't necessarily have an OID index on them, but it will work for all
> the system catalogs that have OIDs.)
>

This will also be needed for toast tables. They have the necessary index.

- Ian