Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nQeqt-0007t3-26 for pljava-dev@arkaria.postgresql.org; Sun, 06 Mar 2022 00:34:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nQeqM-000600-9Q for pljava-dev@arkaria.postgresql.org; Sun, 06 Mar 2022 00:33:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nQeqM-0005xE-07 for pljava-dev@lists.postgresql.org; Sun, 06 Mar 2022 00:33:50 +0000 Received: from mail-ua1-x92b.google.com ([2607:f8b0:4864:20::92b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nQeqI-0000d9-Nz for pljava-dev@lists.postgresql.org; Sun, 06 Mar 2022 00:33:49 +0000 Received: by mail-ua1-x92b.google.com with SMTP id f7so5068653uab.8 for ; Sat, 05 Mar 2022 16:33:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=coyotesong.com; s=google; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=Dz04vbSAz47uumeCjs1CRjfCf05k56W1MdZ5LEay4Lk=; b=YqO/EWwuW4fy7F1BcB0Qou1/rukZ+P8VE4JbR7eDNr14YZRbV24mVfoPn/w2mXOy8o pYnvVGhX5tLwYz8uIMZ/BGvlUyx33lwDJ1mo/DlmhHnXzRDj8bvyT/KU21IrTng6wbbL FrjBa2obBMWLYqDVaqmkoDNCS/vSETDl+WP9Y= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=Dz04vbSAz47uumeCjs1CRjfCf05k56W1MdZ5LEay4Lk=; b=J5eerYOi4s3iiPYZQPBys0zpemGUxBsSRNKRMy6i1K4Nxlez46RKNEX5tvc8A0DEid vUBWH+2FjakUw/ejXVZNJQm9WTIeM8psVAwZ1zjc9FoXgzBCEtCq048rGr0+f/FAPKh0 7e4UpEO7R4ZpJyaxn7ou09/ZySHbQBd758b68U+H+6LVIHd1xjnm5RXesaF/a5XQD76V 89PNJ/k/Wl+xeUFAy5RtXJ9zDaFyJwI6aiNaqhORayatDVNTCZLFSLw2gUiTaeUCwqbV FjZ0mkPLFMsahWI3bVYm/yTgMKgxmN76hnP3VY4zs6IKtYHRvcHD317jt1nXaidOXcin 7Wlg== X-Gm-Message-State: AOAM530EyXh4dn+UMHpd4LKZ7pCIiBqB9TBwKh+DFk79Ur+7qmTOqin6 pGSTV1X60b+RORN9EUPoVKCJ2GzEpspC9EakDdIzPA== X-Google-Smtp-Source: ABdhPJwpSO92mJnTce5a1LZfLb5KTD/eUTyZcXF+5H3TyGqWY/6+0FVkUJ9b/yXR1/j9zJl8BSle6BbTvD27Hu4CIbA= X-Received: by 2002:ab0:6cb9:0:b0:34b:387d:374f with SMTP id j25-20020ab06cb9000000b0034b387d374fmr1494699uaa.123.1646526824116; Sat, 05 Mar 2022 16:33:44 -0800 (PST) MIME-Version: 1.0 References: <6223DA89.80607@anastigmatix.net> In-Reply-To: From: Bear Giles Date: Sat, 5 Mar 2022 17:33:32 -0700 Message-ID: Subject: Re: Problem running examples.jar with official postgresql pljava deb To: Chapman Flack Cc: pljava-dev@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000770adf05d981e6ff" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000770adf05d981e6ff Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 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=3D# SELECT sqlj.set_classpath('javatest', ''); >> postgres=3D# SELECT sqlj.remove_jar("ex", false); >> >> then reinstalling example.jar fails since the 'javatest' schema already >> exists. However if I execute >> >> postgres=3D# 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 >> 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 o= k >>> > 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 functio= ns >>> 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=E2=80=99t 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 schem= a >>> 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 >>> >> --000000000000770adf05d981e6ff Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I mentioned the source is at=C2=A0https://github.com/beargi= les/postgresql-pljava-docker.

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

$ docker run -d -p 5432:5432 -e POSTGRES_PASSWORD:passw= ord --name pljava beargiles/pljava
$ docker exec -it pl= java bash
# cd /docker-entrypoint-initdb.d
=

and you'll see four files executed in this or= der:

- 1= .pljava-vars.sh
- 2.pljava-setup.sql
- 3.plja= va-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 sy= stem. Unless you specified additional properties the only user is 'post= gres'.

Bear

On Sat, Mar 5, 2022 at 5:20 PM Bear Giles <bgiles@coyotesong.com> wrote:
=
All of the docker=C2=A0images 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)",1= 1.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.pgdg9= 0+1)",1.8.0_322,Linux,amd64}


On Sat, Mar 5, 2022 at 4:16 PM Bear Giles <bgiles@coyotesong.com>= wrote:
Thanks. I knew I had seen that=C2=A0somewhere bu= t I couldn't find it again.

The qu= ery works but I'm seeing something odd when I unwind=C2=A0the test. If = I execute

=C2=A0 postgres=3D# SELECT sqlj.set_classp= ath('javatest', '');
=C2=A0 postgres=3D# SELECT sq= lj.remove_jar("ex", false);

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

=C2=A0 postgres= =3D# DROP SCHEMA javatest=C2=A0CASCADE;

then when I re-install=C2=A0examples.jar I get
=C2=A0 ERROR: =C2=A0type "javatest.complextuple" does not exi= st

even thoug= h 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 ex= periment=C2=A0with pl/java and the location of the examples jar will be dif= ferent on each docker image.

BTW the values=C2=A0for the 'latest' image is

=C2=A0{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.
=C2=A0

On Sat, Mar 5, 20= 22 at 2:47 PM Chapman Flack <chap@anastigmatix.net> wrote:
On 03/05/22 16:10, Bear Giles wrote: > INFO:=C2=A0 05 Mar 22 21:01:28 org.postgresql.pljava.example.LoggerTes= t
> aggregate examples ok
> INFO:=C2=A0 05 Mar 22 21:01:28
> org.postgresql.pljava.example.annotation.SPIActions issue 228 tests ok=
> ERROR:=C2=A0 java.sql.SQLNonTransientException: resolving static metho= d
> org.postgresql.pljava.example.saxon.S9.like_regex with signature
> (String,String,String,boolean)boolean: java.lang.ClassNotFoundExceptio= n:
> 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<= br> described here:

http://tada.github.io/pljava/examples/examples.ht= ml#Exception_resolving_class_or_method_.28message_when_installing_examples.= 29

with two different ways you can proceed:



=C2=A0 =C2=A0 Install the required dependency first. Use sqlj.install_jar t= o 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.

=C2=A0 =C2=A0 Use SET check_function_bodies TO off before installing the ex= amples 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,<= br> of course, won=E2=80=99t work; SET check_function_bodies TO off simply mean= s you get
the errors later, when trying to use the functions, instead of when creatin= g
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'.<= br>
Regards,
-Chap
--000000000000770adf05d981e6ff--