Thread: Odd error message
Earlier today I created a function which does some database lookups based on the input parameters. I ran a query using it and thought it might need some performance enhancements, so I created an index on two of the columns used within the function to see if that would make it run faster. It didn't, so I dropped that index and created a different one, adding a third column to the index. I then got the following error message: WARNING: Error occurred while executing PL/pgSQL function uscf_status_on WARNING: line 8 at select into variables ERROR: Relation 3912941 does not exist Recreating the function got rid of the error message, though I didn't get the performance improvement I was looking for. When I delete that new index (which didn't help the performance, either), the error message returns. Recreating the function gets rid of it again. What is going wrong to produce that error message? (Running 7.3.3) -- Mike Nolan
On Sunday 15 Jun 2003 9:41 pm, nolan@celery.tssi.com wrote: [snip] > It didn't, so I dropped > that index and created a different one, adding a third column to the index. > > I then got the following error message: > > WARNING: Error occurred while executing PL/pgSQL function uscf_status_on > WARNING: line 8 at select into variables > ERROR: Relation 3912941 does not exist > > Recreating the function got rid of the error message, though I didn't > get the performance improvement I was looking for. PL/pgsql functions are "compiled" on their first call and plans are fixed at this time. So - if a plan relies on something you delete there are problems. I tend to keep my functions in one or more text-files ready to be dropped/recreated when I make schema changes during development. I believe that the TCL/Perl procedural languages are interpreted each time they are run so should not suffer this difficulty. -- Richard Huxton