Re: EXT :Re: Intermittent occurrence of ERROR: could not open relation

Subject: EXT :Re: [GENERAL] Intermittent occurrence of ERROR: could not open relation

On Tuesday, January 31, 2012 8:08:24 am Nykolyn, Andy (AS) wrote:
> I have been running PostgreSQL 8.4 on Windows Server 2003 Service Pack 2
> for almost 3 years. I have recently been intermittently getting the error
> "ERROR: could not open relation base/85599592/121526263: No such file or
> directory". It only appears to be happening during the execution of
> stored procedures where an insert into a temp table is performed. The
> error goes away after the user reconnects with a new session.
>
> This database periodically gets copied to a PostgreSQL 8.4 server on a
> Linux RedHat 4.5 machine where the same error also has started
> intermittently occurring; again it only appears to happen during the
> execution of stored procedures where an insert into a temp table is
> performed.
>
> Does anybody have an idea what might be happening? Are there any config
> settings for temp tables that might help prevent this error? Any help
> would be much appreciated. Thank you.

Some questions first:
1) What language are you using in the stored procedures?
The stored procedures are in Pl/Pgsql

2) How are the temp tables being created? For instance is there an ON COMMIT
DROP clause?
They are created at the start of stored procedure and dropped at the end. There is no ON COMMIT
DROP clause.

3) How are the stored procedures being called?
They are usually called from a java client but I have also seen this issue when called from a PgAdmin session screen.
>
> Andy Nykolyn
> Northrop Grumman

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com