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

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

On 03/05/22 16:10, Bear Giles wrote:
> 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

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

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.

On Sat, Mar 5, 2022 at 2:47 PM Chapman Flack <chap(at)anastigmatix(dot)net> wrote:

> On 03/05/22 16:10, Bear Giles wrote:
> > 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
>
> 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
>

On 03/05/22 18:16, Bear Giles wrote:
> 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

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

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}

On Sat, Mar 5, 2022 at 4:16 PM Bear Giles <bgiles(at)coyotesong(dot)com> wrote:

> 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.
>
>
> On Sat, Mar 5, 2022 at 2:47 PM Chapman Flack <chap(at)anastigmatix(dot)net>
> wrote:
>
>> On 03/05/22 16:10, Bear Giles wrote:
>> > 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
>>
>> 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
>>
>

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

On Sat, Mar 5, 2022 at 5:20 PM Bear Giles <bgiles(at)coyotesong(dot)com> wrote:

> 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}
>
>
>
> On Sat, Mar 5, 2022 at 4:16 PM Bear Giles <bgiles(at)coyotesong(dot)com> wrote:
>
>> 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.
>>
>>
>> On Sat, Mar 5, 2022 at 2:47 PM Chapman Flack <chap(at)anastigmatix(dot)net>
>> wrote:
>>
>>> On 03/05/22 16:10, Bear Giles wrote:
>>> > 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
>>>
>>> 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
>>>
>>