all I can find out about this bug is that it was reported back in
October 2001, all the lists make no mention of it after that. posts
from Tom indicate that 7.1+ do not have an issue with running out of
OID's yet I still have a problem. I run a fairly large database that
gets updated via temp tables several times a day, as such I passed the
int4 limit for an OID yesterday. Now supposedly they are just suppose
to roll over the top and continue working, however, in my situation it
paralyzes the database usefulness. The database itself continues to run
just fine however I am using a combination of pg_dumps and restores to
distribute the data to a number of other servers. Since rolling over
the int4 limit pg_dump gives the following error:
$ pg_dump news -v -t admin
-- saving database definition
-- last builtin oid is 18539
-- reading user-defined types
-- reading user-defined functions
-- reading user-defined aggregates
-- reading user-defined operators
-- reading user-defined tables
getTables(): SELECT (for PRIMARY KEY) failed on table hp_photo_type.
Explanation from backend: ERROR: dtoi4: integer out of range
it used to fail on other tables so I removed all the offending tables
(so I could at least make a backup) dropped the database and recreated
it then imported all the data. No help, now the rest of the tables are
complaining. the table listed above has the following OID's
2204178165
2204178166
2204178167
2204178168
2204178169
this is the table structure
\d hp_photo_type
Table "hp_photo_type"
Attribute | Type | Modifier
---------------+-----------------------+----------
hppt_key | character(2) | not null
hppt_typename | character varying(50) |
Indices: hp_photo_type_hppt_key,
hp_photo_type_pkey
I have even tried to create a brand new DB and insert some data then
dump, I get the same error.
At this point I am going to re-install postgres so that I can get some
new OID's but I am concerned that I will be right back in the same place
in 6 months.
I am running V 7.1.1