Re: SQL Exception Relation xxx does not exist

On Wed, 21 Jan 2004, Alex wrote:

> Kris,
> thanks for the reply. I dont actually use temp tables in the function
> (not that I know of) but I did truncated and reloaded a few tables incl.
> recreating indices the previous day, however the line no. indicated in
> the serverlog does not point to these sql calls.
>
> I am now re-starting tomcat every night which is not a bad thing anyway
> but still am wondering what the real reason could be.
>

The reason is probably the recreation of the indexes. When a plpgsql
procedure is first executed it takes the queries in it, plans them, and
saves that query plan so that every time afterward the procedure just uses
the stored plan. The problem is that one of the plans depended on one of
the indexes. When the index was dropped the procedure tried to run a plan
that was no longer valid, generating the error you saw. Restarting tomcat
closes and reopens the connection to the database, so the query in the
procedure gets replanned to use the new index and things run smoothly.

Kris Jurka