Обсуждение: 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