Search Postgresql Archives

strange error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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';



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux