Execute Java Code at PostgreSQL startup to "Get Things Ready"

[TasMot]

I am developing with Drools (A Business Rules Management System). The startup of each rule service is a two part process. The first step is to instantiate a "container". This step does a lot of the heavy lifting to get a rule service ready. Then, the second step is to get a "session" and utilize the rule service to make a decision based on the rules. What I want to do is to instantiate the container during the startup of PostgreSQL. Then when an insert/update/delete trigger is executed, get a session and make a decision.

Instantiating a container is a heavy weight process and is expensive and time-consuming to do for each trigger. Also, in some cases, the container maintains a state that is meaningful to the decision making process.

At this point, I have gotten a trigger to invoke the rule service but each time it has to instantiate a container, get a session, make a decision and then throw everything away when the Java Trigger Class goes out of scope.

Since everything is invoked via Java, I was wondering if there is any way to invoke a Java Class during the startup of PostgreSQL so that one or more containers can be instantiated and made available to trigger executions. I'm sure that there are more details needed to actually make this work but I'm curious about whether the general concept is available first.
Thanks,
Tom

[jcflack]

There are a couple of options, one of which should be easy to do now, one of which would be harder.

PostgreSQL currently works in a one-backend-process-per-client-connection model, and PL/Java currently works in a JVM-per-backend-process model. After you make a new connection (which spawns a new PostgreSQL backend process), a JVM gets started in that process on the first use of anything in PL/Java. That JVM lasts for as long as that backend process, therefore as long as that client connection.

If it would meet your needs to instantiate the rule service just once in each backend process, that should be easy to do. Your trigger functions could just use a static initialized-on-demand reference to a rule service, perhaps using the holder pattern.

This would not result in a single rule service shared across client connections. Each client connection would have its own instance. That could be enough if your application uses long-lived connections and if the state that the rule service maintains reflects just the sequence of operations over one connection, independently of operations on other connections. It would probably be too slow if the pattern is lots of quick ephemeral connections, and not satisfactory if the rule service's state depends more globally on operations across connections.

If the way Drools works (I haven't looked) is that the rule service container is really a separate OS process, and a 'session' communicates with it over IPC, then you may well want to start that process once and for all, and have your PL/Java code connect to it (using whatever method Drools uses to locate and connect to an existing service).

if you wanted that, you might simply launch the container from OS scripting at the same time the postmaster starts. You could make an OS service like systemd responsible for making sure it is running, or gets restarted if it exits.

Or, you could launch it in a PostgreSQL background worker launched at postmaster start. That would make postmaster responsible for starting it, and optionally restarting it if it falls over. You can't currently set up a background worker all at the PL/Java level (future support is planned); for now, some C would have to be written to use that approach.

Finally, you could once again use on-demand initialization from your PL/Java trigger code, only with some kind of coordination so that only the first use in the first database session creates the service process, and the information needed to connect to it is somehow shared for subsequent connections to use. For this last case, where PL/Java would launch another process from the JVM in an ordinary database connection backend, you would probably end up needing to add a suitable java.io.FilePermission("...", "execute") in pljava.policy where the "..." is the pathname of whatever has to be executed in the new process. This page describes ways to tailor pljava.policy so it grants that permission narrowly to only the Java code from which you want to start the process.