Solving the OID-collision problem - Mailing list pgsql-hackers

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

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.)

I seem to recall having thought of this idea before, and having rejected
it as being too much overhead to solve a problem that occurs only rarely
--- but in a quick test involving many repetitions of
create temp table t1(f1 int, f2 int);drop table t1;

the net penalty was only about a 2% slowdown on one machine, and no
measurable difference at all on another.  So it seems like it might
be worth doing.

Comments?
        regards, tom lane


*** src/backend/catalog/heap.c.orig    Thu Jul 28 16:56:40 2005
--- src/backend/catalog/heap.c    Wed Aug  3 19:20:22 2005
***************
*** 187,192 ****
--- 187,229 ----  * ---------------------------------------------------------------- */  
+ /*
+  * Quick hack to generate an OID not present in the specified catalog
+  */
+ static Oid
+ safe_newoid(Oid catalogId, Oid oidIndexId)
+ {
+     Oid            newOid;
+     Relation    catalogRelation;
+     SysScanDesc scan;
+     ScanKeyData key;
+     bool        collides;
+ 
+     catalogRelation = heap_open(catalogId, AccessShareLock);
+ 
+     do
+     {
+         newOid = newoid();
+ 
+         ScanKeyInit(&key,
+                     ObjectIdAttributeNumber,
+                     BTEqualStrategyNumber, F_OIDEQ,
+                     ObjectIdGetDatum(newOid));
+ 
+         scan = systable_beginscan(catalogRelation, oidIndexId, true,
+                                   SnapshotNow, 1, &key);
+ 
+         collides = HeapTupleIsValid(systable_getnext(scan));
+ 
+         systable_endscan(scan);
+     } while (collides);
+ 
+     heap_close(catalogRelation, AccessShareLock);
+ 
+     return newOid;
+ }
+ 
+  /* ----------------------------------------------------------------  *        heap_create        - Create an
uncatalogedheap relation  *
 
***************
*** 227,233 ****      * Allocate an OID for the relation, unless we were told what to use.      */     if
(!OidIsValid(relid))
!         relid = newoid();      /*      * Decide if we need storage or not, and handle a couple other
--- 264,270 ----      * Allocate an OID for the relation, unless we were told what to use.      */     if
(!OidIsValid(relid))
!         relid = safe_newoid(RelationRelationId, ClassOidIndexId);      /*      * Decide if we need storage or not,
andhandle a couple other
 
***************
*** 714,720 ****     new_rel_oid = RelationGetRelid(new_rel_desc);      /* Assign an OID for the relation's tuple type
*/
!     new_type_oid = newoid();      /*      * now create an entry in pg_class for the relation.
--- 751,757 ----     new_rel_oid = RelationGetRelid(new_rel_desc);      /* Assign an OID for the relation's tuple type
*/
!     new_type_oid = safe_newoid(TypeRelationId, TypeOidIndexId);      /*      * now create an entry in pg_class for
therelation.