[Pljava-dev] Is it safe to use multi-threaded PL/Java in single-threaded postgres process?

In some cases the time spent in the actual call overhead is dramatically reduced but far from always. It does depend on
several factors. Here are a few:

1. A machine with multiple cores may well be able to run the client and server processes more or less 100% in parallel.
The flow of execution is of course sequential but time spent in context switches will never become an issue.

2. If network latency is very low, then the benefit of using stored procedures for the purpose of minimizing the number
of calls between client and server is minimal. In some cases it's even better to avoid such procedures since you get a
better load balance by executing the logic in the client. Context switching is not an issue here either since the
processes run on different CPUs.

3. For a test like yours, the time it takes to execute the actual statement is important. The execution may be so slow
that the overhead imposed by the network call becomes negligible. A test using a lightweight "SELECT 1" instead of the
heavier "UPDATE testtable" would reveal such issues.

4. The complexity of the function. Let's assume that you implement a new custom datatype. The code associated with that
datatype may potentially be complex and evaluate millions of times when you use it in large tables. Data types affect
sorting and indexing and may also play a role in the data integrity rules on the database and moving that type of
responsibility to the client code is often less than ideal. Complex functions written in Java have very good performance.

Items 1-3 are not specific to PL/Java in any way. They apply regardless of procedure language.

- thomas

On 2014-04-14 00:28, MauMau wrote:
> From: "Thomas Hallgren" <thomas at tada.se>
>> Interesting. What does your call into PL/Java (i.e. the function) look
>> like? How often do you call it (I assume just once)?
>
> Thank you for your response. I did:
>
> 1. Compiled BatchPL.java with JDK6, then ran "jar cf batch.jar
> BatchPL.class".
>
> 2. Register the jar file with:
> SELECT sqlj.install_jar('file:///some/dir/batch.jar', 'batch', false);
>
> 3. Create a Java stored function with:
> CREATE FUNCTION batch(integer) RETURNS void
> AS 'BatchPL'
> LANGUAGE java;
>
> 4. Run "SELECT batch(10000);" from psql.
>
>
> So, I understand that the number of calls from the Java function to PL/Java
> is 10000. I hoped PL/Java reduces the function execution time dramatically
> (by half or more) to make my colleagues get interested in PL/Java.
>
> Regards
> MauMau
>
>