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

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
>