Subject: Starting build-system work (Windows/Mac users please speak up)

From: Chapman Flack <[email-masked]>
Date: Sun, 17 May 2020 14:10:31 -0400

This message is largely for Kartik but may be of interest to others;
please contribute in case of suggestions, questions, concerns, etc.

*In particular, if you use Mac or Windows/MinGW-64 builds of PostgreSQL,
please check what I've said below and supply corrections/better ideas
as necessary.*

Thomas Hallgren, originator of PL/Java, is subscribed on this list, and
may have some thoughts on how GitHub Actions might be usable in place of
Travis without requiring the same level of repository write access.

== Java versions to build for

For setting up CI, I suggest basing the work on the 'master' branch,
which is where the future 1.6.0 release is taking shape. That branch
only supports Java 9 and later, and the build is much simplified
compared to the REL1_5_STABLE branch, which still takes extra pains
to support building back to Java SE 6.

I don't intend to abandon the REL1_5_STABLE branch immediately, so if
time permits, testing that branch and with earlier Java versions would
be ok, but should not be the focus.

== PostgreSQL versions to build for

Also, the newer branch targets a narrower range of PostgreSQL versions.
The obvious starting point would be the versions currently shown as
'supported' in the PostgreSQL versioning policy:

https://www.postgresql.org/support/versioning/  i.e., 12 back to 9.5.

The branch may build successfully for a couple of versions before 9.5.
I know the 'validator' PR knocked out pre-9.0 versions. When it comes
time to announce PL/Java 1.6, we might choose to say it works back
to PG 9.4, 9.3, or 9.2, if it works for those releases without extra fuss.

9.2 is a good stopping place, because the REL1_5_STABLE installation docs
have an extra section for installing on pre-9.2 versions, and that could
go away.

The REL1_5_STABLE branch tries to stay buildable back to PG 8.2, but
again, I would not focus effort there.

== Platforms to build for

Platforms should include at least one common Linux distro, Mac, and
Windows. Windows should test building both with MSVC and with MinGW-64.
(If I remember right, the EnterpriseDB PostgreSQL builds are made
with MSVC, and the BigSQL ones are made with MinGW-64.)

https://www.enterprisedb.com/software-downloads-postgres
https://pgsql.io/

Something may have changed with BigSQL; I see the URL got redirected
now to pgsql.io, and they say "Windows 10 Subsystem for Linux", which
may not involve a native build for Windows anymore.

EnterpriseDB has an installer for Mac. Also, Homebrew has been suggested,
and may now be preferred:

https://www.enterprisedb.com/downloads/postgresql
https://wiki.postgresql.org/wiki/Detailed_installation_guides#MacOS

== Extra options

When testing PL/Java and running the examples, pljava.vmoptions should
additionally contain:

  -ea:org.postgresql.pljava...  -Xcheck:jni

The -Xcheck:jni makes big output and big slowdown; it might be ok to
include that in a subset of the tested configurations rather than all
of them.

The PostgreSQL server used for testing will ideally have been built
with these configure options:

  --with-libxml --enable-cassert --enable-debug

and (on platforms where gcc is the compiler)

  CFLAGS='-ggdb -Og -g3 -fno-omit-frame-pointer'

Prebuilt PostgreSQL versions available to the CI service might not have
been built with those options (pg_config output will show). So it may be
necessary to build PostgreSQL from source too. Again, it may be adequate
to do this for one platform, say Linux, if it is easiest there.)

If a PL/Java test crashes, the JVM may have written an hs_err_pid<n>.log
file (with n the PID of the backend process), which should be preserved
for review of the failing test.

That file is written by HotSpot JVMs (as the hs_ suggests). It is also
good to test with OpenJ9 JVMs, but I would focus first on covering the
combinations of Java version ✕ PG version ✕ platform, using HotSpot.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Mon, 18 May 2020 10:19:48 -0400

Hi, I'm adding pljava-dev in the recipients.

On 05/18/20 09:19, Kartik Ohri wrote:
> way of determining the name of the jar files created by mvn clean install.
> I understand that the jar files are named as pljava-pgX.Y-arch-os-link.jar
> . I am currently find command to determine the complete name. Is this

To determine for sure what the name is going to be would force you
to duplicate the logic that sets ${suffix} in pljava-packaging/build.xml,
which is rather gruesome, to be honest (and I think I wrote it!).

On the other hand, if running mvn clean install, the 'clean' should ensure
that no other pljava-pgX.Y-arch-os-link.jar from any earlier run is present,
so the only file in that directory matching pljava-pg*.jar should be the
right one. (Note that the /directory/ it's created in is always the same,
so that isn't part of the puzzle.)

Even using 'find' therefore seems needlessly heavyweight; the wildcarded
name should do fine.

You could do that for the examples jar also, or capture the output when
running the installer jar, as it prints the full pathnames where it is
installing the files. The wildcard in the build directory is probably
simpler; no need for a CI script to be any fancier than what works.

Regards,
-Chap

p.s. Different open-source projects tend to have different
mailing-list cultures.

When replying to a list message, your mail client should offer options
like 'Reply' (only to the person who sent a message), 'Reply List' (only
to the list), and 'Reply All' (goes to all of the individually named
participants *and* to the list). No need to be shy about sending questions
to the list; you're doing development, and that's what a -dev list is for.

On mailing lists in the PostgreSQL project, the tradition is 'Reply All',
so your reply will go directly to whatever individuals are directly
engaging with you in that thread *and also* to the list for archiving.
(That tends to mean the people directly participating get two copies,
but that's the tradition in the PostgreSQL community.)

More on PostgreSQL's mailing list culture is here:
https://wiki.postgresql.org/wiki/Mailing_Lists

This tradition also prefers to: (1) trim the message being replied to
so it includes only the portions getting replies, and (2) write
each reply after the trimmed original bit being replied to.

It is not considered necessary, in this tradition, to have the entire
previous message quoted in the reply, because the mailing list archive
is always available to anyone who wants to see the past context.

For example, this email thread can be reviewed at
https://www.postgresql.org/message-id/5EC17E17.7070002%40anastigmatix.net

and all history of pljava-dev at
https://www.postgresql.org/list/pljava-dev/

-c

---

From: Kartik Ohri <[email-masked]>
Date: Thu, 21 May 2020 12:18:40 +0530

> have an extra section for installing on pre-9.2 versions, and that could

Should I add checks for a PostgreSQL version from each major version or
just for one from the latest major version and the oldest version supported
? Like just test with 9.5 and 12.x . Same question with Java versions. Is
testing with Java 9 and Java 14 (the latest java release) is enough ? Or
each of java versions in between Java 9 and 14 should be tested as well.

---

From: David Gradwell <[email-masked]>
Date: Thu, 21 May 2020 08:14:36 +0000

I would include Java 1.8 (e.g. recent build such as jdk1.8.0_221.

This is because some capabilities of NetBeans (e.g.  Native packaging) still require running under Java 1.8.

Regards

David

From: Kartik Ohri <[email-masked]>
Date: Thursday, 21 May 2020 at 07:48
To: Chapman Flack <[email-masked]>
Cc: "[email-masked]" <[email-masked]>
Subject: Re: Starting build-system work (Windows/Mac users please speak up)

Should I add checks for a PostgreSQL version from each major version or just for one from the latest major version and the oldest version supported ? Like just test with 9.5 and 12.x . Same question with Java versions. Is testing with Java 9 and Java 14 (the latest java release) is enough ? Or each of java versions in between Java 9 and 14 should be tested as well.

---

From: Chapman Flack <[email-masked]>
Date: Thu, 21 May 2020 07:34:25 -0400

On 05/21/20 02:48, Kartik Ohri wrote:

> ? Like just test with 9.5 and 12.x .

Each major version in the range, as there are often changes from every
version to the next that require conditional compilation in PL/Java, and
it is important to make sure that no recent PL/Java change broke any of
those.

(It is exactly here that CI promises to make the release process for
PL/Java much less tedious and treacherous.)

A reasonable timeline might be to get both endpoints covered first,
and then fill in the middle.

> Same question with Java versions. Is

Again, maybe endpoints first, and then middles. Without testing of
middles, the odd javac bug that appeared in 10 and 11 and was fixed
in 12 would have been a surprise. [1]

Regards,
-Chap

[1] https://github.com/tada/pljava/commit/c763cee

---

From: Chapman Flack <[email-masked]>
Date: Thu, 21 May 2020 07:47:00 -0400

> still require running under Java 1.8.

Java 1.8 remains supported on the 1.5.x branch.

The still-gestating 1.6 branch represents a clean break requiring
Java 9 or later, to take advantage of Java 9 features like the Java
Platform Module System, VarHandles, etc.

To throw Kartik into the many-Java-versioned 1.5.x build configuration
would probably have violated the Geneva Convention. Hence the focus
on the 1.6 branch and its much simplified arrangements.

It would be interesting if you were to describe what you're doing with
NetBeans ... native-packaging PL/Java alone? PL/Java along with a
PL/Java application?

Although I do not intend to imminently abandon the 1.5.x branch, it
might be well to begin breathing on NetBeans concerning their support
for Java 9.

There is also a native-packaging feature in GraalVM that I have not
played with; it might be worth investigating.

Regards,
-Chap

---

From: David Gradwell <[email-masked]>
Date: Thu, 21 May 2020 12:20:47 +0000

Chap,

I'm using Native Packaging (but which only works with Ant, not Maven) for applications I want to distribute widely.    See https://issues.apache.org/jira/browse/NETBEANS-4237?filter=-2.  I have got Native packaging to work with Netbeans 12.0 beta4 as long as it runs with Java 1.8.

Separately, I'm using plJava to specify some server side functions in situations where I cannot get enough performance if I retrieve each column in each row one at a time !  I've built the latest plJava on my Mac OS X  Catalina system for postgres 9.6 and 11.  If anyone needs binaries let me know what to upload and where to.

Regards
 
David

On 21/05/2020, 12:47, "Chapman Flack" <[email-masked]> wrote:

    Regards,
    -Chap

---

From: Kartik Ohri <[email-masked]>
Date: Fri, 22 May 2020 02:55:25 +0530

> [1] https://github.com/tada/pljava/commit/c763cee

Please see https://travis-ci.org/github/amCap1712/pljava/builds/689767027 .
I have written a script to add builds for combinations of major versions of
Java and PostgreSQL on macOS. But I think this is an overkill to for all
platforms (even for a single platform). Let me know your thoughts on this
and if any other additions are required to complete the macOS setup. After
this, I will add builds for Linux and at last for Windows.

Some of the build combinations fail intermittently but work just fine when
ran again. It seems to be an error on Travis CI side. I am investigating
the issue and am working to fix it.

---

From: Chapman Flack <[email-masked]>
Date: Thu, 21 May 2020 18:07:07 -0400

> this, I will add builds for Linux and at last for Windows.

I will look soon.

I agree that a Cartesian product of PG versions and Java versions would
be more than necessary. What is important is to confirm that every
supported PG version works (built with some Java version), and every
supported Java version can be used to build (with some PG version).

Ideally, that much would be confirmed on each supported platform.

I don't think I have ever run into an interaction effect between the
Java version and the PG version (it would be quite unexpected, as those
two things on their own don't know about each other).

> the issue and am working to fix it.

Thanks!

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Fri, 22 May 2020 23:54:29 +0530

> I will look soon.
Please take a look at this
https://travis-ci.com/github/amCap1712/pljava/builds/167810527 instead. I
have added builds for both linux and macOS here. (The previous one had only
macOS). #77.19 test pljava against PostgreSQL 12 built from source with the
relevant flags you mentioned earlier.

> Ideally, that much would be confirmed on each supported platform.

I have reduced the build matrix to the minimum builds required to meet
these conditions.

> > the issue and am working to fix it.
 #77.8 and #77.17 illustrate this issue. In #77.8, macports is unable to
install openssl which is installed properly in all of the remaining builds.
#77.17 fails with some include path error due to nar-maven. #77.16 had
failed with the same error. But when I ran it again without any changes, it
passed the build as you can see on the page.

Both #77.8 and #77.17 will pass the build if run again without any
changes. I am trying to contact with the people at #macports on freenode
IRC to find a solution for the issue with #77.8. For #77.17, I have seen
this issue in PL/Java Github issues. The problem is I do not have to change
anything to fix it. It automatically breaks and fixes itself. Can you shed
some light on this one ?

Sorry for sending it multiple times. I had pressed both Reply and Reply All
by mistake.

---

From: Chapman Flack <[email-masked]>
Date: Fri, 22 May 2020 15:48:31 -0400

On 05/22/20 14:24, Kartik Ohri wrote:

> #77.17 fails with some include path error due to nar-maven.

I have seen that in reported issues too. Inconvenient that the
nar-maven-plugin developers wrote an IncludePath object that doesn't
override toString(), so when it is printed in an error message it
just comes out IncludePath@hex-address.

Don't do that in your plugin. :)

I see Maven gives a hint there that if you run with -e it might
give a full stack trace of the errors. I haven't tried it, but
in a case like this I wonder if the full stack trace would include
a lower-level exception like FileNotFoundException that would
actually show the name that wasn't found!

My suspicion is that it also is related to some dependency like
openssl that intermittently isn't getting downloaded/installed properly.

Another thing I notice that could be annoying when going through
these logs is that the vast majority of the log is just download
progress of all the dependencies for the build.

It looks like that bulk can be reduced a lot by adding these Maven options:

--batch-mode
-Dorg.slf4j.simpleLogger.log.org.apache.maven.cli.transfer.Slf4jMavenTransferListener=warn

The first one just tones down some of the message bloat like color changes,
and setting the TransferListener logging level to 'warn' means we should see
if something goes wrong, but not fill the whole log with all the stuff
that went right.

Found here: https://stackoverflow.com/a/35653426

As another thing to think about, if every build is starting from a
clean slate, all of that stuff has to get re-downloaded every time.
If I start from a clean slate, build PL/Java once, then look at the
size of my local Maven repository:

$ du -sh ~/.m2/repository
72M	/var/tmp/testbuilds/.m2/repository

that's 72 megs of stuff getting downloaded ... each time.

If we had a way of doing multiple PG version / multiple Java version
test builds without completely resetting the environment every time,
that could save the Travis guys more than half a gig of bandwidth.

For every set of test runs.

What I do here is I have several different PG versions built and
installed in testbuilds/pg12 testbuilds/pg11 and so on, and likewise
for Java versions.

Then if I run Maven like so:

JAVA_HOME=/var/tmp/jdk-14+36/ mvn \
  -Dpljava.libjvmdefault=/var/tmp/jdk-14+36/lib/server/libjvm.so \
  -Dpgsql.pgconfig=/var/tmp/testbuilds/pg12/bin/pg_config \
  clean install

it builds for that Java version and that PG version:

- the JAVA_HOME in front controls what Java version will run Maven itself
- the -Dpljava.libjvmdefault builds in a default value of
  pljava.libjvm_location so it doesn't need to be specified later in PG
- the -Dpgsql.pgconfig controls which PG version to build against.

Then when it has created the pljava-packaging/target/pl*jar file, I can
run it like this:

/var/tmp/jdk-14+36/bin/java \
  -Dpgconfig=/var/tmp/testbuilds/pg12/bin/pg_config \
  -jar pljava-packaging/target/pl*jar

which makes sure to use the tested Java version to run the jar, and
again gives the pg_config location, which determines where the files
are installed.

This doesn't all have to be in place at once, if you are focused on
getting the basic functionality in place. But it would be a significant
optimization later.

Also, I suggest (as in the build docs) using these Maven options:

 -Dnar.cores=1 -Pwnosign

The -Pwnosign only works on platforms using gcc, so it would have to be
left off for others. It silences a whole category of sign-conversion
warnings that are only distracting.

Setting nar.cores to 1 makes it a lot easier to read error messages if
there are errors; otherwise, if the compilation uses a bunch of threads,
the messages all come out on top of each other and it is hard to see
where an error came from.

Also -Psaxon-examples is needed to build a set of XML examples that are
not built by default.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Sat, 23 May 2020 01:34:08 +0530

:+1:

> Found here: https://stackoverflow.com/a/35653426

I will make these changes and try to reduce any other unnecessary logs as
well.

> getting the basic functionality in place. But it would be a significant
This would be worthwhile but as mentioned at
https://docs.travis-ci.com/user/build-stages/#data-persistence-between-stages-and-jobs
, state is not persisted between jobs. Every build job compulsorily starts
as a clean state. We can though cache dependencies to speed up the build
and save bandwidth though to some extent using
https://docs.travis-ci.com/user/caching/ .

> I will incorporate these changes into the builds.

> -Chap
Thanks for the detailed feedback. I will work on this and get back to you
soon.

---

From: Chapman Flack <[email-masked]>
Date: Fri, 22 May 2020 16:15:29 -0400

> https://docs.travis-ci.com/user/caching/ .

Ah, yes, looks like

cache:
 directories:
 - $HOME/.m2

would be the ticket.

https://docs.travis-ci.com/user/caching/#arbitrary-directories

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Sat, 23 May 2020 12:46:17 +0530

> Also -Psaxon-examples is needed to build a set of XML examples that are
I added the flag to the build but the build fails with
INFO: 22 May 20 21:27:40 org.postgresql.pljava.example.LoggerTest Looking
good!
INFO: 22 May 20 21:27:40
org.postgresql.pljava.example.annotation.SPIActions issue 228 tests ok
ERROR: java.sql.SQLNonTransientException: Unable to find static method
org.postgresql.pljava.example.saxon.S9.like_regex with signature
(String,String,String,boolean)boolean

5638

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
'boolean=org.postgresql.pljava.example.saxon.S9.like_regex(java.lang.String,java.lang.String,java.lang.String,boolean)'".
I was unable to find this issue in Github Issues. Is this a known issue or
should I file one on Github or is this an error due to some
misconfiguration in the build environment ? Here is the complete build log
https://travis-ci.com/github/amCap1712/pljava/jobs/339047104 .

---

From: Chapman Flack <[email-masked]>
Date: Sat, 23 May 2020 11:06:22 -0400

> CONTEXT: SQL statement "CREATE OR REPLACE FUNCTION javatest.like_regex(

Good, good! In the 1.6 branch, PL/Java now has a validator, which will
produce an error at CREATE FUNCTION time if anything the function depends
on can't be resolved. (In 1.5, you would be able to create the function,
and only find out it didn't work later when trying to call it.)

The Saxon examples depend on the Saxon jar, which hasn't been installed
or isn't on the classpath. So there are two ways to proceed:

1. sqlj.install_jar the Saxon jar first, and sqlj.set_classpath to put
   it on the classpath, before the sqlj.install_jar of the examples jar.

   After a build completes, the needed jar will be present in the local
   Maven repository as
   .m2/repository/net/sf/saxon/Saxon-HE/*/Saxon-HE-*.jar

   The * is there for the Saxon version, which could change with time.
   In a build from a clean slate, there should be only one version there.
   If .m2/repository is being cached, it is possible from time to time
   Maven will find a later Saxon version available and download it and
   an older one will still be there, and a script would have to select
   the latest match.

2. Or, SET check_function_bodies TO off; before the sqlj.install_jar for
   the examples, and dependencies won't be checked. That will be more like
   the old 1.5 behavior, where the CREATE FUNCTION will succeed, but later
   the function won't work.

> misconfiguration in the build environment ?

If the PL/Java documentation that you are reading is what is online
at https://tada.github.io/pljava/ then it's worth remembering that what
is published there is the latest *released* version's documentation,
for 1.5.5 at the moment. So there's nothing in that version about the
function validator.

As you're working on the never-yet-released 1.6.0-SNAPSHOT branch,
the best way to read the documentation is to generate it locally

  mvn  site  site:stage

and then point your browser at the local file
  target/staging/index.html

However, just at the moment the javadoc build is failing for me.
I'm quite sure it worked in February when I committed 5ee9b6b.

This is one of those moments when I do not love Maven. I'll take
a look at what's broken this time.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Sat, 23 May 2020 11:58:44 -0400

> a look at what's broken this time.

Meanwhile, you can read the .md files for the documentation. ;)
Even let git show you what the differences are since V1_5_5.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Sun, 24 May 2020 01:20:53 -0400

>> a look at what's broken this time.

I have put up a branch bug/master/mjavadoc where the javadocs are
buildable again, so you should be able to build a complete set
of up-to-date docs.

They won't build with Java 9, 10, or 11 though. PL/Java will, but
it has to be 12 or later to build the javadocs. Just the way it is....

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Wed, 27 May 2020 22:12:56 +0530

> include that in a subset of the tested configurations rather than all
I think this is the major option left to be added to builds for Linux and
macOS. To which builds should I add the -Xcheck:jni flag. Also, I do not
know about  the -ea:org.postgresql.pljava... flag, can you please explain
it to me?

---

From: Kartik Ohri <[email-masked]>
Date: Wed, 27 May 2020 22:16:44 +0530

Anyone, who is interested in following the status of builds can do it at
https://travis-ci.com/github/amCap1712/pljava . It has a history of all
builds, configuration changes and the build logs available publicly.

---

From: Chapman Flack <[email-masked]>
Date: Wed, 27 May 2020 16:40:38 -0400

On 05/27/20 12:42, Kartik Ohri wrote:
> macOS. To which builds should I add the -Xcheck:jni flag

I don't think it will make much difference ... maybe start with a build
that's against the latest supported PG. Once things are going, maybe we
can get a better sense of which outputs from -Xcheck:jni are innocuous
and which ones really deserve attention, and write a filter that cuts
down that volume effectively, then it might be desirable to add the option
to more of the builds.

> Also, I do not know about  the -ea:org.postgresql.pljava... flag

Good question, you got me to look again at the 'java' manual page
and see that there is a long form for the option, -enableassertions.
I have been using the -ea form by habit because it is shorter to type,
but when putting it into a CI script the length doesn't matter, and it
would be better to say -enableassertions:org.postgresql.pljava...
and then it will not be as puzzling to the next person reading the CI
script.

If the idea of assertions isn't familiar, there's a very good
explanation in [1].

In a lot of programming languages, there will be something like

   assert <condition>;

where you are saying you are certain <condition> will be true
at that point in the program, because your analysis of the
possibilities leaves no way it can be false. So it is a check on
whether you've made an analytical mistake (or maybe a dumb typo
five lines earlier). It does nothing at all if the condition is
true (as it should be), and signals an error if it is false, so
you know there is a problem.

In production, after you're happy with testing results, sometimes
you don't want the overhead of checking assertions anymore; after all,
they are all testing conditions that are supposed to be true every time,
and doing nothing else useful.

Historically, in a language like C, 'assert' was provided
as a macro [2], and you could compile the code for production
with the macro defined to be nothing at all, so all your 'assert's
just disappear and there is no cost in the running code.

The disadvantage was that if some rare bug only showed up in
production, you would not have any of the assertions there
potentially showing where things went wrong. You would have to go
back and completely recompile with assertions enabled, and run
that version of the program, and hope the bug showed up again.

Because Java does just-in-time compilation in the JVM, they can
get the same efficiency when you don't want assertions checked,
but without that disadvantage. The assertions are always written
into the .class files, and you can decide at run time to give the
-enableassertions option or not, and if you don't, the JIT compiler
just leaves them out of the compiled code, but you can get them back
whenever you want by just running again with -enableassertions.

And as you can see, the option can be selective; you can
-enableassertions:org.postgresql.pljava... for testing all the
packages and subpackages in PL/Java but not in other libraries
or Java itself.

Naturally, CI testing is a context where we definitely want
to be running with assertions enabled.

Regards,
-Chap

[1] https://docs.oracle.com/javase/7/docs/technotes/guides/language/assert.html
[2] https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/assert.h.html

---

From: Kartik Ohri <[email-masked]>
Date: Fri, 29 May 2020 15:43:53 +0530

> down that volume effectively, then it might be desirable to add the option
I tested a build these -enableassertions and -Xcheck:jni flag enabled. The
output is at least over 30000 lines and causes the build to terminate
unsuccessfully as the it exceeds maximum supported log length. Please see
https://travis-ci.com/github/amCap1712/pljava/jobs/341362356 . I tried to
find but I couldn't find a flag to limit its log level to warning or above.
I have an idea to try to direct the output to a separate file instead to
standard output. And then search, the file for warnings and output those to
the standard log. There may be a few caveats I need to iron out but I think
it might work. Let me know your views on this.

---

From: Chapman Flack <[email-masked]>
Date: Fri, 29 May 2020 08:14:40 -0400

On 05/29/20 06:13, Kartik Ohri wrote:

> it might work.

I think that's the right approach. Is it possible Travis also might allow
a separate file to be saved as a result of the run, so it could be mentioned
in the log and inspected separately? That might be how we want to treat
hs_err_pid<n>.log files in the case of a crash, too.

My sense from running with -Xcheck:jni in the past is that a lot of the
messages /are/ warnings, but apparently warnings about usage in the JRE
itself that we're not in a position to fix (analogously to the C compiler
sign-conversion warnings that mostly pertain to code in the PostgreSQL
headers, not in PL/Java).

It may take some study and iteration to figure out patterns to search for
in that output that will be sensitive to the most useful messages. But
just getting the output saved somewhere is a good start.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Fri, 29 May 2020 09:21:49 -0400

> just getting the output saved somewhere is a good start.

Honestly, looks like the vast majority are local refs exceeding capacity in
com.sun.management.internal.DiagnosticCommandImpl.getDiagnosticCommandInfo.

I think a whole sequence like this is one warning and its stack trace:

INFO:  WARNING: JNI local refs: %d, exceeds capacity: %d
INFO:  	at com.sun.management.internal.DiagnosticCommandImpl.getDiag...
INFO:  	at ...
...(22 more lines)...
INFO:  	at java.lang.management.ManagementFactory.getPlatformMBeanServer...
INFO:  	- locked <%#016x>
INFO:  (a java.lang.Class for java.lang.management.ManagementFactory)
INFO:  	at org.postgresql.pljava.internal.DualState.<clinit>...

I was scratching my head why DualState.<clinit> would be getting called
so many times (I was expecting *one*), but I think what happens is it
starts the PlatformMBeanServer off in its own thread and that just reuses
the whole stack trace it inherited at birth forever.

I am pretty sure if sequences of just that one pattern were filtered out,
the result would be way, way shorter and the messages remaining in it
would be interesting ones.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Fri, 29 May 2020 09:33:42 -0400

> would be interesting ones.

I wrote too soon, there are more interesting ones later on. Probably the
most useful thing is to recognize the basic pattern of lines

  INFO:  WARNING: something
(
  INFO:   at somewhere
|
  INFO:   - locked <address>
  INFO:  (a something)
)*

and save the first line and the first 'at' line, and make a sorted,
uniq'd collection of those for the log, and save the original file
to dive into for more details.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Fri, 29 May 2020 23:08:22 +0530

> to dive into for more details.
Regarding this, Travis does not store any files after a job completes or
terminates. It recommends to use an external storage provider like AWS S3
to upload the logs to before the build finishes. Alternatively, I thought
we could email the logs as attachment to an email id in case of failures
but this might cause a lot of traffic as there at present 19 jobs in a
build. What are your thoughts?

---

From: Chapman Flack <[email-masked]>
Date: Fri, 29 May 2020 14:44:53 -0400

> build. What are your thoughts?

It might be ok to just filter it down to the actual messages and first
stack-trace lines (or maybe actual message, first line and last line of
stack trace), sorted and uniq'd and put into the log, and then allow the
file to go away. If something shows up that is interesting, it will
probably be reproducible in a local run.

I would be more interested in preserving hs_err_pid<n>.log files, but
I think those are less huge, and might just fit in the log.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Sat, 30 May 2020 14:05:27 +0530

I am now redirecting the output of the scripts to a file and trying to
filter using grep. I discovered that passing the -Xcheck:jni flag not
crashes travis but also the PostgreSQL database. Below is the postgres log
output. Can you shed some light on this?

020-05-30 08:02:10.952 UTC [10741] LOG:  starting PostgreSQL 12.3
(Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
11432020-05-30 08:02:10.952 UTC [10741] LOG:  listening on IPv4
address "127.0.0.1", port 5432
11442020-05-30 08:02:10.952 UTC [10741] LOG:  listening on IPv4
address "127.0.1.1", port 5432
11452020-05-30 08:02:10.954 UTC [10741] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
11462020-05-30 08:02:10.972 UTC [10753] LOG:  database system was shut
down at 2020-05-30 08:02:09 UTC
11472020-05-30 08:02:10.978 UTC [10741] LOG:  database system is ready
to accept connections
11482020-05-30 08:04:14.658 UTC [10741] LOG:  received SIGHUP,
reloading configuration files
11492020-05-30 08:04:14.659 UTC [10741] LOG:  parameter
"pljava.libjvm_location" changed to
"/home/travis/openjdk14/lib/server/libjvm.so"
11502020-05-30 08:04:14.713 UTC [10741] LOG:  received SIGHUP,
reloading configuration files
11512020-05-30 08:04:14.714 UTC [10741] LOG:  parameter
"pljava.vmoptions" changed to
"-enableassertions:org.postgresql.pljava... -Xcheck:jni"
11522020-05-30 08:04:54.113 UTC [10741] LOG:  server process (PID
12377) was terminated by signal 6: Aborted
11532020-05-30 08:04:54.113 UTC [10741] DETAIL:  Failed process was
running: CREATE EXTENSION pljava;
11542020-05-30 08:04:54.113 UTC [10741] LOG:  terminating any other
active server processes
11552020-05-30 08:04:54.114 UTC [10758] WARNING:  terminating
connection because of crash of another server process
11562020-05-30 08:04:54.114 UTC [10758] DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
11292020-05-30 08:04:54.114 UTC [10758] HINT:  In a moment you should
be able to reconnect to the database and repeat your command.
11302020-05-30 08:04:54.121 UTC [10741] LOG:  all server processes
terminated; reinitializing
11312020-05-30 08:04:54.170 UTC [12415] LOG:  database system was
interrupted; last known up at 2020-05-30 08:02:10 UTC
11322020-05-30 08:04:54.225 UTC [12415] LOG:  database system was not
properly shut down; automatic recovery in progress
11332020-05-30 08:04:54.228 UTC [12415] LOG:  redo starts at 0/164E5E8
11342020-05-30 08:04:54.231 UTC [12415] LOG:  invalid record length at
0/169CF30: wanted 24, got 0
11352020-05-30 08:04:54.231 UTC [12415] LOG:  redo done at 0/169CEE8
11362020-05-30 08:04:54.235 UTC [12429] postgres@postgres FATAL:  the
database system is in recovery mode
11372020-05-30 08:04:54.259 UTC [10741] LOG:  database system is ready
to accept connections
11382020-05-30 08:04:54.290 UTC [12448] postgres@postgres ERROR:
schema "sqlj" does not exist at character 8
11392020-05-30 08:04:54.290 UTC [12448] postgres@postgres STATEMENT:
SELECT sqlj.set_classpath('public', 'saxon');
11402020-05-30 08:04:54.336 UTC [12462] postgres@postgres ERROR:
schema "sqlj" does not exist at character 8
11412020-05-30 08:04:54.336 UTC [12462] postgres@postgres STATEMENT:
SELECT sqlj.install_jar('file:/home/travis/build/amCap1712/pljava/pljava-examples/target/pljava-examples-1.6.0-SNAPSHOT.jar','examples',true);
1142
11572020-05-30 08:04:54.384 UTC [12477] postgres@postgres STATEMENT:
SELECT sqlj.get_classpath('javatest');
11582020-05-30 08:04:54.430 UTC [12490] postgres@postgres ERROR:
schema "sqlj" does not exist at character 8
11592020-05-30 08:04:54.430 UTC [12490] postgres@postgres STATEMENT:
SELECT sqlj.set_classpath('javatest', 'examples');
11602020-05-30 08:04:54.475 UTC [12503] postgres@postgres ERROR:
schema "javatest" does not exist at character 8
11612020-05-30 08:04:54.475 UTC [12503] postgres@postgres STATEMENT:
SELECT javatest.java_addone(3);
11622020-05-30 08:07:19.280 UTC [10741] LOG:  received SIGHUP,
reloading configuration files
11632020-05-30 08:07:29.750 UTC [10741] LOG:  received SIGHUP,
reloading configuration files
11642020-05-30 08:07:48.355 UTC [10741] LOG:  server process (PID
12610) was terminated by signal 6: Aborted
11652020-05-30 08:07:48.355 UTC [10741] DETAIL:  Failed process was
running: CREATE EXTENSION pljava;
11662020-05-30 08:07:48.355 UTC [10741] LOG:  terminating any other
active server processes
11672020-05-30 08:07:48.357 UTC [12435] WARNING:  terminating
connection because of crash of another server process
11682020-05-30 08:07:48.357 UTC [12435] DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
11692020-05-30 08:07:48.357 UTC [12435] HINT:  In a moment you should
be able to reconnect to the database and repeat your command.
11702020-05-30 08:07:48.359 UTC [10741] LOG:  all server processes
terminated; reinitializing
11712020-05-30 08:07:48.371 UTC [12629] LOG:  database system was
interrupted; last known up at 2020-05-30 08:04:54 UTC
11722020-05-30 08:07:48.412 UTC [12629] LOG:  database system was not
properly shut down; automatic recovery in progress
11732020-05-30 08:07:48.415 UTC [12629] LOG:  redo starts at 0/169CFA8
11742020-05-30 08:07:48.417 UTC [12629] LOG:  redo done at 0/16EBFA8
11752020-05-30 08:07:48.436 UTC [10741] LOG:  database system is ready
to accept connections
11762020-05-30 08:11:29.901 UTC [10741] LOG:  received SIGHUP,
reloading configuration files
11772020-05-30 08:11:29.955 UTC [10741] LOG:  received SIGHUP,
reloading configuration files
11782020-05-30 08:11:29.956 UTC [10741] LOG:  parameter
"pljava.vmoptions" changed to
"-enableassertions:org.postgresql.pljava..."

---

From: Chapman Flack <[email-masked]>
Date: Sat, 30 May 2020 10:42:02 -0400

> running: CREATE EXTENSION pljava;

Well, I'd say it looks like your work has caught its first PL/Java bug. :)
I've been able to reproduce it here. Right during CREATE EXTENSION too,
not even running the examples later. Good thing this is on 1.6.0-SNAPSHOT...

So, I would say, retain all the work you've done on it so far, but
make it possible to run without -Xcheck:jni for now, until I track
down the bug(s). You might keep working on scripting to condense the
-Xcheck:jni output. It should probably have found something like this:

INFO:  FATAL ERROR in native method: Bad global or local ref passed to JNI
INFO:  	at
org.postgresql.pljava.internal.ExecutionPlan._execute([email-masked]/Native
Method)
...
INFO:  	at
org.postgresql.pljava.internal.InstallHelper.groundwork([email-masked]/InstallHelper.java:174)

By the way, have you found any way to redirect just the -Xcheck:jni
output somewhere, or only it in combination with other stderr from the
backend?

If it can only be redirected in combination with other stuff, I would
be rather conservative about filtering it. I think I would just want to
recognize and condense the

 WARNING: JNI local refs: %d, exceeds capacity: %d
(
  at ...
|
  - locked <...>
 (a ...)
)*

pattern, which makes up the bulk of the output, and leave anything else
untouched. We don't want to clobber other useful information in the logs,
and even anything from -Xcheck:jni that isn't a local refs capacity warning
should probably be left complete. If it's a FATAL ERROR it won't make the
log file too big, 'cause there'll only be one. :)

Diagnostic messages from Java code generally follow one of these paths:

- they get explicitly sent to PostgreSQL's elog system, and go wherever
  the PostgreSQL logging is pointed, or suppressed depending on the
  enabled log level

- Java exceptions caught and turned into PostgreSQL errors go into the
  PostgreSQL logs at level ERROR.

- Separately, the stack traces of those exceptions are output if the
  PostgreSQL log level is set to DEBUG1 or finer. The code adjusts
  the vfprintf-hook log level (see below) to DEBUG1 before printing
  the stack trace, but I'm not positive it isn't just going to System.err
  and not through the hook. Should be easy enough to confirm, I just
  haven't had to before.

- Messages from the JVM itself that are emitted through a 'vfprintf hook'
  are caught by PL/Java's hook [1] and injected into PostgreSQL's
  elog system at a selectable level, usually INFO. (That is probably
  the path being followed by the -Xcheck:jni messages, explaining
  why they have INFO: in front.)

- Messages that are simply written to Java's System.err *can be* caught
  and included in PostgreSQL's logs, depending on PostgreSQL's
  logging_collector setting. [2] Otherwise, they just go wherever stderr
  was pointing when the PostgreSQL server was started (or wherever
  the server startup scripts point it). That could be, for example,
  the systemd journal.

While filtering with the *idea* of grep seems like the right idea,
I'm not sure the actual grep command will be an easy way to do that.
The 'pattern'

 WARNING: JNI local refs: %d, exceeds capacity: %d
(
  at ...
|
  - locked <...>
 (a ...)
)*

is obviously regular-expression pseudocode, but it's an expression that
spans over multiple lines.

If I were doing it, I would probably write a bit of Python (or jshell,
if you prefer Java) and slurp in the whole log file; conveniently,
both Python and Java have a MULTILINE regular expression option, so
if you wanted you really could translate that pseudocode into a Python
or Java regex and apply it over the whole file across line boundaries.
(Java finally added a method to readAllBytes from a file [3], though
it didn't happen until version 7!)

Regards,
-Chap

[1]
https://github.com/tada/pljava/blob/efbbc1d/pljava-so/src/main/c/Backend.c#L200

[2]
https://www.postgresql.org/docs/9.5/runtime-config-logging.html#GUC-LOGGING-COLLECTOR

[3]
https://docs.oracle.com/javase/7/docs/api/java/nio/file/Files.html#readAllBytes(java.nio.file.Path)

---

From: Chapman Flack <[email-masked]>
Date: Sun, 31 May 2020 12:00:08 -0400

>   and not through the hook. Should be easy enough to confirm, I just

I've just confirmed this with gdb; an exception stack trace emitted
with printStackTrace [0] goes straight to System.err and the vfprintf hook
is not involved, so the surrounding calls to setJavaLogLevel aren't
really accomplishing anything.

>   why they have INFO: in front.)

Confirmed also: -Xcheck:jni messages go through the vfprintf hook, so
they are always funneled into the PostgreSQL server log, regardless of
the setting of logging_collector.

-Chap

[0]
https://github.com/tada/pljava/blob/efbbc1d/pljava-so/src/main/c/JNICalls.c#L82

---

From: Chapman Flack <[email-masked]>
Date: Sun, 31 May 2020 14:15:10 -0400

> not even running the examples later. Good thing this is on 1.6.0-SNAPSHOT...

PR #275 should fix the one during CREATE EXTENSION. Now I'm running into
another later running the examples. I'll look for that next.

The bug you caught has been there for fifteen years. :)

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Sun, 31 May 2020 23:23:54 -0400

>>   why they have INFO: in front.)

Exploiting that fact, I decided to just condense out those messages (only
the ones coming from Java's own JMX classes, outside our power to fix)
right at the source, by putting a small state machine in the vfprintf hook
to recognize and not log them. That's in PR #276.

Also in that PR, changes to fix the remaining -Xcheck:jni messages that
were really coming from PL/Java. For the moment, in my environment,
with PR #275 and #276 applied, it builds and tests -Xcheck:jni clean.

That should eliminate any need for doing fancy stuff with those messages
in the CI configuration. Any new issues that crop up in development later
ought to produce a smaller volume of messages that we want to know about.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Mon, 1 Jun 2020 14:58:11 +0530

> ought to produce a smaller volume of messages that we want to know about.
So we do not need a custom log filter now, right ?

---

From: Chapman Flack <[email-masked]>
Date: Mon, 1 Jun 2020 07:44:16 -0400

> So we do not need a custom log filter now, right ?

Right. And it's probably ok to include -Xcheck:jni in all configurations
(once PRs #275 and #276 are merged, of course).

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Mon, 1 Jun 2020 17:34:47 +0530

Great! So, I'll begin work on setting up CI for Windows. Once, these PRs
are merged I'll update my repository and run a build with -Xcheck:jni in
all configurations to a full test for Linux and macOS.

---

From: Kartik Ohri <[email-masked]>
Date: Wed, 3 Jun 2020 22:54:24 +0530

Hi! I have begun the setup on Windows. Here, is the log
https://ci.appveyor.com/project/amCap1712/pljava.I get the error Command
executed with exception: ERROR: ALTER SYSTEM cannot run inside a
transaction bloc at psql -c "SET pljava.libjvm_location to '$libjvm'; ALTER
SYSTEM SET pljava.libjvm_location to '$libjvm'; SELECT pg_reload-conf();"
-U postgres . Can you help with this?

---

From: Chapman Flack <[email-masked]>
Date: Wed, 3 Jun 2020 14:09:15 -0400

On 06/03/20 13:24, Kartik Ohri wrote:
> ERROR: ALTER SYSTEM cannot run inside a
> -U postgres .

Hmm. psql does have a --single-transaction mode that, if enabled, makes
psql send a BEGIN before your first command and a COMMIT after the last
one, so that if you have multiple commands, they all happen in one
transaction. [1]

If one of your commands is something that's not allowed inside
a transaction block, that of course will be a problem.

What surprises me is that you see this happening on Windows only.
I wonder if psql is built with different option defaults there? Or could
there be a .psqlrc (or whatever it is called in Windows) that is setting
that option? Those are the kind of things I would look for.

That said, there are alternatives to the whole ALTER SYSTEM / pg_reload_conf
business. If you are going to include all of the testing commands within
one psql connection, there isn't any need for the ALTER SYSTEM or reload,
you can just send the SET commands first and then the others in the same
session. Or, if you do want to save the settings for later reconnection,
you could also do that with ALTER DATABASE or ALTER USER, and I think
both of those will work inside transactions.

Another alternative is to simply pass the settings with -c when
starting the PostgreSQL server [2]:

postgres -c pljava.libjvm_location="$libjvm" \
  -c pljava.vmoptions='-ea:org.postgresql.pljava... -Xcheck:jni'

(Note I'm writing Unixy syntax here; I don't use Windows enough to be
sure of the right way to quote that, but it gives you the idea.)

Those settings will apply to all sessions started in that run of the server.
It would be odd to run a production server that way, but one that is just
being started to run some tests, why not?

And still another alternative is just to send the options as part of the
psql connection string [3]:

psql "dbname=postgres options='-c pljava.libjvm_location=$libjvm
  -c pljava.vmoptions=-ea:org.postgresql.pljava...\\\ -Xcheck:jni'"

... where again, I'm showing Unixy syntax. The tricky bit is getting a
space inside a single option setting, which you can see takes three
backslashes in the above. I have no idea what would be correct in Windows
but if you figure it out, it's good practice for the Maven plugin work.

Settings sent with the psql connection of course are in effect just for
that session.

Regards,
-Chap

[1] https://www.postgresql.org/docs/13/app-psql.html#R1-APP-PSQL-3

[2] https://www.postgresql.org/docs/13/app-postgres.html#id-1.9.5.14.6.3

[3] https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-CONNSTRING

---

From: Kartik Ohri <[email-masked]>
Date: Fri, 5 Jun 2020 19:14:25 +0530

Hi! I think ALTER USER will do the job but I am again facing an issue with
the pljava.libjvm_location. This works fine on Linux and macOS but not on
Windows.  I have tried escaping the / and used \ as well but none seems to
be working.

Here are the pljava variables.

select name, source, setting from pg_settings where name like 'pljava.%';
                name                              | source  |
                                                      setting

----------------------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
 pljava.debug                                  | default | off
 pljava.enable                                 | default | on
 pljava.implementors                      | default | postgresql
 pljava.java_thread_pg_entry         | default | allow
 pljava.libjvm_location                    | session | C:/Program
Files/Java/jdk14/bin/server/jvm.dll
 pljava.module_path                       | default | C:/Program
Files/PostgreSQL/12/share/pljava/pljava-1.6.0-SNAPSHOT.jar;C:/Program
Files/PostgreSQL/12/share/pljava/pljava-api-1.6.0-SNAPSHOT.jar
 pljava.release_lingering_savepoints | default | off
 pljava.statement_cache_size        | default | 11
 pljava.vmoptions                           | default |

And here is the psql log,

2020-06-05 13:25:06.558 UTC [5652] LOG:  database system was shut down at
2020-05-27 01:18:49 UTC
2020-06-05 13:25:06.585 UTC [1056] LOG:  database system is ready to accept
connections
2020-06-05 13:27:20.207 UTC [1056] LOG:  received SIGHUP, reloading
configuration files
2020-06-05 13:27:20.385 UTC [3096] WARNING:  Java virtual machine not yet
loaded
2020-06-05 13:27:20.385 UTC [3096] DETAIL:  Access is denied.

2020-06-05 13:27:20.385 UTC [3096] HINT:  SET pljava.libjvm_location TO the
correct path to the jvm library (libjvm.so or jvm.dll, etc.)
2020-06-05 13:27:20.395 UTC [3096] ERROR:  cannot use PL/Java before
successfully completing its setup
2020-06-05 13:27:20.395 UTC [3096] HINT:  Check the log for messages
closely preceding this one, detailing what step of setup failed and what
will be needed, probably setting one of the "pljava." configuration
variables, to complete the setup. If there is not enough help in the log,
try again with different settings for "log_min_messages" or
"log_error_verbosity".
2020-06-05 13:27:20.395 UTC [3096] STATEMENT:  CREATE EXTENSION pljava;
2020-06-05 13:36:27.036 UTC [5152] ERROR:  unrecognized configuration
parameter "client_min_messgaes"
2020-06-05 13:36:27.036 UTC [5152] STATEMENT:  set client_min_messgaes to
DEBUG1;
2020-06-05 13:36:32.400 UTC [5152] ERROR:  unrecognized configuration
parameter "client_min_messgages"
2020-06-05 13:36:32.400 UTC [5152] STATEMENT:  set client_min_messgages to
DEBUG1;
2020-06-05 13:37:58.758 UTC [5152] WARNING:  Java virtual machine not yet
loaded
2020-06-05 13:37:58.758 UTC [5152] DETAIL:  Access is denied.

2020-06-05 13:37:58.758 UTC [5152] HINT:  SET pljava.libjvm_location TO the
correct path to the jvm library (libjvm.so or jvm.dll, etc.)
2020-06-05 13:37:58.762 UTC [5152] ERROR:  cannot use PL/Java before
successfully completing its setup
2020-06-05 13:37:58.762 UTC [5152] HINT:  Check the log for messages
closely preceding this one, detailing what step of setup failed and what
will be needed, probably setting one of the "pljava." configuration
variables, to complete the setup. If there is not enough help in the log,
try again with different settings for "log_min_messages" or
"log_error_verbosity".
2020-06-05 13:37:58.762 UTC [5152] STATEMENT:  CREATE EXTENSION pljava;
2020-06-05 13:38:50.100 UTC [5152] WARNING:  Java virtual machine not yet
loaded
2020-06-05 13:38:50.100 UTC [5152] DETAIL:  Access is denied.

2020-06-05 13:38:50.100 UTC [5152] HINT:  SET pljava.libjvm_location TO the
correct path to the jvm library (libjvm.so or jvm.dll, etc.)
2020-06-05 13:39:26.369 UTC [5152] WARNING:  Java virtual machine not yet
loaded
2020-06-05 13:39:26.369 UTC [5152] DETAIL:  Access is denied.

2020-06-05 13:39:26.369 UTC [5152] HINT:  SET pljava.libjvm_location TO the
correct path to the jvm library (libjvm.so or jvm.dll, etc.)
2020-06-05 13:39:26.374 UTC [5152] ERROR:  cannot use PL/Java before
successfully completing its setup
2020-06-05 13:39:26.374 UTC [5152] HINT:  Check the log for messages
closely preceding this one, detailing what step of setup failed and what
will be needed, probably setting one of the "pljava." configuration
variables, to complete the setup. If there is not enough help in the log,
try again with different settings for "log_min_messages" or
"log_error_verbosity".

I hope you can shed some light on this.

---

From: Chapman Flack <[email-masked]>
Date: Fri, 5 Jun 2020 10:32:14 -0400

> 2020-06-05 13:27:20.385 UTC [3096] HINT:  SET pljava.libjvm_location TO the

My attention is drawn most strongly to the "Access is denied." That isn't
a familiar error to me from non-Windows environments, so it might indicate
some Windows-specific access control policy that is preventing a file
being opened.

I would not read too much into what the HINT says; it is just a constant
string in the PL/Java source. It mentions libjvm_location because that's
a usual suspect, but it doesn't really know why the dlopen failed. The
DETAIL is the best information for that.

It might help some to say

  \set VERBOSITY verbose

(a local command in psql, from the department of redundancy department),
and

  set client_min_messages to debug2;  -- it has to be spelled right :)

or even to experiment with pg_ls_dir(), pg_stat_file(),
pg_read_binary_file() [1] with the name of the JVM DLL or the directory
it lives in, to see if PostgreSQL always gets "Access is denied".
Presumably, if you spell the name wrong on purpose, you might get a
different error for that, and then you'll know this isn't a matter
of spelling the name right.

I found a StackOverflow thread [2] about some Windows settings that
might be relevant. Most of the answers there suggest changing settings
through the GUI (file/directory properties or service properties). I
didn't see anything about how to change from a script, though I assume
with PowerShell there is probably a way.

If this turns out to be a necessary step it will be good to get the
details into the PL/Java documentation for Windows, because I'm
pretty sure there's nothing about it in there now.

One more thing about setting client_min_messages; PL/Java only looks once,
just when starting the JVM, so for that setting to affect PL/Java, it has
to come before the first statement that causes PL/Java to load,

Regards,
-Chap

[1]
https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

[2]
https://stackoverflow.com/questions/4267051/error-5-access-denied-when-starting-windows-service

---

From: Kartik Ohri <[email-masked]>
Date: Sat, 6 Jun 2020 18:10:26 +0530

> it lives in, to see if PostgreSQL always gets "Access is denied".

I have played around a bit using your suggestions. It seems this is indeed
a permissions issue. The good thing is I was able to find a solution to it.

> pretty sure there's nothing about it in there now.

The solution is to use *pg_ctl* to start the postgres server from a shell
with appropriate permissions. Earlier, I was trying to start the postgres
server using *net start *in CMD and *Start-Service* in PowerShell. Even
with privileged shells, postgres created using these commands do not have
the necessary rights to some directories (including the Java directory,
hence, the libjvm location error). I was unable to figure out why this
happens.

I am now using pg_ctl to start the database. This is causing some issues in
the normal mode. The build stalls right after postgres starts. This does
not happen when the server is started using the other 2 options. I am
working on fixing this. However, in the interactive debug mode, I have been
able to use pg_ctl with success. I have run all the usual tests as on other
platforms except the -Xcheck:jni and all of these work.

Once this issue is fixed we will have a fully functional MSVC Windows
build. I'll let you know once this is done. Thanks.

---

From: Chapman Flack <[email-masked]>
Date: Sat, 6 Jun 2020 10:19:05 -0400

> hence, the libjvm location error). I was unable to figure out why this

Interesting! There are a bunch of #ifdef WIN32 sections in pg_ctl.c [1],
maybe some useful info in there.

Regards,
-Chap

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/pg_ctl/pg_ctl.c;hb=HEAD

---

From: Kartik Ohri <[email-masked]>
Date: Mon, 15 Jun 2020 22:17:09 +0530

The pg_ctl stall issue has been fixed.

I am now trying to work around the Powershell streams. It seems psql writes
all its output including info, debug to error stream whereas Powershell has
a different stream for each of errors, warnings, debug and info. I have
been able to redirect all the output to stdout instead. However, this
redirects errors as well without failing the build. I am thinking of
manually processing the output to catch errors and manually terminate the
build in such a case. However, I think there might be a simpler solution to
use psql in quiet mode. What are your views on this ?

---

From: Chapman Flack <[email-masked]>
Date: Mon, 15 Jun 2020 13:23:24 -0400

> use psql in quiet mode. What are your views on this ?

I was thinking about that last night again. The current "tests" in the
examples are written to report most problems they test for as warnings
and not errors, because the first error would stop everything, and I would
rather see as many issues as possible. (Of course you still get an error
if the test itself actually breaks, which also counts as finding a problem!
You just don't find the ones after it then.)

I was looking through psql's docs again last night because I really wish
it had some kind of

\if any-warnings-happened
  \quit status=1
\endif

which seem like surprising things for it to be missing, but as far as
I can tell, it really is missing both: no way to test for warnings (it's
clearly possible, because server responses do include them), and no way
to specify an exit status to \quit. Weird.

(You might check the psql docs yourself to be sure I haven't missed
something. It seems very weird for those abilities to be lacking, but
I'm honestly not seeing them.)

In part, this is because the tests in the examples DDR aren't written
with a more conventional testing framework. What I would really like to
do is move to a combination of pgTAP in the database and tap4j in Java,
and integrate them by providing a method maybe executeQueryAsTAP(qry),
where you give it an SQL qry that produces pgTAP output, and the method
knows that and parses the results and gives you a tap4j results object.
Also the inverse, a PL/Java function that returns a tap4j object as a
TAP-formatted query result.

That's a longer-term goal; there were a couple prerequisites needing
to happen in tap4j, and I sent one and they merged it [1], and I suggested
another and didn't have time to do it myself and no one else has yet [2],
though I see they recently added a "good first issue" tag for it.

Of course assuming the tap4j prerequisites get implemented, there'd
still be the matter of rewriting all the PL/Java tests as tap. Which is
definitely what I want, but it would still be work.

So for now we're stuck with test failures as warnings, and psql having
(unless I missed something) no good way to tell you there were warnings.
That leads down the ugly road of you trying to parse log text and look
for warnings, and not make the nar-maven-plugin-like mistake of thinking
later lines of warnings are errors, or be fooled by keywords inside other
messages, and all of those well-known horrors.

Unless ... we use something besides psql to connect to the database and
run the tests. For example, make travis_setup_pljava and travis_test_pljava
into a Python script using one of Python's pgsql drivers (there are a few
choices) to connect to the server and run the SET, CREATE EXTENSION, and
SELECT commands, and notice any warnings. Or the same in another language
if you prefer it to Python.

(I'm not very familiar with Powershell but I was thinking it sounded
powerful enough it might directly support ODBC connections or something,
and sure enough, [3].)

Then it's a simple matter of using a real API to do the queries and
retrieve whatever warnings came back.

It is worth checking that the choice of script language and PostgreSQL
driver will actually support retrieving warnings! They all should,
but apparently there's a driver for Go at least [4] that doesn't. That
would be frustrating, especially to find out after doing some work.

A final issue is whether to try to do this in one common script language
across platforms. It might be natural to do something in Python on Linux
and in Powershell on Windows (though I guess you can get Python on Windows
or even Powershell on Linux, but relying on an ODBC driver binary could
add complexity).

One language guaranteed to be present on any platform for testing PL/Java
will be ... Java, and that comes with jshell starting with Java 9, so
there could be that argument for writing a jshell script and using pgjdbc.
Fewer other dependencies then, and no native binary ones.

Regards,
-Chap

[1] https://github.com/tupilabs/tap4j/pull/38
[2] https://github.com/tupilabs/tap4j/issues/39
[3]
https://stackoverflow.com/questions/9217650/connect-to-remote-postgresql-database-using-powershell
[4]
https://stackoverflow.com/questions/42070023/how-to-get-postgresql-procedure-warning-messages

---

From: Kartik Ohri <[email-masked]>
Date: Wed, 17 Jun 2020 22:45:29 +0530

> and sure enough, [3].)

Out of the box, Powershell only supports MySQL server. ODBC drivers will be
required to be built from source for use with Powershell which I think will
not be worth the complexity. JDBC driver for Java or using python may be a
viable option. I haven't had much success with the python version though.

In any case, a very simple query suppressed INFO outputs SET
client_min_messages TO WARNING. What should be the behaviour if warnings
are found ? Should be marked as failed or passing.

---

From: Kartik Ohri <[email-masked]>
Date: Thu, 18 Jun 2020 00:06:07 +0530

Also, I saw that pljava was failing on Java 15 early builds. Doing some
digging, I found Nashorn Engine is proposed to be removed from Java 15. So,
I think it might be the probable cause.

---

From: Chapman Flack <[email-masked]>
Date: Wed, 17 Jun 2020 15:25:32 -0400

> I think it might be the probable cause.

Good catch. So it's gone in 15, eh? I was hoping it might be around
a little longer.

That means it'll be time to add a profile with build-time dependencies
on org.graalvm.js:js and org.graalvm.js:js-scriptengine. That ought to
handle the uses of JS in the build itself (modulo whatever Nashorn ->
GraalJS compatibility kinks we discover).

I see no reason to also depend on the Graal compiler as described in [1];
what JS is being used for in the build doesn't require speed, so we can
keep it simple.

That will leave the problem of the snippets of JS in the self-installer
jar. I really want that jar to Just Work when run on anybody's Java runtime,
without making them download other stuff first.

Copying/shading js.jar and js-scriptengine.jar into it might work, modulo
licensing issues, but it would grow what's now about a 1 MB jar to 16 MB.
Ouch!

Probably time to work on the self-installer Java code and just build in
the work that is currently done in the included JS. That makes it less
transparent and configurable, but it hasn't needed to change much in
several years so it's probably ok.

I wonder if there is some very small, BSD-licensed javax.script engine
for some other language that would work.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Wed, 17 Jun 2020 15:31:17 -0400

On 06/17/20 13:15, Kartik Ohri wrote:
> not be worth the complexity.

Agreed.

> JDBC driver for Java or using python may be a

I was looking at the various Python driver choices myself the other night
and it wasn't as promising as I thought. pg8000 was the pure-Python one
that I remembered, but can't access warnings (as far as I can tell).
py-postgresql looks quite promising and has message hooks, but even it
has native components, as do several others. I stopped before completing
an exhaustive survey.

There's a lot to like about a Java and JDBC approach. It shouldn't be hard
to get off the ground, JDBC has a nice standard API for finding out about
warnings (and reading through the pgJDBC code, I believe it is implemented,
though I haven't done a test yet).

Later on, as the prerequisites for migrating to TAP get accomplished,
that code could just be evolved to match.

> are found ? Should be marked as failed or passing.

My convention in the existing tests has been to use INFO to report
success, and WARNING to report failure. The idea would be to let the
test progress as far as it can, even complete if possible, but give an
overall not-passing result if there were warnings.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Wed, 17 Jun 2020 15:33:12 -0400

> I see no reason to also depend on the Graal compiler as described in [1];

oops.

[1]
https://medium.com/graalvm/graalvms-javascript-engine-on-jdk11-with-high-performance-3e79f968a819

---

From: Kartik Ohri <[email-masked]>
Date: Thu, 18 Jun 2020 01:12:41 +0530

> that code could just be evolved to match.
As of now, setting client log level seems to work very well. Should I try
to work out this approach or go ahead?

---

From: Chapman Flack <[email-masked]>
Date: Wed, 17 Jun 2020 15:53:59 -0400

> to work out this approach or go ahead?

That sounds adequate for now. Especially if log_min_messages is still
at INFO (or could even go finer than that), and you can limit just the
client messages to warning, use that to detect success/failure, but put
the backend log into the build log.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Fri, 19 Jun 2020 21:24:31 +0530

On windows, do we want to have all combinations of Java and PostgreSQL with
both mingw and MSVC?

---

From: Chapman Flack <[email-masked]>
Date: Fri, 19 Jun 2020 12:16:55 -0400

> On windows, do we want to have all combinations of Java and PostgreSQL with

As an ultimate goal, I think that's desirable.

If it comes to a matter of managing time, I could see starting with
a few points in the space (say, Java 9 because it's intended to be the
earliest supported in 1.6, Java 11 because it's LTS, the latest Java
that currently works, to catch regressions), with intermediate points
filled in later.

It is good to test all targeted PG versions, at least for MSVC, because
there have been recurring issues with different PG globals lacking the
PGDLLIMPORT declaration in different versions.

That may be less important with Mingw-w64 which (I think) does not rely
on the PGDLLIMPORT declarations.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Sat, 20 Jun 2020 11:30:29 +0530

Hi,
I am having trouble with building pljava in mingw. I have attached the log
as well. gcc, g++, java, maven and pg_config are all present and working.
OpenSSL is also installed using pacman prior to building. I think there is
something wrong with the include paths and other system variables. Can you
take a look at the log and let me know your views about the probable cause ?

---

From: Chapman Flack <[email-masked]>
Date: Sun, 21 Jun 2020 16:08:43 -0400

Hi,

About the large attachment that just went to the list: it was held
for moderation because of the size, and we shared the attachment more
efficiently as a github issue and solved it.

But I had not yet used my moderator powers to make the held message
disappear, and it seems it got released from the mod queue anyway.
I wasn't expecting that.

Sorry for the size.

Regards,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Sun, 21 Jun 2020 18:42:05 -0400

> GraalJS compatibility kinks we discover).

I have pushed a branch chore/master/denashorn that does that exactly,
when Java 15 or later is detected. It also sets the system property
polyglot.js.nashorn-compat, which lets the existing JavaScript bits
continue to work without any changes.

> several years so it's probably ok.

I ended up refactoring it just slightly so it can be usefully subclassed,
then writing a PL/Java-specific subclass that handles the pathname mapping.
So now it's a self-extracting jar made by adding two extra .class files
rather than just one. Relatively painless.

Kartik, could you check that the branch works with Java 15 in CI?

Now that there is a small PL/Java-specific class file inside the
installer jar, I may go ahead and give it a few extra small methods
useful for testing, just so they could be run from jshell with a
classpath pointing to the jar. It would be a sensible place to put them.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Mon, 22 Jun 2020 09:46:21 +0530

> Kartik, could you check that the branch works with Java 15 in CI?
Hi! The branch passes the CI with both Java 14 & Java 15.

---

From: Kartik Ohri <[email-masked]>
Date: Wed, 24 Jun 2020 02:09:17 +0530

The mingw build is working but it spins off its new console which makes it
impossible to detect errors and retrieve logs. It even stalls the build. I
have been trying to figure a way to get past this but have been unable to
do so till now. Do you have any suggestions on how to fix this ?

---

From: Chapman Flack <[email-masked]>
Date: Tue, 23 Jun 2020 16:56:39 -0400

> do so till now. Do you have any suggestions on how to fix this ?

I have never experienced a Mingw-w64 build personally, so I don't have
much insight into the details. I understand that it involves starting
a shell/console within which the environment is set up for doing the build.

I've always assumed that isn't necessary for running the built artifacts.
Can the Mingw-w64-built PostgreSQL server be run as an ordinary executable?
If it can, then the test run after building should work in the usual way.

I assume that when inside the msys2 shell, the filesystem you see is still
the same filesystem, so redirecting output or error output of the commands
into a file ought to work, and the file could be read outside of msys2.

According to a MinGW mailing list thread [1], it looks possible to
redirect output and error output, either using the familiar > and 2>
or by using a 'redir' command in msys (which mustn't be confused with
a standard Microsoft "network redirector" command of the same name).

I also gather from that thread that there's an "echo %ERRORLEVEL%" to
show the exit status of a command, so that could be used after the mvn
command to get an indication of whether Maven thought it had succeeded.

If these are things you've already tried and there were issues, some more
details of what seems to go wrong where might help.

Regards,
-Chap

[1] https://sourceforge.net/p/mingw/mailman/message/16209368/

---

From: Kartik Ohri <[email-masked]>
Date: Wed, 24 Jun 2020 22:44:10 +0530

Hi! I was able to fix the mingw issue through another way. The build matrix
is here https://ci.appveyor.com/project/amCap1712/pljava/builds/33715210/.
Appveyor does not have JDK 9 and 10. I have written a script for those
versions to install them. As you can see, Java 9, PG9 and PG10 are failing.
I am investigating these. Also, it seems that msys2 only has postgres12
pre-built packages. If we want to test for other versions, we may need to
build from source. What are your thoughts on this?

---

From: Chapman Flack <[email-masked]>
Date: Wed, 24 Jun 2020 13:23:37 -0400

> build from source. What are your thoughts on this?

I think that's great news and when I get time later today I will look at
the failing Java 9, PG9, PG10 logs.

As for the msys2 coverage of PostgreSQL versions, that sounds like the
kind of gap we might eventually want to return to and fill in, but it
doesn't have to be right now. Having this much of a CI matrix is worlds
better than before, and this might be a good time to shift more (not
necessarily all, but more) attention to the remaining parts of the GSoC
project.

Regards,
-Chap

---

From: Kartik Ohri <[email-masked]>
Date: Thu, 25 Jun 2020 01:01:19 +0530

> the failing Java 9, PG9, PG10 logs.

For Java 9 it is a bit subtle, the tests don't fail but every time a JNI
refs warning is emitted. This raises a false alarm and the build fails. In
the case of PG9 and PG10 with MSVC, a test in examples.jar fails
probably. org.postgresql.pljava.example.LoggerTest
SetOfRecordTest not ok

> necessarily all, but more) attention to the remaining parts of the GSoC

 Yeah, sure. I am exploring the maven plugin and have been facing some
issues. I'll try to debug it for another day or two before asking for help.
Meanwhile, could you make the introductions with the pgJDBC maintainer of
org.postgresql endpoint at maven so that we could start work on the next
part?

---

From: Chapman Flack <[email-masked]>
Date: Wed, 24 Jun 2020 19:41:20 -0400

On 06/24/20 15:31, Kartik Ohri wrote:

> refs warning is emitted. This raises a false alarm and the build fails.

I see that. It appears to be coming from java.lang.System.initProperties,
not anywhere we can fix, so it would be reasonable to just filter that out
if possible.

> In the case of PG9 and PG10 with MSVC, a test in examples.jar fails

I was just looking at this matrix again, and I realized there might be
something basic I forgot to mention because it's kind of insider trivia:
PostgreSQL changed to a shorter version number scheme starting with 10.
Before that, they were three components and a major release was a bump
in the middle one.

So the three latest major versions have been 12, 11, and 10, and the one
before that was 9.6, and before that was 9.5, and so on.

So, I am not certain, even looking at appveyor.yml, what version of
PostgreSQL it is using in the "PG: 9" case. 9.6, 9.5, 9.4, 9.3, 9.2,
9.1, 9.0 are all different major releases. If that's building against
9.0, that's farther back than I intend PL/Java 1.6 to support. (I'm
not absolutely decided what the cutoff version will be, but 9.3 to
9.5 would be in the ballpark.)

It would not be a bad thing to get the exact versions of PostgreSQL,
Java, etc., into these build logs for easy reference when something
goes wrong. The initial banner that PL/Java outputs when log level
is DEBUG1 would do the trick.

> issues. I'll try to debug it for another day or two before asking for help.

That doesn't sound like much fun. Feel free to say something about the
issues, or check in some code on a branch where I can try building it;
I could try to get some ideas in parallel with you.

This is the 'stretch' part of the project; I haven't built a Maven plugin
either, though I may have spent more time studying existing ones. An
earlier look gives me more of a chance to sound like I know what I'm
talking about when it's time to try to help. :)

Regards,
-Chap