Chris Kratz <chris.kratz@xxxxxxxxxxxxxx> writes: > We aren't sure at this point if the problem we are having has to do with the > fact that we drop and reload the test db quite often and so we are running > into OID wraparound, or if it has to do with the fact that we recently went > to 8.1 on this test machine and OID is now handled differently on reload. We did change the handling of OIDs in 8.1, specifically this patch: http://archives.postgresql.org/pgsql-committers/2005-08/msg00109.php which ensued from this discussion: http://archives.postgresql.org/pgsql-hackers/2005-08/msg00074.php Pre-8.1 versions contained a kluge that made COPY FROM ... WITH OIDS increase the OID counter to be at least the maximum of the OIDs loaded into the table. The lack of this hack is what's making the collision problem manifest almost immediately for you. However, you would have had a problem eventually anyway, because the OID counter is only 32 bits and will eventually wrap around. So the first thing to ask yourself is whether depending on OID uniqueness is a sane design decision at all. IMHO the answer is no unless (1) the tables you need OIDs for will never exceed 2^32 rows, or even reach any significant fraction of that, and (2) you aren't assuming global uniqueness of OIDs but only per-table uniqueness. (NOTE: you can create a database-wide unique identifier by combining tableoid and row OID, if necessary.) Assuming you meet those sanity checks, the way to do this in 8.1 is to create a unique index on OID for each table you need unique OIDs in. This not only is needed anyway to enforce the design assumption, but the presence of such an index triggers the code we added to 8.1 to select an unused OID. http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ddl.sgml.diff?r1=1.42&r2=1.43 regards, tom lane
