Skip site navigation (1) Skip section navigation (2)

JDBC caching plpgsql function errors?

From: "Jim Steinberger" <steinberger(at)dynedge(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: JDBC caching plpgsql function errors?
Date: 2004-04-30 16:34:50
Message-ID: 20040430163632.77CC722BC86@inetsrv07.digitalrealm.net (view raw or flat)
Thread:
Lists: pgsql-general
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.

Responses

pgsql-general by date

Next:From: Chris GamacheDate: 2004-04-30 16:35:39
Subject: Foreign Key ON DELETE CASCADE Performance
Previous:From: Tom LaneDate: 2004-04-30 16:34:31
Subject: Re: hugetlb feature linux 2.6 kernel

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group