Re: Could not create relation: File exists error

Tom Lane wrote:
> 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
>
>
Thanks Tom - that seems to be it. OIDs on each table were up in the
4.29496 billion range and two tables had max OIDs just exactly prior to
the error message filename IDs. Updating PG is not an option for now,
but I can drop the OID field as you suggest. We actually ran into
another OID max issue with a previous instance of this DB and so have a
seq field to supply a unique ID in our mapping application instead of
using OID. I didn't realize that our OID field was even still around
'til now.

I ran my CLUSTER and REINDEX commands on the tables I dropped the OID
from and they ran smoothly. Will keep the list posted if my nightly
maintenance acts weird, but I think this will fix it.

Thanks so much for your advice and time solving this - I and many others
on the list really appreciate your efforts...

Jesse

--

Jesse Cleary
Department of Marine Sciences
UNC Chapel Hill

334 Chapman Hall
(919) 962-4987

jcleary(at)email(dot)unc(dot)edu