Subject: [Pljava-dev] pljava concurrency

From: jurka at ejurka.com (Kris Jurka)
Date: Thu, 23 Oct 2008 10:22:49 -0700

Peter Mengaziol wrote:
> In my searches on the web I once came across a document that
> described the Backend locking that pl/java used. I cannot find it
> anymore. Can you point me to it?

I'm not sure specifically what you're referring to.

> I have been running pl/java under MacOSX and I have a question that I
> was trying to research. That above document  said that pljava locked
> the backend and that implied only a single pljava 'task' could run at
> a time. I am trying to port some rather time consuming tasks from
> another DB and it seems that pljava queues them up. Is this correct?
> 

A given pl/java function can be multi-threaded, but only one of those 
threads may execute a query at a time.  To get concurrency you need a 
separate connection and function execution.  That's not a separate 
connection to jdbc:default:connection, but another real connection that 
starts up another backend.

Kris Jurka

---

From: jurka at ejurka.com (Kris Jurka)
Date: Thu, 23 Oct 2008 12:52:13 -0700

Peter Mengaziol wrote:
> 
> I'm evaluating Postgresql (via EnterpriseDB) and I need to be sure
> that separate pljava requests (e.g., called from cron) that overlap
> in execution won't contend. I have some java procs that take a long
> time (read files, parse them, stuff them into a db) and I'd like to
> be assured that the java sprocs themselves won't block one another.

The fact that they are java functions doesn't imply any additional 
blocking, but you are still subject to the constraints of the database 
regarding holding and waiting for locks.  This may be complicated by the 
fact that postgresql functions are not true stored procedures and 
therefore cannot commit transactions to release locks.  All locks are 
held through function exit until the enclosing transaction ends.

> I have been getting transaction errors since I am using cursor code
> inherited from the other database. Can I assume that nested cursors
> are not prohibited by the pljava environment? I assume that nested
> cursors are allowed in other PG languages...
> 

Nested cursors are legal, but be aware that some databases and jdbc 
drivers allow the following while the spec and pljava do not:

ResultSet rs1 = stmt.executeQuery(sql);
while (rs1.next()) {
   ResultSet rs2 = stmt.executeQuery(sql2);
}

The second statement execution will close the first ResultSet.  To do 
this you need to create a second independent Statement.

Please also keep replies on the list, so that others may contribute to 
or benefit from the discussion.

Kris Jurka