Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Reminder - there are official-ish releases for Debian + TestContainer notes #496

Open
7 tasks
beargiles opened this issue Jul 12, 2024 · 0 comments
Open
7 tasks

Comments

@beargiles
Copy link

beargiles commented Jul 12, 2024

This question may have come up again recently...

There are official-ish releases for Debian (+ Ubuntu, etc.) The main Linux distros typically include one or two PostreSQL releases but the PostgreSQL organization itself provides current packages of all supported releases. Note: this is usually done in coordination with the major Linux distros, e.g., you'll have the same people managing both PostgreSQL and distro packages.

The organization also provides packages for many of the database server extensions, including PL/Java. You can pretty much toss all of the installation notes if you use these packages since they'll do a lot of things for you behind the scenes. (Foreshadowing an announcement on TestContainer support...)

The only limitation is that this repo only supports a single PL/Java release. That's rarely an issue but if you needed to change the release you can either do it after the official package has been installed, or download the package's source file, edit the package configuration file to use a different release, and then rebuild it.

Repo instructions: apt.postgresql.org
Package: postgresql-xx-pljava, eg., postgresql-16-pljava.

Usage

I'll have more to say after I publish my most recent work on github, but as a teaser....

I'm currently successfully using this repo in a Spring Boot project that uses a PostgreSQL TestContainer.
The preconditions are:

  • docker repository: postgres
  • docker image name: postgresql-16-pljava. (This is built on top of Debian)

TestContainer modification

You can either extend the-standard PostgreSQL TestContainer's runInitScriptIfRequired()
method to include these tasks or make the calls within the test classes. I prefer the former
since removes the burden from the developer.

  • server.execInContainer("apt-get update")
  • server.execInContainer("apt-get install -y default-jre-headless postgresql-16-java libsaxonhe-java")
  • server.execInContainer("/etc/init.d/postgresql restart")

A custom docker image does the same thing - and the database will be available for use much quicker -
but doing this programmatically gives you flexibility.

(I'm doing a bit of handwaving here, e.g., I've skipped the DEBIAN_FRONTEND envvar, etc.)

Once the server if back up we need to grab a connection and run the following SQL commands:

  • CREATE EXTENSION IF NOT EXISTS pljava;
  • GRANT USAGE ON LANGUAGE java TO PUBLIC;
  • SELECT sqlj.install_jar('('file:/usr/share/java/Saxon-HE.jar', 'saxonhe', true);
  • SELECT sqlj.set_classpath('public', 'saxonhe');

It should be enough to flush any connection pools - I don't think we need to bounce the server
a second time.

Integration tests

At this point we can freely use sqlj.* functions. We won't see any of the properties listed if we
run SHOW ALL but that isn't an issue.

This test is modeled on the canonical example.

@Test
public void checkPLJavaExample() throws SQLException, IOException, InterruptedException {

    try (Connection conn = postgres.getDataSource().getConnection();
         Statement stmt = conn.createStatement()) {

        // TODO: replace "1.6.7" with pjlava's extension version
        try (ResultSet rs = stmt.executeQuery(
                "SELECT sqlj.install_jar('file:/usr/share/postgresql/16/pljava/pljava-examples-1.6.7.jar'," +
                        " 'examples', true)")) {
            assertTrue(rs.next(), "failed to load 'pljava-examples' jar");
        }

        try (ResultSet rs = stmt.executeQuery("SELECT sqlj.set_classpath('javatest', 'examples')")) {
            assertTrue(rs.next(), "failed to set 'examples' classpath");
        }

        try (ResultSet rs = stmt.executeQuery("SELECT javatest.java_addone(3)")) {
            assertTrue(rs.next(), "failed to execute javatest.java_addone()");
            assertEquals(4, rs.getInt(1), "got wrong result");
        }
    }

Remaining work on SpringBoot + TestContainer + PL/Java

Something to be aware of - I'm currently seeing a number of error messages as the test
container shuts down. I think it's just a race condition between Spring Boot, TestContainers,
and the Hikari data source (connection pool) but I haven't confirmed that yet. I wanted to
mention it in case other people saw this behavior and were concerned.

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/

 :: Spring Boot ::                (v3.3.1)

2024-07-12 11:14:21.431 INFO  [c.c.e.c.PostgresqlSQLContainerWithPLJavaTest:50] - Starting PostgresqlSQLContainerWithPLJavaTest using Java 21.0.3 with PID 3831472 (started by bgiles in /home/bgiles/src/coyotesong/spring-boot-with-testcontainers-examples/spring-boot-3x-and-databases/postgresql-pljava)
"2024-07-12 11:14:21.434 INFO  [c.c.e.c.PostgresqlSQLContainerWithPLJavaTest:654] - No active profile set, falling back to 1 default profile: "default"
"2024-07-12 11:14:21.936 INFO  [c.c.e.c.PostgresqlSQLContainerWithPLJavaTest:56] - Started PostgresqlSQLContainerWithPLJavaTest in 0.773 seconds (process running for 33.238)
"OpenJDK 64-Bit Server VM warning: Sharing is only supported for boot loader classes because bootstrap classpath has been appended
WARNING: A Java agent has been loaded dynamically (/home/bgiles/.m2/repository/net/bytebuddy/byte-buddy-agent/1.14.17/byte-buddy-agent-1.14.17.jar)
WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
2024-07-12 11:14:23.933 WARN  [Container.postgres$16.3:112] - [3983] WARNING:  12 Jul 24 17:14:23 org.postgresql.pljava.example.LoggerTest SetOfRecordTest not ok
"2024-07-12 11:14:30.917 WARN  [Container.postgres$16.3:112] - [3983] WARNING:  [JEP 411] migration advisory: there will be a Java version (after Java 17) that will be unable to run PL/Java 1.6.7 with policy enforcement
"2024-07-12 11:14:30.917 WARN  [Container.postgres$16.3:112] - [3983] DETAIL:  This PL/Java version enforces security policy using important Java features that will be phased out in future Java versions. Those changes will come in releases after Java 17.
"2024-07-12 11:14:30.918 WARN  [Container.postgres$16.3:112] - [3983] HINT:  For migration planning, this version of PL/Java can still enforce policy in Java versions up to and including 22, and Java 17 and 21 are positioned as long-term support releases. For details on how PL/Java will adapt, please bookmark https://github.com/tada/pljava/wiki/JEP-411
"2024-07-12 11:14:30.979 WARN  [Container.postgres$16.3:112] - [4001] FATAL:  terminating connection due to unexpected postmaster exit
"2024-07-12 11:14:30.980 WARN  [Container.postgres$16.3:112] - [4008] FATAL:  terminating connection due to unexpected postmaster exit
"2024-07-12 11:14:30.980 WARN  [Container.postgres$16.3:112] - [4010] FATAL:  terminating connection due to unexpected postmaster exit
"2024-07-12 11:14:30.980 WARN  [Container.postgres$16.3:112] - [3981] FATAL:  terminating connection due to unexpected postmaster exit
"2024-07-12 11:14:30.980 WARN  [Container.postgres$16.3:112] - [3983] FATAL:  terminating connection due to unexpected postmaster exit
"2024-07-12 11:14:30.980 WARN  [Container.postgres$16.3:112] - [4009] FATAL:  terminating connection due to unexpected postmaster exit
"2024-07-12 11:14:31.057 INFO  [Container.postgres$16.3:115] - 
"2024-07-12 11:14:31.057 INFO  [Container.postgres$16.3:115] - 
"[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 42.42 s -- in com.coyotesong.examples.containers.PostgresqlSQLContainerWithPLJavaTest
[INFO] 
[INFO] Results:
[INFO] 
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
[INFO] 
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  45.531 s
[INFO] Finished at: 2024-07-12T11:14:31-06:00
[INFO] ------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant