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

On Tuesday, January 31, 2012 9:42:07 am Nykolyn, Andy (AS) wrote:

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

Temp table in PL/pgSQL was improved in 8.3 to handle this case, so you should be
covered.

http://www.postgresql.org/docs/8.4/interactive/release-8-3.html

"
Automatically re-plan cached queries when table definitions change or statistics
are updated (Tom)

Previously PL/pgSQL functions that referenced temporary tables would fail if the
temporary table was dropped and recreated between function invocations, unless
EXECUTE was used. This improvement fixes that problem and many related issues.
"
>
> 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.

Are you using EXECUTE or doing a straight CREATE TEMP TABLE ...?
Maybe a sample with sensitive info changed?
The insert you mention happens in the same procedure or is there a nested
procedure?

>
> 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.

Is there a database pooler in the mix?
Are there any other errors in the logs at the same time that might pertain?

>
> > Andy Nykolyn
> > Northrop Grumman

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