Subject: Problem running examples.jar with official postgresql pljava deb

From: Bear Giles <[email-masked]>
Date: Sat, 5 Mar 2022 14:10:10 -0700

I'm wrapping up a new set of docker images for pljava -
https://hub.docker.com/beargiles/pljava - and hit a problem when installing
examples.jar to get the values to provide when registering the prebuilt
distributions.

Basically the official version of pl/java does not include saxon. I can
easily install the debian package and install the jars but I'm stuck
installing the example jar.

If I don't create the 'javatest' schema the execution fails because it
can't find the saxon jar.

postgres=# SELECT
sqlj.install_jar('file:///usr/share/postgresql/14/pljava/pljava-examples-1.6.4.jar',
'ex', true);
INFO:  05 Mar 22 21:01:28 org.postgresql.pljava.example.LoggerTest
aggregate examples ok
INFO:  05 Mar 22 21:01:28
org.postgresql.pljava.example.annotation.SPIActions issue 228 tests ok
ERROR:  java.sql.SQLNonTransientException: resolving static method
org.postgresql.pljava.example.saxon.S9.like_regex with signature
(String,String,String,boolean)boolean: java.lang.ClassNotFoundException:
net.sf.saxon.trans.XPathException
CONTEXT:  SQL statement "CREATE OR REPLACE FUNCTION javatest.like_regex(
value pg_catalog.varchar, pattern pg_catalog.varchar, flag
pg_catalog.varchar DEFAULT CAST(e'' AS pg_catalog.varchar), w3cNewlines
boolean DEFAULT CAST(e'false' AS boolean)) RETURNS boolean LANGUAGE java
VOLATILE AS
e'boolean=org.postgresql.pljava.example.saxon.S9.like_regex(java.lang.String,java.lang.String,java.lang.String,boolean)'"

If I do create the 'javetest' schema the execution fails because the
javatest schema already exists.

Nice Catch-22 there.

I tried adding the jars to both the 'public' and 'sqlj' schemas but no joy.

How can I get past this? Besides providing the information requested (which
I could get by looking at the underlying tables) I would like to be able to
execute the query just to have an automated way to verify a successful
installation.

Bear

---

From: Chapman Flack <[email-masked]>
Date: Sat, 5 Mar 2022 16:47:53 -0500

> net.sf.saxon.trans.XPathException

I think you are running into the known complication that was introduced
with 1.6.0, where functions are now validated at CREATE FUNCTION time,
and can fail if a dependency isn't resolvable at that time. That's
described here:

http://tada.github.io/pljava/examples/examples.html#Exception_resolving_class_or_method_.28message_when_installing_examples.29

with two different ways you can proceed:

    Install the required dependency first. Use sqlj.install_jar to install
the Saxon jar (as described here), and sqlj.set_classpath to make it
accessible, and then use sqlj.install_jar to install the examples jar
itself. The dependency will be satisfied and all of the example functions
will work.

    Use SET check_function_bodies TO off before installing the examples jar.
That will simply relax the strict checking at CREATE FUNCTION time, so that
all of the example functions will be created. The ones that require Saxon,
of course, won’t work; SET check_function_bodies TO off simply means you get
the errors later, when trying to use the functions, instead of when creating
them. If you install the dependency jar later and add it to the class path,
those functions will then work.

The Saxon jar is pure library code; it doesn't declare any PL/Java
functions. So no particular schema needs to exist before installing it.
Of course, it needs to be added to a classpath before installing
the examples jar (if using the dependency-first approach). I generally
just add it to the public schema, which is the fallback for other schema
class paths.

SELECT sqlj.set_classpath('public', 'saxon');

Or, you might find it more convenient to use the
SET check_function_bodies TO off; approach, install the examples jar first,
then install the saxon jar, and finish by setting the schema classpath
for javatest (which will exist by that point) to 'examples:saxon'.

Regards,
-Chap

---

From: Bear Giles <[email-masked]>
Date: Sat, 5 Mar 2022 16:16:29 -0700

Thanks. I knew I had seen that* somewhere* but I couldn't find it again.

The query works but I'm seeing something odd when I unwind the test. If I
execute

  postgres=# SELECT sqlj.set_classpath('javatest', '');
  postgres=# SELECT sqlj.remove_jar("ex", false);

then reinstalling example.jar fails since the 'javatest' schema already
exists. However if I execute

  postgres=# DROP SCHEMA javatest CASCADE;

then when I re-install examples.jar I get

  ERROR:  type "javatest.complextuple" does not exist

even though check_function_bodies has been set to off.

Perhaps this isn't supported and I should just leave 'javatest' in place
since one of the goals is to make it easy to experiment with pl/java and
the location of the examples jar will be different on each docker image.

BTW the values for the 'latest' image is

 {1.6.4,"14.2 (Debian 14.2-1.pgdg110+1)",11.0.14,Linux,amd64}

I'll be creating docker images for PostgreSQL 10 through 14.

---

From: Chapman Flack <[email-masked]>
Date: Sat, 5 Mar 2022 18:39:53 -0500

>   ERROR:  type "javatest.complextuple" does not exist

If I do sqlj.remove_jar with true instead of false, then it puts away
all its toys including the javatest schema itself, and I don't see an
error upon reinstalling examples.jar (in a new session),

Trying to reinstall the jar in the same session can lead to puzzling
errors of the "Cannot cast Point to Point" variety, because the caching
of type information in the C code can retain references to the schema's
former ClassLoader, and classes of the same name but different loaders
are different. But that's easily sidestepped by just opening a new session.

Regards,
-Chap

---

From: Bear Giles <[email-masked]>
Date: Sat, 5 Mar 2022 17:20:06 -0700

All of the docker images are now running the query. I'm not removing the
schema or samples jar though.,

   - {1.6.4,"14.2 (Debian 14.2-1.pgdg110+1)",11.0.14,Linux,amd64}
   - {1.6.4,"13.6 (Debian 13.6-1.pgdg110+1)",11.0.14,Linux,amd64}
   - {1.6.4,"12.10 (Debian 12.10-1.pgdg110+1)",11.0.14,Linux,amd64}
   - {1.5.6,"11.15 (Debian 11.15-1.pgdg90+1)",1.8.0_322,Linux,amd64}
   - {1.5.6,"10.20 (Debian 10.20-1.pgdg90+1)",1.8.0_322,Linux,amd64}

---

From: Bear Giles <[email-masked]>
Date: Sat, 5 Mar 2022 17:33:32 -0700

I mentioned the source is at
https://github.com/beargiles/postgresql-pljava-docker.

You can look at the scripts (bash and SQL) there but you just want to see
what actually ran it may be easier to just start an instance.

$ docker run -d -p 5432:5432 -e POSTGRES_PASSWORD:password --name pljava
beargiles/pljava
$ docker exec -it pljava bash
# cd /docker-entrypoint-initdb.d

and you'll see four files executed in this order:

- 1.pljava-vars.sh
- 2.pljava-setup.sql
- 3.pljava-saxon.sql
- 4.pljava-test.sql

If you want to start psql in the docker container use

# su postgres
# psql

or just access it like any remote system. Unless you specified additional
properties the only user is 'postgres'.

Bear