Skip to content

pljava-examples-1.6.10.jar: ERROR: 42P01: relation "oldrows" does not exist #544

Open
Open
pljava-examples-1.6.10.jar: ERROR: 42P01: relation "oldrows" does not exist#544

Description

@df7cb
df7cb
opened on Jan 27, 2026

Hi,

I just tried to update the pljava Debian package to 1.6.10 to finally get PG 18 support (had missed that so far), but our regression tests fail now. They try to run the included pljava-examples-1.6.10.jar file:

+ psql -c 'ALTER USER myon SET pljava.vmoptions = '\''-Djava.security.manager=allow'\'''
ALTER ROLE
Time: 0.510 ms
+ psql -c 'ALTER USER myon SET check_function_bodies = off'
ALTER ROLE
Time: 0.530 ms
+ psql -c 'CREATE EXTENSION pljava'
WARNING:  01000: [JEP 411] migration advisory: Java version 24 and later cannot run PL/Java 1.6.10 with policy enforcement
DETAIL:  This PL/Java version enforces security policy using important Java features that upstream Java has disabled as of Java 24, as described in JEP 486. In Java 18 through 23, enforcement is still available, but requires "-Djava.security.manager=allow" in "pljava.vmoptions". 
HINT:  For migration planning, this version of PL/Java can still enforce policy in Java versions up to and including 23, and Java 17 and 21 are positioned as long-term support releases. Java 24 and later can be used, if wanted, WITH ABSOLUTELY NO EXPECTATIONS OF SECURITY POLICY ENFORCEMENT, by adding "-Djava.security.manager=disallow" in "pljava.vmoptions". This mode should be considered only if all Java code to be used is considered well vetted and trusted. For details on how PL/Java will adapt, please bookmark https://github.com/tada/pljava/wiki/JEP-411
LOCATION:  Backend_warnJEP411, Backend.c:2091
CREATE EXTENSION
Time: 466.292 ms
+ psql -c 'SELECT sqlj.install_jar('\''file:/home/myon/projects/postgresql/postgresql-pljava/postgresql-pljava/debian/postgresql-18-pljava/usr/share/postgresql/18/pljava/pljava-examples-1.6.10.jar'\'', '\''examples'\'', true)'
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest java.time.LocalDate passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest java.time.LocalTime passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest java.time.OffsetTime passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest java.time.LocalDateTime passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest java.time.OffsetDateTime passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest OffsetTime as stmt param passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:58 org.postgresql.pljava.example.LoggerTest SetOfRecordTest ok
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest variadic calls ok
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest composite return passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest set of composite return passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest ConditionalDDR looking good!
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.ComplexTuple javatest.complextuple from SQLInput
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.ComplexTuple javatest.complextuple to SQLOutput
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.ComplexTuple javatest.complextuple from SQLInput
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.SPIActions issue 228 tests ok
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest Holdability OK
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest timestamp roundtrip passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest timestamptz roundtrip passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.Point pg_catalog.point from SQLInput
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest issue192 test passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest boolean[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest "char"[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest bytea passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest int2[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest int4[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest int8[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest float4[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest float8[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest text[] passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.Point pg_catalog.point from SQLInput
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.annotation.PreJSR310 issue 199 test ok
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest aggregate examples ok
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest get resource passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
INFO:  00000: 27 Jan 26 19:31:59 org.postgresql.pljava.example.LoggerTest get ResourceBundle passes
LOCATION:  Java_org_postgresql_pljava_internal_Backend__1log, Backend.c:2195
ERROR:  42P01: relation "oldrows" does not exist
LINE 1: SELECT o.value, n.value FROM oldrows o FULL JOIN newrows n U...
                                     ^
QUERY:  SELECT o.value, n.value FROM oldrows o FULL JOIN newrows n USING (username)
CONTEXT:  SQL statement "UPDATE javatest.foobar_2 SET value = 43 WHERE value = 42"
LOCATION:  parserOpenTable, parse_relation.c:1466
Time: 871.324 ms

This was on the upcoming Ubuntu resolute 26.04, but the failure is also visible in this build log from Debian unstable: https://jengus.postgresql.org/job/postgresql-pljava-binaries/160/architecture=amd64,distribution=sid/console

All PG versions are affected.

Activity

  1. jcflack commented on Jan 29, 2026

    @jcflack
    jcflack
    on Jan 29, 2026
    Contributor

    That's perplexing. It suggests that the registration of the trigger tableOld and tableNew isn't happening

    pljava/pljava-examples/src/main/java/org/postgresql/pljava/example/annotation/Triggers.java

    Line 115 in d29f8f9

    tableOld = "oldrows", tableNew = "newrows" )

    but I'm surprised that can be something that affects all PG versions now, and didn't show up when I made the release.

    More to follow....

  2. df7cb commented on Jul 15, 2026

    @df7cb
    df7cb
    on Jul 15, 2026
    Author

    It seems the test was always broken but our testsuite never got to it, or something like that. Sorry that I only got to it now, too many moving parts here, new PG version, new Java version, new maven artifacts and generally new operating system updates. For the Debian package, I now removed the examineRows functions. I'm happy everything works now and pljava is back on track for PG 18. (Just in time for PG19beta2 😃!)

    AI helped me debug this. Sorry for dumping slop at you, but perhaps it helps:

     Different bug — and not your packaging. This is in the example itself: pljava-examples/src/main/java/org/
 postgresql/pljava/example/annotation/Triggers.java:118-134 (examineRows) tries to read the REFERENCING
 transition tables by name via raw SQL:
 Connection co = DriverManager.getConnection("jdbc:default:connection");
 ResultSet rs = st.executeQuery(
     "SELECT o.value, n.value" +
     " FROM oldrows o FULL JOIN newrows n USING (username)");
 Transition tables are not real relations, so PL/Java's SPI_execute/default-connection cannot see them —
 only plpgsql/SQL trigger functions get the parser magic that substitutes oldrows/newrows for the tuplestore.
  For a C trigger function (which PL/Java triggers are) the names resolve at parse time and fail with 42P01.
 PL/Java's accessor is TriggerData.getNew() / getOld(), which return ResultSets over the underlying
 tuplestores (pljava-api/.../TriggerData.java:43,56, implemented in pljava/.../internal/TriggerData.java:110-
 150). Example has been broken since it was added; the Debian test only just got past the SecurityManager/
 allow_unenforced wall far enough to hit it.

 Either fix the example to use the accessor (preferred — and a candidate for a Debian patch):
 public static void examineRows(TriggerData td) throws SQLException
 {
     int oval = 0, nval = 0;
     ResultSet oldRs = td.getOld();
     ResultSet newRs = td.getNew();
     // transition tables are keyed by username; pick the row that
     // actually changed from 42 -> 43
     while ( oldRs.next() ) { oval = oldRs.getInt("value"); break; }
     while ( newRs.next() ) { nval = newRs.getInt("value"); break; }
     if ( 42 == oval && 43 == nval )
         logMessage( "INFO", "trigger transition table test ok");
     else
         logMessage( "WARNING", String.format(
             "trigger transition table oval %d nval %d", oval, nval));
 }

  3. jcflack commented on Jul 16, 2026

    @jcflack
    jcflack
    on Jul 16, 2026
    Contributor

    It seems the test was always broken but our testsuite never got to it, or something like that. ... For the Debian package, I now removed the examineRows functions. I'm happy everything works now and pljava is back on track for PG 18. (Just in time for PG19beta2 😃!)

    AI helped me debug this. Sorry for dumping slop at you, but perhaps it helps:

     Different bug — and not your packaging. This is in the example itself: ...
 Transition tables are not real relations, so PL/Java's SPI_execute/default-connection cannot see them —

    Unfortunately, it really is AI slop. :)

    A less-sloppy AI would have seen that git blame finds that example added in 6c8ecba "add example for trigger transition tables", which directly follows adf15af "Register a trigger's transition tables":

    The magic that makes tableOld/tableNew appear to exist under
    the chosen names, for purposes of queries in SPI, happens when
    SPI_register_trigger_data is called, passing the TriggerData
    struct that was passed to the handler function, and that struct
    has the chosen names and the tuplestores. ...

    The AI could also have noticed those commits were first released in 1.5.1beta1, and the release notes for that release say:

    A trigger annotation can now specify tableOld="name1" or tableNew="name2", or both, and the PL/Java function servicing the trigger can do SPI JDBC queries and see the transition table(s) under the given name(s). The triggers example code has been extended with a demonstration.

    The example is there to be a regression test; I haven't made any releases where that example failed, and it succeeds for me here when I build against PG 18.0.

    It'll take me a little more time to check against current PG minors. I wonder if some breaking change was made in a later minor, and backported to earlier majors?

  4. df7cb commented on Jul 16, 2026

    @df7cb
    df7cb
    on Jul 16, 2026
    Author

    Well sorry about the slop, but the test was still failing here before I removed it 😬 ...

  5. jcflack commented on Jul 16, 2026

    @jcflack
    jcflack
    on Jul 16, 2026
    Contributor

    I have just built both 1.6.10 and the head of REL1_6_STABLE here locally, against the latest PG minors 18.4, 17.10, 16.14, 15.18, 14.23, 13.23 with that regression test included and succeeding in each case.

    Not that I'm denying it's failing for you. But whatever the reason is, it certainly isn't that the regression test is wrong.

  6. jcflack commented on Aug 11, 2026

    @jcflack
    jcflack
    on Aug 11, 2026
    Contributor

    This recent CI workflow run in PL/Java's GitHub repository includes successful builds for all of: ubuntu-latest, macos-15-intel, macos-26 (aarch64), and windows-latest (MSVC and MinGW).

    https://github.com/tada/pljava/actions/runs/31507768687

    The logs include the "trigger transition table test ok" message, confirming the regression test did run and did succeed. For example:

    https://github.com/tada/pljava/actions/runs/31507768687/job/93833622290#step:15:175

    The pull request that triggered the workflow only included updates to the CI scripts themselves (and an unrelated typo fix), and nothing to do with the trigger transition table support. The regression tests also all passed when the last release was made, but those workflow logs are no longer available to inspect.

    I have no insight at the moment into what is happening in your package, but whatever it is, it breaks a long-standing, documented, regression-tested PL/Java feature, and excluding the regression test doesn't seem like a satisfying fix. :)

  7. jcflack commented on Aug 11, 2026

    @jcflack
    jcflack
    on Aug 11, 2026
    Contributor

    Another attempt with AI produces another suggestion:

    https://share.google/aimode/iszV1RzfRNmy35sT6

    To fix this, the Debian packaging script simply needs to avoid setting check_function_bodies = off for the database user before running sqlj.install_jar, or explicitly reset it to on prior to installing the examples jar file.

    I am not fully satisfied with Gemini's explanation of why check_function_bodies = off produces this effect, and will be looking further into that, but I have indeed reproduced the failure in my environment by setting check_function_bodies = off.

    That setting in Debian's packaging script may have been added as a workaround at one point for allowing the examples jar to be loaded and deployed without having loaded the Saxon jar first (wherecheck_function_bodies = on would report unresolvable dependencies). However, since 594c5fb (appearing in PL/Java 1.6.9), that shouldn't be a reason to need such a workaround.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions