Обсуждение: strange error
I am receiving the following error and cannot understand what it means.
Please let me know if this makes sense to you.
ERROR: could not open relation 1663/3364852/7973197: No such file or
directory
CONTEXT: SQL statement "create temporary table tmpcust as select a.* from
qry_Customers_Country a"
PL/pgSQL function "buildsrtemptables" line 34 at execute statement
The code I am using checks if a temporary tables exists, if it exists, it
drops 3 temp tables. The function continues on and creates the temporary
tables. This ensures that if my application calls the function twice in the
same connection, it doesn't get the error. What is strange about the error
is that it does not happen everytime. I can call the function and it creates
the tables. I then call it again and I get the error, and the tables still
exist. Then I call the function again and it drops and rebuilds the tables
with no error. But it is not always every other one, sometimes it is every 3
calls.
Below is the code:
create or replace function buildsrtemptables(uidparm int, prodparm int,
cntparm int, custparm int, orderparm int, startdateparm date, monthsparm
int) returns void
--prodparm 0=not enabled, 1=useprod, 2=useprodtype
--cntparm 0 = not enabled, 1 = usecountry, 2= useregion
--custparm 1=list, 2=all
--orderparm 0=not enabled, 1=3months, 2=1yr, 3=2yr, 4=3yr, 5=all, 6=defined
--if orderparm=6 then
--startdate date
--months int
as
$$
declare
sqltmpcust citext;
sqltmporder citext;
tempschema citext;
begin
select current_schemas[1] into tempschema from current_schemas(true);
if tempschema like 'pg_temp%' then
if exists(select * from pg_class a join pg_namespace b on
a.relnamespace=b.oid and b.nspname=tempschema where relname='tmpprod') then
execute 'drop table tmpprod';
execute 'drop table tmpcust';
execute 'drop table tmporder';
end if;
end if;
execute 'create temporary table tmpprod(like Products)';
if prodparm=0 then
execute 'insert into tmpprod select * from products';
elsif prodparm=1 then
execute 'insert into tmpprod select a.* from products a join set_prod b on
a.productid=b.id and b.setz AND b.uid=' || uidparm;
else --prodparm = 2
execute 'insert into tmpprod select a.* from products a join set_prodtype
b on a. producttypeid=b.id and b.setz and b.uid=' || uidparm;
end if;
sqltmpcust = 'select a.* from qry_Customers_Country a';
if custparm=1 then
sqltmpcust=sqltmpcust || ' join set_cust b on a.customerid=b.id and b.setz
and b.uid=' || uidparm;
end if;
if cntparm<>0 then
if cntparm=1 then
sqltmpcust=sqltmpcust || ' join set_country c on c.id=a.countryid and
c.setz and c.uid=' || uidparm;
else --cntparm=2
sqltmpcust=sqltmpcust || 'join countries c on c.countryid=a.countryid
join set_region d on d.id=c.regionid and d.setz and d.uid=' || uidparm;
end if;
end if;
execute 'create temporary table tmpcust as ' || sqltmpcust;
sqltmporder='select a.* from quotations a join tmpcust b on
a.customerid=b.customerid
join quotationitems c on c.quotationid=a.quotationid join tmpprod d on
d.productid=c.productid';
if orderparm not in (0,5) then
sqltmporder=sqltmporder || ' where orderdate >= ';
if orderparm = 1 then
sqltmporder=sqltmporder || 'current_date - interval ''3 months''';
elsif orderparm=2 then
sqltmporder=sqltmporder || 'current_date - interval ''1 year''';
elsif orderparm=3 then
sqltmporder=sqltmporder || 'current_date - interval ''2 years''';
elsif orderparm=4 then
sqltmporder=sqltmporder || 'current_date - interval ''3 years''';
elsif orderparm=6 then
if startdateparm is null then
sqltmporder=sqltmporder || 'current_date - interval ''' || monthsparm ||
' months''';
else --startdateparm
sqltmporder=sqltmporder || '''' || startdateparm || '''';
end if;
end if;
end if;
execute 'create temporary table tmporder as ' || sqltmporder;
return;
end;
$$ language 'plpgsql';
"Sim Zacks" <sim@compulab.co.il> writes:
> I am receiving the following error and cannot understand what it means.
> Please let me know if this makes sense to you.
> ERROR: could not open relation 1663/3364852/7973197: No such file or
> directory
> CONTEXT: SQL statement "create temporary table tmpcust as select a.* from
> qry_Customers_Country a"
> PL/pgSQL function "buildsrtemptables" line 34 at execute statement
That's pretty darn odd. Is qry_Customers_Country a temp table, plain
table, view, or what? What PG version is this exactly, running on what
platform? When the error happens, can you find any row in pg_class with
relfilenode equal to the third number mentioned (7973197 above), and if
so what table is it?
regards, tom lane
qry_Customers_Country is a view.
There is no row in pg_class with relfilenode=7973197 either when it
works or after I get the error.
The version (from select version()) is
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1,
ssp-3.3.2-3,pie-8.7.7.1)
Thank You
Sim Zacks
________________________________________________________________________________
"Sim Zacks" <sim@compulab.co.il> writes:
> I am receiving the following error and cannot understand what it means.
> Please let me know if this makes sense to you.
> ERROR: could not open relation 1663/3364852/7973197: No such file or
> directory
> CONTEXT: SQL statement "create temporary table tmpcust as select a.* from
> qry_Customers_Country a"
> PL/pgSQL function "buildsrtemptables" line 34 at execute statement
That's pretty darn odd. Is qry_Customers_Country a temp table, plain
table, view, or what? What PG version is this exactly, running on what
platform? When the error happens, can you find any row in pg_class with
relfilenode equal to the third number mentioned (7973197 above), and if
so what table is it?
regards, tom lane
Sim Zacks <sim@compulab.co.il> writes:
> The version (from select version()) is
> PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1,
ssp-3.3.2-3,pie-8.7.7.1)
Gentoo eh? When you run a bleeding-edge distribution, sometimes you get
nicked :-(. I'm betting this is some weird kernel bug. You might try
another distro with tighter quality control.
regards, tom lane
I tried it on a test RedHat server
PostgreSQL 8.0.2beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
and the problem didn't occur. It is a slightly newer version, though,
so it doesn't prove anything. But the Gentoo is my production machine,
so I guess I'll have to figure out a way around it. I'll post again if
if I can figure out exactly where the problem is occurring.
Thank You
Sim
________________________________________________________________________________
Sim Zacks <sim@compulab.co.il> writes:
> The version (from select version()) is
> PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1,
ssp-3.3.2-3,pie-8.7.7.1)
Gentoo eh? When you run a bleeding-edge distribution, sometimes you get
nicked :-(. I'm betting this is some weird kernel bug. You might try
another distro with tighter quality control.
regards, tom lane
On Tue, 2005-10-11 at 07:55 +0200, Sim Zacks wrote: > I tried it on a test RedHat server > PostgreSQL 8.0.2beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) > and the problem didn't occur. It is a slightly newer version, though, > so it doesn't prove anything. But the Gentoo is my production machine, > so I guess I'll have to figure out a way around it. I'll post again if > if I can figure out exactly where the problem is occurring. You were doing the creation of the temporary table in a function. Did it go wrong on the very first execution in a session or only on the second and subsequent executions? If the latter, try using EXECUTE in the function, so that the statement is reevaluated each time. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
Thank you for your reply, the procedure code (which I posted in the original post) already utilizes the Execute statment. Here is the weirdest thing. The error only occurs in the query window that I originally wrote the code in. If I open another query window (I'm using PGAdmin III), I can run the code a number of times with no error. (In short there is no problem restraining me from what I am doing, it just threw me for a loop for a while.) Sim ________________________________________________________________________________ On Tue, 2005-10-11 at 07:55 +0200, Sim Zacks wrote: > I tried it on a test RedHat server > PostgreSQL 8.0.2beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) > and the problem didn't occur. It is a slightly newer version, though, > so it doesn't prove anything. But the Gentoo is my production machine, > so I guess I'll have to figure out a way around it. I'll post again if > if I can figure out exactly where the problem is occurring. You were doing the creation of the temporary table in a function. Did it go wrong on the very first execution in a session or only on the second and subsequent executions? If the latter, try using EXECUTE in the function, so that the statement is reevaluated each time. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html