Hey all – my inaugural post,
I have a Java web application on Tomcat 5 using pg73jdbc3.jar to connect to PostgreSQL 7.3.4 running on the same server (Red Hat Linux [8.0, I think]).
I have a Perl script that drops the database and rebuilds it with all of our table/function/etc. scripts. This ran without any errors – all the functions, tables, etc. were created successfully.
While testing the web application, one of the functions returned a syntax error. I went in using the psql command-line utility to fix the function manually, and this worked – I could then call the function successfully via the command-line (“SELECT foo(bar);”).
However – and here’s my problem – when testing the web application again, I still got the SQLExceptioned syntax error, even though the function was fixed.
Some more curiosities:
If I shut down Postgres and Tomcat, and then restart them, the problem is still there.
If I shut down Postgres, and test the web application, I get the expected “Connection could not be established”. Then, when restarting Postgres, I still get the syntax error message.
If I copy the function “foo”, rename it as “foo2”, and then have the web application use “foo2” instead of “foo”, then it works perfectly.
It would seem some sort of caching is going on, and it persists beyond shutting Tomcat and Postgres down. Further, it would seem that it has something to do with JDBC (I’m using PreparedStatement, by the way), or Tomcat’s connection-pooling mechanism, since if I run the very same SQL statements directly via the psql console, the functions to not return an error.
Anyone have any ideas? I /think/ I worked around this same problem a few months ago by restarting the server, but clearly I shouldn’t have to do that every time I modify a function.
Thank you!
Jim Steinberger
Dynamic Edge, Inc.