Subject: PL/Java 1.5.2 - fixes one regression in date conversion in 1.5.1

From: Chapman Flack <[email-masked]>
Date: Mon, 5 Nov 2018 13:35:32 -0500

---
Note to packagers - please make sure the newly-added file
$sharedir/pljava/pljava--1.5.1--1.5.2.sql will be installed, alongside
those from prior releases. It is found in the
pljava-packaging/target/pljava-*.jar file but not in any build directory (it
gets added
synthetically to the jar file based on a template).
---

PL/Java 1.5 brings functions, triggers, and types in Java to PostgreSQL
releases from 11 back to 8.2. Release 1.5.2 fixes a regression in 1.5.1
that affected the conversion of PostgreSQL date values to Java java.sql.Date
objects.

Project site: http://tada.github.io/pljava/
Release notes: http://tada.github.io/pljava/releasenotes.html

## Change

Before 1.5.1, PostgreSQL date, time, and timestamp values could be accessed
in PL/Java as java.sql.Date, java.sql.Time, and java.sql.Timestamp objects.
PL/Java 1.5.1 added the option of accessing those values as objects of new
classes in the Java 8+ java.time package. The new classes are recommended as
a superior API for accessing dates, times, and timestamps.

However, one regression resulted in the older conversion between PostgreSQL
date and java.sql.Date, leading to an error of one day for some dates of the
year in some time zones. Release 1.5.2 restores the former behavior.

Please see the release notes for more complete information.

Availability:

1.5.2 is available from GitHub as a source release, which builds quickly
using Maven:

Release page: https://github.com/tada/pljava/releases/tag/V1_5_2

This wiki page will add links to prebuilt packages that become available:
https://github.com/tada/pljava/wiki/Prebuilt-packages

---

From: Christoph Berg <[email-masked]>
Date: Tue, 6 Nov 2018 08:49:31 +0100

Re: Chapman Flack 2018-11-05 <[email-masked]>
> year in some time zones. Release 1.5.2 restores the former behavior.

Hi,

1.5.2 doesn't build on Jessie and Trusty anymore (JDK 7):

https://pgdgbuild.dus.dg-i.net/job/postgresql-pljava-binaries/24/
https://pgdgbuild.dus.dg-i.net/job/postgresql-pljava-binaries/24/architecture=amd64,distribution=jessie/console

23:35:43 [ERROR] /<<PKGBUILDDIR>>/pljava-examples/src/main/java/org/postgresql/pljava/example/saxon/S9.java:[35,16] error: package java.time does not exist
23:35:43 [ERROR] /<<PKGBUILDDIR>>/pljava-examples/src/main/java/org/postgresql/pljava/example/saxon/S9.java:[879,12] error: cannot find symbol
23:35:43 [ERROR] symbol:   variable TIME_WITH_TIMEZONE
23:35:43 [ERROR] location: class Types

(There is another issue that apparently maven doesn't like running
several times in parallel, it chokes while reading/writing to ~/.m2.)

I've also enabled -Psaxon-examples - is it correct that no new files
appear in the .deb, but the examples are inside the existing
/usr/share/postgresql/11/pljava/pljava-examples-1.5.2.jar file?

Christoph

---

From: Chapman Flack <[email-masked]>
Date: Tue, 6 Nov 2018 07:38:07 -0500

On 11/06/18 02:49, Christoph Berg wrote:
> 23:35:43 [ERROR] .../example/saxon/S9.java:[35,16] error: package java.time does not exist
> ...

Correct.

1) Thanks for enabling -Psaxon-examples :)
2) ... but don't enable it for JDK 7 builds (S9.java uses Java 8 features).

I think that should solve the Jessie / Trusty problem.

Cheers,
-Chap

---

From: Christoph Berg <[email-masked]>
Date: Tue, 6 Nov 2018 15:56:59 +0100

Re: Chapman Flack 2018-11-06 <[email-masked]>
> I think that should solve the Jessie / Trusty problem.

Thanks, you had probably mentioned that earlier, but I got lost with
too many PG versions in parallel.

There's still a problem, though. Jessie, 9.3, OpenJDK 7:

ERROR:  java.lang.SecurityException: read on /usr/share/javazi/Europe/Prague
STATEMENT:  SELECT sqlj.install_jar('file:/usr/share/postgresql/9.3/pljava/pljava-examples-1.5.2.jar', 'examples', true)

https://pgdgbuild.dus.dg-i.net/job/postgresql-pljava-binaries/25/architecture=ppc64el,distribution=jessie/console
(That's on ppc64el, the rest is still building as of now.)

https://pgdgbuild.dus.dg-i.net/job/postgresql-pljava-binaries/25/
(The i386 errors are my fault.)

Christoph

---

From: Chapman Flack <[email-masked]>
Date: Wed, 7 Nov 2018 13:23:42 -0500

> STATEMENT:  SELECT sqlj.install_jar('file:/usr/share/postgresql/9.3/pljava/pljava-examples-1.5.2.jar', 'examples', true)

Hmm. Does that happen only on Jessie? Is there a debianism where zoneinfo
files for Java are distributed in this other location instead of under
$JAVA_HOME ?

At present, the security manager for "trusted" PL/Java doesn't offer any
easy way to configure additional filesystem locations that are ok to read
from. I'm working on that for a future version, but that doesn't help here.

I suspect it could be made to work by adding
trust=org.postgresql.pljava.annotation.Function.Trust.UNSANDBOXED
in the @Function annotation on the issue199() method in
pljava-examples/src/main/java/org/postgresql/pljava/example/annotation/PreJSR310.java

However, it is only a regression test, and it is probably simpler just to
build with a patch to not run it (say, by removing the @SQLAction annotation
at the top of that file). That's preferable to adding UNSANDBOXED willy
nilly to things in the examples jar that people may casually install.

This is good for me to know about, as I was thinking of changing PL/Java's
behavior in a future release to set Java's timezone to match the PG
session's by default. Now I know not to do that until there is a way to
accommodate zoneinfo from atypical locations.

-Chap

---

From: Christoph Berg <[email-masked]>
Date: Thu, 8 Nov 2018 22:42:56 +0100

Re: Chapman Flack 2018-11-07 <[email-masked]>
> files for Java are distributed in this other location instead of under

Trusty has the same problem.

Both ship with a "tzdata-java" package that has timezone info in that
location. Newer Java apparently use the standard TZ database in
/usr/share/zoneinfo/. (The only java-specific thing I could find was
-rw-r--r-- 1 root root 105882 Okt 17 09:31 /usr/lib/jvm/java-11-openjdk-amd64/lib/tzdb.dat )

> nilly to things in the examples jar that people may casually install.

Hmm, just not building pljava on the older dists looks more and more
attractive...

> accommodate zoneinfo from atypical locations.

Well, the location is typical for Java 7 on Debian. If it's still a
problem with Java 8+, I'd claim Java should be patched to recognize it
as "normal".

Christoph

---

From: Chapman Flack <[email-masked]>
Date: Tue, 1 Jan 2019 00:04:54 -0500

Hi,

If I'm checking in the right place
  https://yum.postgresql.org/11/redhat/rhel-7.5-x86_64/

... the version of PL/Java that I'm seeing there is still 1.5.1.

Please forgive this repeat announcement if the 1.5.2 package is already
in the works. It would be valuable to have 1.5.2 packaged, because it
fixes a known bug in java.sql.Date conversion that was in 1.5.1.

Thanks!
-Chap

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 02 Jan 2019 19:04:04 +0000

Hi Chapman,

> fixes a known bug in java.sql.Date conversion that was in 1.5.1.

Updated to 1.5.2 yesterday. Sorry for the delay.

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

---

From: Chapman Flack <[email-masked]>
Date: Wed, 2 Jan 2019 14:34:08 -0500

> Updated to 1.5.2 yesterday. Sorry for the delay.

That was fast, thanks!

... but it does not have these files:

usr/pgsql-*/share/pljava/pljava--1.5.0--1.5.2.sql
usr/pgsql-*/share/pljava/pljava--1.5.1--1.5.2.sql

Without those files, ALTER EXTENSION pljava UPDATE; will fail
for sites that have 1.5.0 or 1.5.1 installed already.

The build process puts such files directly into the installer jar,
as copies of pljava--.sql with the right names.

Your packaging process seems to bypass the installer jar, so will
have to be responsible for making those files. It could make the
needed copies or links at build time, or even just include one
of them and make the remaining copies/links on the target system
in a post-install script.

More on that (and also on debuginfo and examples) was in [1].

Thanks,
-Chap

[1]
https://www.postgresql.org/message-id/5BC924A4.7080709%40anastigmatix.net

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 02 Jan 2019 19:45:34 +0000

Hi Chapman,

> usr/pgsql-*/share/pljava/pljava--1.5.1--1.5.2.sql

I actually committed it a few mins ago :)

https://git.postgresql.org/gitweb/?p=pgrpms.git;a=commit;h=00dc4f440d958eb464615602ccb9e51cc83630ef

> More on that (and also on debuginfo and examples) was in [1].

They are already in 1.5.2-1.

Pushed 1.5.2-2 to repos, that include the .sql files.

Thanks!

Regards,

---

From: Chapman Flack <[email-masked]>
Date: Wed, 2 Jan 2019 17:09:08 -0500

> Pushed 1.5.2-2 to repos, that include the .sql files.

Ah, there they are. Thanks!

-Chap

---

From: Chapman Flack <[email-masked]>
Date: Thu, 3 Jan 2019 11:25:50 -0500

> Ah, there they are. Thanks!

It turns out to be almost right. The update .sql files appear
to be links to pljava--1.5.2.sql, but that is the file used for
a new installation. ALTER EXTENSION UPDATE fails if that file is used.

There is a file in the source tree named pljava--.sql and that
one is the template for all (trivial) update scripts. The
installer jar produced by the build process simply includes that
file under all of the necessary update script names (and not
under its own name).

It would be possible to do that, or to make one of the named
update scripts a copy of it and the rest all links to that one,
etc.

Sorry for the unexpected structure.

-Chap

---

From: Chapman Flack <[email-masked]>
Date: Thu, 10 Jan 2019 08:50:45 -0500

> update scripts a copy of it and the rest all links to that one,

Can this be fixed? As it stands in 1.5.2-2, ALTER EXTENSION UPDATE
still breaks.

Thanks,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Tue, 22 Jan 2019 11:05:59 -0500

Ping?

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 30 Jan 2019 18:32:21 +0000

Hi Chapman,

> Ping?

Ack. Apologies. Will look soon.

Cheers,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

---

From: Chapman Flack <[email-masked]>
Date: Fri, 15 Feb 2019 10:33:38 -0500

Ping?

---

From: Chapman Flack <[email-masked]>
Date: Tue, 26 Feb 2019 13:38:04 -0500

Ping?

---

From: Devrim Gündüz <[email-masked]>
Date: Tue, 26 Feb 2019 22:16:13 +0300

Hi Chapman,

> Ping?

How does it look now?

# ll *sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.0--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.0-BETA1--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.0-BETA2--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.0-BETA3--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.1--1.5.2.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.1-BETA1--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.1-BETA2--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
lrwxrwxrwx 1 root root   39 Feb 26 22:13 pljava--1.5.1-BETA3--1.5.1.sql -> /usr/pgsql-11/share/pljava/pljava--.sql
-rw-r--r-- 1 root root 3228 Feb 26 22:13 pljava--1.5.2.sql
-rw-r--r-- 1 root root 2433 Feb 26 22:13 pljava--.sql
-rw-r--r-- 1 root root 3580 Feb 26 22:13 pljava--unpackaged--1.5.2.sql

Regards,

---

From: Chapman Flack <[email-masked]>
Date: Tue, 26 Feb 2019 16:18:56 -0500

> -rw-r--r-- 1 root root 3580 Feb 26 22:13 pljava--unpackaged--1.5.2.sql

Hi Devrim,

Thanks for taking a look ... that's better, with one complication, though.

It has links for updating 150-BETA* to 150, 151-BETA* and 150 to 151,
151-BETA* and 151 to 152, which would be the typical arrangement for a
typical extension, and would cause PostgreSQL to automatically apply
multiple scripts in sequence.

The multiple script sequence won't work in PL/Java though, because
the updating work is really done inside PL/Java at its own startup,
which can't be repeated over a single backend connection.
(You have seen that they are all links to one script, which does
nothing but store the library pathname and exnihilo=false (indicating
update) in a table and LOAD the library, which unconditionally applies
any schema updates needed to match its own version. PostgreSQL itself
treats later LOADs of the same library as no-ops, so I couldn't
make the multiple-script sequence work if I wanted to).

This is sort of an imperfect fit with the PG extension conventions,
but PL/Java 1.5.x still supports PG versions older than the extension
feature, so updating has to be easy for them too.

So the intent for packaging is to include a link pljava--foo--1.5.2.sql
for every plausible prior version foo, so PostgreSQL will choose
a one-script update path no matter which prior version is installed.

The actual files placed into an "official" build can be found
starting here:

https://github.com/tada/pljava/blob/REL1_5_STABLE/pljava-packaging/build.xml#L280

Thanks!
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Wed, 27 Feb 2019 14:38:51 -0500

Hi Devrim,

I assume it's straightforward to change the symlinks to have the
right names?

Regards,
-Chap