Re: Could not create relation: File exists error

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Jesse Cleary <jcleary(at)email(dot)unc(dot)edu> writes:
> This script has been running successfully for several months (70-90 min each night).  Out of the blue I'm now getting the following error message after each psql command, except the last vacuum full analyze command:
> ERROR:  could not create relation 1663/835021/4294967254: File exists

> Postgres 8.0.8 with

After looking back at the 8.0 code I'm pretty sure I know approximately
what is happening, though not the exact details.  Somehow,
CheckMaxObjectId is firing and forcing the OID counter up to
almost-maximum, which constrains the numbers that REINDEX and CLUSTER
try to select as file names.  And there wasn't any code in 8.0 to
recover from a chance filename collision, hence the error.

A fairly likely cause for this is that one of the tables being
CLUSTERed has OIDs and there is a row with an almost-maximum OID in
there --- when the row is copied across to the newly clustered table,
its OID would be shown to CheckMaxObjectId.  So every night, the OID
counter would have the exact same value just after the CLUSTER step,
and subsequent reindexes would always try to pick the same filenames
as they did before.

We fixed that whole horrid mess in 8.1, so really the best answer
would be to update to 8.1 or 8.2.  If you can't do that, are you
actually using the OIDs in these tables?  If not, "ALTER TABLE SET
WITHOUT OIDS" would be a good and quick fix.  Failing that, I think
you need to find the high-numbered OIDs and get rid of them
(just delete and reinsert the rows should work).

			regards, tom lane