How to deploy compiled PL/Java code straight to Postgres database from application?

Question

I have set up a Postgres database server with PL/Java binary installed on it.

I have observed the process of getting an example PL/Java code to install and run on the database as it starts with moving the compiled .jar file from application server to the database server, via file transfer, then call sqlj.install_jar('file::<path>', 'name', true); to load the .jar into the database server.

I am looking for a different way to load compiled PL/Java code without resorting to the file transfer method as explained above. I am looking through PL/Java documentation and it mentions that sqlj.install_jar function also supports pulling a .jar from web. Theoretically, I could get the application server to briefly spin up a HTTP file server to serve the .jar file and invoke the sqlj.install_jar to pull the .jar from the ad-hoc webserver. However, this may be difficult if the hostname of the application server is not known (i.e. not localhost or behind firewall/private network).

However I am wondering if there are a better way to do it. I am looking for a way that allows the application server to directly push implementation inside .jar using the existing connection to Postgres server without resorting to "hacks" explained above.

Does something like this already exists in PL/Java?

Answer 1

If you do this in psql:

\df sqlj.install_jar

you will see there are two versions of the function:

 Schema |    Name     | Result data type |                          Argument data types                           | Type 
--------+-------------+------------------+------------------------------------------------------------------------+------
 sqlj   | install_jar | void             | image bytea, jarname character varying, deploy boolean                 | func
 sqlj   | install_jar | void             | urlstring character varying, jarname character varying, deploy boolean | func

The one that takes a urlstring is the one that is specified by the SQL/JRT standard. The one that takes a bytea is a PL/Java nonstandard extension, but it can be useful for this case. If the jar file is available on the client machine you are running psql on, you can do:

postgres=$ \lo_import foo.jar
lo_import 16725
postgres=$ select sqlj.install_jar(lo_get(16725), 'foo', true);
 install_jar
-------------

(1 row)
postgres=$ \lo_unlink 16725
lo_unlink 16725

That is, you can use psql's \lo_import command, which opens a local file and saves it as a "large object" on the server, and gives you an Oid number to refer to it (the 16725 in my example might be a different number for you).

Once the large object is there, the SQL function lo_get(16725) returns its contents as a bytea, so you can pass it to the bytea flavor of install_jar. Once that's done, you just use \lo_unlink to remove the large object from the server.

If you are using JDBC or some other programmatic API to connect to the server, you can just bind your local jar file as first parameter in select sqlj.install_jar(?::bytea,?,?);.

Answer 2

Unfortunately, No is the short answer. This PL/Java is not formatted or written with control files and .sql files needed to deploy it as an extension. Although whatever I read on their official site site says it's an extension for PG.

But to have an extension installed PG(postgres) way, you need to have the control file for it and cos its written in java and do not have any control files it has to be compiled in its own way.

Normally PGXS is something helps compile the extensions for postgres with the help of PG_CONFIG file.

Note: on their site it clearly mentions "PL/Java can be downloaded, then built using Maven"

Just wanted to share a little of what i'm aware of :), Hope it helps.