0

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?

Silence
  • 73
  • 3
  • 12

2 Answers2

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,?,?);.

Chapman Flack
  • 604
  • 5
  • 13
0

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.

Raj Verma
  • 1,050
  • 1
  • 7
  • 19
  • I want to make sure I'm understanding you. I am understanding that you must "hand-install" the extension. The page that you linked is about installing the extension itself. However, it does not discuss about compiling and installing business-logic implementation. The business-logic implementation has different steps as explained on [hello example](https://tada.github.io/pljava/use/hello.html). Can you clarify your answer about the part of installing the business logic implementation? – Silence Feb 07 '20 at 02:30
  • pg has its own compiler for the extensions that are used and does not use maven or other java compiler like this one does, that is what i was pointing to. Now you want to push your implementation app code directly into this extension that is compiled and pushed or sits on the db server weather it be localhost or remote. Extensions for postgres(native or third party) have to be compiled with xyz compilers and then installed, so there is no direct way or on the fly way of implementing the updated code for the Scenario as far as i’m aware. – Raj Verma Feb 07 '20 at 04:35
  • The original question was about installing user code via sqlj.install_jar, which happens after PL/Java is already installed in the database. What the OP wants to do can be accomplished by using the alternate version of sqlj.install_jar with a bytea argument. -- as for installing PL/Java in the first place, it is indeed done with CREATE EXTENSION pljava; as it has all the associated control and sql files, since release 1.5.0 back in 2016. – Chapman Flack Mar 04 '20 at 23:24