Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Postgres JDBC trouble Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Postgres JDBC trouble" Watch "Postgres JDBC trouble" New topic
Author

Postgres JDBC trouble

Sid Garg
Greenhorn

Joined: Apr 03, 2005
Posts: 3
Howdy JRers,

I am working on a software which can be installed with both Oracle and Postgres as the backend. I have two questions:

1. For the next release we have to upgrade the database. So I wrote this program in Java using JDBC to do it. Cant do it in SQL (see seond question) Anyway, the program runs fine on Oracle. It took about 15 minutes on a dual processor machine. But the trouble is with Postgres JDBC driver. The first problem I run into is that I get java.lang.outOfMemory error. I think postgres JDBC driver returns the whole query result (huge in this case) in one go whereas Oracle JDBC driver returns a cursor. Any insight into this will be great.
Then I increased memory allocation for JVM. Still the query is probably going to take 1 week to complete on a faster dual processor machine!! (i think it will be 1 week - the query is still running in background....)
Any clues about performace issues with postgres JDBC driver?

2. I tried to run the same query in SQL:


But I keep getting this error: numeric field overflow. Any idea what I am doing wrong. numericaldata is FLOAT8 and data is VARCHAR(4000)

I think I covered all the details. If I missed anything, please let me know. Any input is appreciated.

Thanks
Sid
Sid Garg
Greenhorn

Joined: Apr 03, 2005
Posts: 3
Regarding second question, I am able to do the conversion now



Instead of 999D9 I am passing 99999D999999. So to break this statement someone has to add a record with a 7 digit number. Dumb and dumber!!

Obviously I am not doing something right. Any inputs??

-Sid
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

Sid,
"similar to" is not standard SQL. Does postgres recognize this clause? You can check by doing select count(*) from table where data similar to 'test'

No query should take a week, so you should take a look at tuning it. Looking at the execution plan may also help with the out of memory exception. In particular, make sure the query is using an index on the data field. (If there isn't already one, it may be faster to add it and then do the query.)

And welcome to JavaRanch!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sid Garg
Greenhorn

Joined: Apr 03, 2005
Posts: 3
Jeanne, Thanks for your reply.

Yep, "similar to" is not standard SQL. But it works fine in Postgres. You can pass a regex after similar to and it works like a charm.

Finally I was able to do the update using the following SQL query:



Took about 54 seconds.

About the JDBC - I thought earlier that it might be my code - but the code runs fine if I use Oracle - less than an hour. But in case of Postgres its taking too long - not a week as I said earlier but still would be about 24 hours.

So I guess Postgres JDBC driver is the bottleneck.

-Sid
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

Sid,
Thanks for posting the query!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Postgres JDBC trouble
 
Similar Threads
Tutorial PostgreSQL 8.3 on Glassfish V2.1 for ConnectionPool in Netbeans 6.5.1, WindowsXP (Post 3)
Oracle JDBC Connectivity problem
Probs with Sybase
DB2 Stored procedure build error
Strange behaviour when using Oracle TO_CHAR to get day of week