Обсуждение: 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?
2) How are the temp tables being created? For instance is there an ON COMMIT
DROP clause?
3) How are the stored procedures being called?

>
> Andy Nykolyn
> Northrop Grumman

--
Adrian Klaver
adrian.klaver@gmail.com

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.klaver@gmail.com

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.klaver@gmail.com

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?

It is a straight CREATE TEMP TABLE.  It does not happen on the same procedure every time.  It happens on different
proceduressometimes they are nested.  These store procedures have been working for many years the same way.  Only
recentlyhas this error been intermittently occurring on temp tables.  Sometimes it occurs on the create, sometimes it
occurson an insert, update or delete on a temp table 

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

There is no database pooler and there are never any other errors in the log at the time of this error.

"Nykolyn, Andy (AS)" <andrew.nykolyn@ngc.com> writes:
> It is a straight CREATE TEMP TABLE.  It does not happen on the same procedure every time.  It happens on different
proceduressometimes they are nested.  These store procedures have been working for many years the same way.  Only
recentlyhas this error been intermittently occurring on temp tables.  Sometimes it occurs on the create, sometimes it
occurson an insert, update or delete on a temp table 

8.4.what exactly, and did you update versions around the time this
started happening?  I'm worried that this may represent a
newly-introduced bug.  Can you provide a self-contained test case?
It doesn't matter if it only fails occasionally, as long as we can
keep running it till it does fail.

            regards, tom lane

"Nykolyn, Andy (AS)" <andrew.nykolyn@ngc.com> writes:
>> 8.4.what exactly, and did you update versions around the time this
>> started happening?  I'm worried that this may represent a
>> newly-introduced bug.  Can you provide a self-contained test case?
>> It doesn't matter if it only fails occasionally, as long as we can
>> keep running it till it does fail.

> It is version 8.4.1 and it has been that for almost 3 years.

8.4.1?  Well, the *first* thing you ought to do is update to 8.4.10
so we can see if this represents an already-fixed bug.  In a quick
look through the release notes I see at least two possibly related
bug fixes, and in any case there are a slew of known crash and data
corruption bugs you are vulnerable to.

            regards, tom lane

On Wednesday, February 01, 2012 4:55:46 am Nykolyn, Andy (AS) wrote:

>
> Tom,
>
> It is version 8.4.1 and it has been that for almost 3 years.  I have
> attached a script that will create and load the tables as well as the
> store procedure required to run the case that sometimes causes this error.
>  As I stated it happens on different types of DML statements but always on
> a temp table.  The last line of the script contains the call to the stored
> procedure where the error had occurred most often when it occurred.  The
> line it usually happened on was the "create temporary table t_bitgrid as
> select * from bitgrid"  Please let me know if you need any more
> information.  Greatly appreciated.

Well I have been running the function using the data you sent against both an
8.4.1 and 8.4.9 instance on and off a good part of the day. At this point we are
talking many thousands of runs. In either case I have not seen an error. So
either I am incredibly lucky(I wish) or something is going on that is unique to
your environment.  At this point I am not quite where to go other then say,  do
what Tom recommends, upgrade to 8.4.10.


>
> Andy Nykolyn
> Northrop Grumman

--
Adrian Klaver
adrian.klaver@gmail.com

Well I have been running the function using the data you sent against both an
8.4.1 and 8.4.9 instance on and off a good part of the day. At this point we are
talking many thousands of runs. In either case I have not seen an error. So
either I am incredibly lucky(I wish) or something is going on that is unique to
your environment.  At this point I am not quite where to go other then say,  do
what Tom recommends, upgrade to 8.4.10.

Thanks for taking a look at it.  I will be upgrading to 8.4.10 and I will keep you posted.