Re: FW: Java Memory Issue while Loading Postgres library

On 29/04/2010 9:23 PM, Ashish(dot)Arya(at)sungard(dot)com wrote:
> Hi Craig,
>
> Sorry for creating confusion. Let me (I work with Ambarish, the original
> author of the mail) try to be more specific now.
>
> We have a library (written in C) which helps us in doing phonetic based
> name search. We want to use this library inside a postgres DB function.
> To achieve this we wrote a small C code (we referred as wrapper class)
> which uses the library. This C code is an ECPG which is bundled as a dll
> and placed in postgres's lib dir.

OK, that makes sense - though I'm not sure your use of ecpg in that role
does.

I haven't worked with ecpg much at all, but I didn't realise it was
capable of being used as a tool for server backend functions. Are you
using EXEC SQL CONNECT TO in the ecpg code? Or is there some other way
of using ECPG embeded in a backend that I don't know about?

I don't see anything in:
http://www.postgresql.org/docs/8.4/static/ecpg.html
but I haven't gone through it in detail.

The usual way to write a PostgreSQL backend function in C is using the
server extension interfaces:

http://www.postgresql.org/docs/8.4/static/extend.html
http://www.postgresql.org/docs/8.4/static/xfunc-c.html

and, if you need to execute SQL from within your C code, the Server
Programming Interface:

http://www.postgresql.org/docs/8.4/static/spi.html

Your code is generally compiled using pgxs.

> The original postgres function is
> supposed to be called from a java program using JDBC. And the postgres
> function should call the C function of the wrapper class.

That makes sense.

> At runtime we
> observed that when the postgres DB function calls the C function of the
> wrapper class (in the dll), the java heap memory start increasing and
> reached to the max level resulted in crashing of JVM.

OK, so the earlier statement that made it sound like you were calling a
DLL from the Java runtime:

"Now the issue is that, when we make a call to this dll, it consumes a
lot of memory and this memory is getting consumed from the heap space of
the original java process causing an out of memory exception in Java."

meant nothing of the sort, and your Java code (that's running out of
memory) is really only using JDBC?

> Then we commented
> out the call to ECPG C function from postgres DB function and realized
> that everything went well.

... but the amount of data returned from your function call changed lots
too, right?

> We were surprised why the loading and execution of the ECPG is taking
> JVM memory.

I doubt it is. I suspect you're just seeing memory used by a large
result set. Consider using a cursor. See the JDBC manual on handling
large result sets.

The JVM should *not* crash if it runs out of memory due to JDBC using
too much, though. Any crash really should be reported directly to sun.
If the JVM crashes it saves some error logs and reports the crash on the
text console so you can send them off to Sun for analysis.

If you don't actually mean that the JVM crashes at all, and actually
mean "my program throws an OutOfMemoryError" ... then yes, that's the
expected behaviour when you try to use too much memory loading a big
result set. This is NOT a jvm crash, the jvm is doing exactly what it's
supposed to do. Describing this as "crashing of JVM" is very misleading
if this is actually what's happening.

--
Craig Ringer