this sounds like a caching thing where the postgres, or the jdbc
connection, has cached the name of the table under an OID. so when you
drop the table and recreate it its using the old OID, not the new one
hence the odd-sounding error. i had the same problem when i was dropping
functions and recreating them with the same name (CREATE OR REPLACE
FUNCTION fixes this).
i don't know how to tell postgres the recalculate/reread the cache stuff.
why not just schedule a vacuum to run once a day/week ? easy enough to do
and it would solve your issue.
toby
On Wed, 28 Aug 2002, Steve Linabery wrote:
> So I'm using postgresql 7.2, redhat linux 7.1, jakarta tomcat 4.x, j2sdk
> 1.4.
>
> I have a jsp that instantiates a bean that creates a Connection and
> queries a table called dw_adcosts.
>
> This table is updated nightly with data from another database. Until
> recently, this update was done with a DELETE followed by multiple
> INSERTs. This eventually caused performance problems since I wasn't
> doing a VACUUM after the DELETE, so I decided to DROP the table and
> re-CREATE it each time (I already had meta-info about the table stored
> in an xml document, so re-creating it wasn't a problem).
>
> THIS is the problem: Since I first dropped the table, my jsp doesn't
> work. I suspect this is not a postgresql problem, but I'm hoping
> someone has some ideas anyway. When I try to access the jsp, the bean's
> Connection throws this exception:
> java.sql.SQLException: ERROR: Relation "dw_adcosts" with OID 597084 no
> longer exists
>
> I thought perhaps this was something weird with the pgjdbc driver, so I
> got the shiniest, newest version. Restarted Tomcat. Still get the error.
>
> I can "see" the table just fine using psql.
>
> If I can just get the jsp working again, I think I'll just TRUNCATE the
> table from now on...but my problem is that even with restarting tomcat I
> get this error. I know jsp's get compiled, but the bean creates a new
> Connection each time its
> "getTheOutputThatTheJspNeedsOrSomeSimilarMethodName()" method is called.
>
> Anyone have any thoughts on this?
>
> Thank you,
> Steve Linabery
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
