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

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
>>>
>>