Tidy benchmarks (chiefly for Java, and generally for composites)

[jcflack]

This PR fixes a few nits noticed in the Java benchmark tests (old versions in the Java test-suite POM, a /* Failing Test Case */ comment in TestNumeric because the parameters and returns had been declared double instead, and a similar issue in TestArray).

This PR also touches the other PLs and the Makefiles in order to factor out the CREATE TYPE Person that was duplicated in every PL's testcomposites.sql. Now it is created once in the Makefile itself. The simplification benefits the Java test, as now the intended type Person can simply be used in the Java source annotations to generate those functions with the correct signatures, eliminating the need for duplicate CREATE FUNCTION commands in java/testcomposites.sql. For that to work, of course, the type has to exist at the time of the install_jar call.

[tlewis-ba]

This help is much appreciated; given that pl/dotnet is our main focus, these tests didn't necessarily receive the love they deserve.

We are working on rebuilding these tests from scratch in the hopes of sharing them with the broader PL community as a shared resource. So (a) thanks for the changes, but (b) they might be discarded later. @jcflack, I wouldn't want you putting a ton of work into this unaware of that.

That said, we will test these and then hopefully merge them. We don't yet have ci/cd set up, but if these pull requests keep coming in, such that we have to do manual testing, then I predict that we will have it set up soon. 😆

[jcflack]

Thanks for looking!

As you rebuild them to share with the PL community, I might suggest something like the 'transpose' of the current organization. Right now there are, for example, a testarrays script that runs those tests for all PLs, and so on for the other tests.

As any maintainer of a given PL might want an easy way to run all the tests for that PL without installing all of the others, an organization with per-PL test scripts that run all the tests would be convenient. It might naturally produce a results file that would look like the transpose of the current one, and that could easily be transposed back in the scripts that generate the result graphics.

[jcflack]

Another unasked-for future test-reorganizing idea: :)

Instead of shell scripts piping lots of SQL snippets through sudo and psql, it might be convenient to write perl scripts that make use of the PostgreSQL::Test::Cluster module used in PostgreSQL's own tests (which is available when $(pg_config --pkglibdir)/pgxs/src/test/perl is added to perl's library path). It once had a different name, but the current name PostgreSQL::Test::Cluster has been backpatched as far back as PostgreSQL 10(.22).

(When installing from a package repository, it may take a little sleuthing to find which package delivers that module. The rpm packaging includes it in postgresql-devel; the deb packaging puts it, a little less intuitively, in postgresql-client.)

Using that module, a few lines of perl can spin up a private PostgreSQL instance or three without interfering with anything else, and run SQL queries and get their results with no need to fill the script with assumptions about $DBUSER or sudo. That can make the script less noisy to read, and also make it easier for someone else to run: it'll just work, without the person having to first set up an environment that permits sudo to the same $DBUSER, or patch the script to change those assumptions. Essentially, a lot of boilerplate and inessential assumptions can come out, leaving just the clear code for the tests that you want.

Documentation can be found in the README and in the perldoc of the module itself. (The README mentions TAP tests, but nothing forces using TAP; the module allows spinning up a PostgreSQL instance and sending it any SQL for any purpose.)

PL/Java builds, and uses for its own CI tests, a very similar module documented here. It's designed to be a near-workalike (though much less comprehensive), right down to the spelling of method names, for test scripts written in jshell rather than perl. So its documentation can shed light on PostgreSQL::Test::Cluster and vice versa.

In an environment where both the PostgreSQL perl module and the PL/Java java one are available, either one would be a possible choice for the test scripts, coming down to preference for writing in perl or jshell, or for getting query results back as psql text output or via typed JDBC methods. (The one that comes with PostgreSQL is probably familiar to more people, and would be more convenient for those who might only want to run benchmarks for one PL or a few, and not have to have PL/Java around.)

[tlewis-ba]

@jcflack, I promise that we haven't forgotten you; just not able to test your code at the moment. We will get to it.