Thread: jdbc & tomcat
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
Steve, This error message is from some other object in the database that depended on the table you dropped. Most likely this is a view that selects from the table. After dropping and recreating the table you also need to drop and recreate dependent objects like views. thanks, --Barry 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 >
Is the drop/recreate being done from a stored procedure? When postgres compiles a procedure it ends up referencing it by it's OID (Object ID). When you recreate something it'll get a new OID, and so mess up the proc. The workaround is to use EXECUTE in your stored proc so it re-evals the statement. I believe the same issue can apply to triggers dependent upon the table. -Steve On Wed, 2002-08-28 at 10:14, Steve Linabery wrote: > 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
You must be executing a stored procedure. Instead of DROPing the table, TRUNCATE the table. You will have to "CREATE OR REPLACE" the procedure. JLL 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
Thank you Barry, This, I think, is the most likely scenario since I was not using a stored procedure as others have suggested. And there were views on the table. Good call. (By the way, I will use TRUNCATE if someone can assure me that this is like doing DELETE followed by VACUUM FULL on the table--these are fairly large tables being used for a data warehousing application, so a weekly VACUUM won't cut it). Regards, Steve On Wed, 2002-08-28 at 09:31, Barry Lind wrote: > Steve, > > This error message is from some other object in the database that > depended on the table you dropped. Most likely this is a view that > selects from the table. After dropping and recreating the table you > also need to drop and recreate dependent objects like views. > > thanks, > --Barry > > > 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 > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Steve Linabery, sysadmin/developer World Cycling Productions
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 >