Postgres JDBC trouble

[Sid Garg]

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:

  
update table set numericaldata = to_number(data, '999D9') ;
where data IS NOT NULL 
AND data similar to '[0-9.]+';

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]

Regarding second question, I am able to do the conversion now

update table set numericaldata = to_number(data, '99999D999999') ;
where data IS NOT NULL 
AND data similar to '[0-9.]+';

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]

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!

[Sid Garg]

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:

  
update table set numericaldata = to_number(data, '999999999999999999999999999D9999999999999999999999999999')
where data IS NOT NULL
AND UPPER(data) = LOWER(data)
AND ( trim(data) similar to '(^(-|[0-9])[0-9]*[.][0-9]*)'
OR trim(data) similar to '^(.)[0-9]+');

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]

Sid,
Thanks for posting the query!