Release notes, releases prior to PL/Java 1.6

Improvements to the java.sql.SQLXML type

Additions to the Adjusting.XML API support limiting resource usage in XML processing, controlling resolution of external documents and resources, validation against a schema, and integration of an XML catalog to locally satisfy requests for external documents.

Corrections and new documentation of whitespace handling in XML values of CONTENT form, and implementation limitations.

Improvements to the Saxon-based ISO SQL/XML example functions

Updated the dependency for these optional examples to Saxon 10. Probably the most significant of the Saxon 10 changes, for PostgreSQL's purposes, will be that the XQuery higher-order function feature is now included in the freely-licensed Saxon-HE, so that it is now possible without cost to integrate a modern XQuery 3.1 implementation that is lacking only the schema-aware feature and the typed data feature (for those, the paid Saxon-EE product is needed), and the static typing feature (which is not in any Saxon edition).

To compensate for delivering the higher-order function support in -HE, Saxonica moved certain optimizations to -EE. This seems a justifiable trade, as it is better for development purposes to have the more complete implementation of the language, leaving better optimization to be bought if and when needed.

Thanks to a tip from Saxon's developer, the returning of results to SQL is now done in a way that may incur less copying in some cases.

Internals

• Many sources of warnings reported by the Java VM's -Xcheck:jni option have been tracked down, making it practical to use -Xcheck:jni in testing.
• Reduced pressure on the garbage collector in management of references to PostgreSQL native state.

Threading/synchronization, finalizers, and new configuration variable

Java is multithreaded while PostgreSQL is not, requiring ways to prevent Java threads from entering PostgreSQL at the wrong times, while cleaning up native resources in PostgreSQL when PL/Java references are released, and vice versa.

PL/Java has historically used an assortment of approaches including Java object finalizers, which have long been deprecated informally, and formally since Java 9. Finalizers enter PostgreSQL from a thread of their own, and the synchronization approach used in PL/Java 1.5.2 and earlier has been associated with occasional hangs at backend exit when using an OpenJ9 JVM at runtime.

A redesigned approach using a new DualState class was introduced in 1.5.1, at first only used in implementing the java.sql.SQLXML type, a newly-added feature. In 1.5.3, other approaches used in the rest of PL/Java's code base are migrated to use DualState also, and all uses of the deprecated Java object finalizers have been retired. With the new techniques, the former occasional OpenJ9 hangs have not been observed.

This represents the most invasive change to PL/Java's thread synchronization in many years, so it may be worthwhile to reserve extra time for testing applications.

A new configuration variable, pljava.java_thread_pg_entry, allows adjusting the thread policy. The default setting, allow, preserves PL/Java's former behavior, allowing Java threads entry into PostgreSQL one at a time, only when any thread already in PG code has entered or returned to Java.

With object finalizers no longer used, PL/Java itself does not need the allow mode, but there may be application code that does. Application code can be tested by setting the error mode, which will raise an error for any attempted entry to PG from a thread other than the original thread that launched PL/Java. If an application runs in error mode with no errors, it can also be run in block mode, which may be more efficient, as it eliminates many locking operations that happen in allow or error mode. However, if block mode is used with an application that has not been fully tested in error mode first, and the application does attempt to enter PostgreSQL from a Java thread other than the initial one, the result can be blocked threads or a deadlocked backend that has to be killed.

A JMX management client like JConsole or jvisualvm can identify threads that are blocked, if needed. The new DualState class also offers some statistics that can be viewed in JConsole, or jvisualvm with the VisualVM-MBeans plugin.

Improvements to the java.sql.SQLXML type

Support for this JDBC 4.0 type was added in PL/Java 1.5.1. Release 1.5.3 includes the following improvements:

• A new “Adjusting” API exposes configuration settings for Java XML parsers that may be created internally during operations on SQLXML instances. That allows the default settings to restrict certain XML parser features as advocated by the Open Web Application Security Project when XML content may be coming from untrusted sources, with a simple API for relaxing those restrictions when appropriate for XML content from a known source.
• It is now possible for a PL/Java function to return, pass into a PreparedStatement, etc., an SQLXML instance that PL/Java did not create. For example, a PL/Java function could use another database's JDBC driver to obtain a SQLXML value from that database, and use that as its own return value. Transparently, the content is copied to a PL/Java SQLXML instance. The copy can also be done explicitly, allowing the “Adjusting” API to be used if the default XML parser restrictions should be relaxed.
• Behavior when the server encoding is not UTF-8, or when it is not an IANA-registered encoding (even if Java has a codec for it), has been improved.

Improvements to the Saxon-based ISO SQL/XML example functions

Since PL/Java 1.5.1, the supplied examples have included a not-built-by-default example supplying ISO SQL/XML features missing from core PostgreSQL. It is not built by default because it raises the minimum Java version to 8, and brings in the Saxon-HE XML-processing library.

In 1.5.3, the example now provides versions of the ISO SQL XMLEXISTS, XMLQUERY, XMLTABLE, and XMLCAST functions based on the W3C XQuery language as ISO SQL specifies (while PostgreSQL has an “XMLTABLE” function since release 10 and “XMLEXISTS” since 9.1, they have numerous limitations inherited from a library that does not support XQuery, and additional peculiarities prior to PostgreSQL 12), and the ISO SQL LIKE_REGEX, OCCURRENCES_REGEX, POSITION_REGEX, SUBSTRING_REGEX, and TRANSLATE_REGEX functions that apply XQuery regular expressions. It also includes the XMLTEXT function, which is rather trivial, but also missing from core PostgreSQL, and supplied here for completeness.

As plain user-defined functions without special treatment in PostgreSQL's SQL parser, these functions cannot be used with the exact syntax specified in ISO SQL, but require simple rewriting into equivalent forms that are valid ordinary PostgreSQL function calls. The rewritten forms are intended to be easy to read and correspond closely to the ISO syntax.

While still presented as examples and not a full implementation, these functions are now intended to be substantially usable (subject to minor documented limits), and testing and reports of shortcomings are welcome.

ResultSet holdability again

A ResultSet obtained from a query done in PL/Java would return the value CLOSE_CURSORS_AT_COMMIT to a caller of its getHoldability method, but in reality would become unusable as soon as the PL/Java function creating it returned to PostgreSQL. That was fixed in PL/Java 1.5.1 for a ResultSet obtained from a Statement, but not for one obtained from a PreparedStatement. It now correctly remains usable to the end of the transaction in either case.

Savepoint behavior at rollback

Per JDBC, a Savepoint still exists after being used in a rollback, and can be used again; the rollback only invalidates any Savepoint that had been created after the one being rolled back. That should be familiar behavior, as it is the same as PostgreSQL's own SQL SAVEPOINT behavior. It is also correct in pgJDBC, which has test coverage to confirm it. PL/Java has been doing it wrong.

In 1.5.3 it now has the JDBC-specified behavior. For compatibility with existing application code, the meaning of the pljava.release_lingering_savepoints configuration variable has been adjusted. The setting tells PL/Java what to do if a Savepoint still exists, neither released nor rolled back, at the time a function exits. If on, the savepoint is released (committed); if off, the savepoint is rolled back. A warning is issued in either case.

In an existing function that used savepoints and assumed that a rolled-back savepoint would be no longer live, it will now be normal for such a savepoint to reach the function exit still alive. To recognize this case, PL/Java tracks whether any savepoint has been rolled back at least once. At function exit, any savepoint that has been neither released nor ever rolled back is disposed of according to the release_lingering_savepoints setting and with a warning, as before, but any savepoint that has already been rolled back at least once is simply released, regardless of the variable setting, and without producing a warning.

Control of function parameter names in generated SQL

When generating the CREATE FUNCTION command in a deployment descriptor according to an annotated Java function, PL/Java ordinarily gives the function parameters names that match their Java names, unquoted. Because PostgreSQL allows named notation when calling a function, the parameter names in its declaration become part of its signature that cannot later be changed without dropping and re-creating the function.

In some cases, explicit control of the SQL parameter names may be wanted, independently of the Java names: to align with an external standard, perhaps, or when either the SQL or the Java name would collide with a reserved word. For that purpose, the (already slightly overloaded) @SQLType annotation now has a name attribute that can specify the SQL name of the annotated parameter.

Documentation

The user guide and guide for packagers contained incorrect instructions for using Maven to build a single subproject of PL/Java (such as pljava-api or pljava-examples) instead of the full project. Those have been corrected.

Schema-qualification

PL/Java now more consistently schema-qualifies objects in queries and DDL it generates internally, as a measure of defense-in-depth in case the database it is installed in has not been protected from CVE-2018-1058.

No schema-qualification work has been done on the example code. If the examples jar will be installed, it should be in a database that the recommended steps have been taken to secure.

Some large-object code removed

1.5.1 removes the code at issue in CVE-2016-0768, which pertained to PostgreSQL large objects, but had never been documented or exposed as API.

This is not expected to break any existing code at all, based on further review showing the code in question had also been simply broken, since 2006, with no reported issues in that time. That discovery would support an argument for downgrading the severity of the reported vulnerability, but with no need to keep that code around, it is more satisfying to remove it entirely.

Developers wishing to manipulate large objects in PL/Java are able to do so using the SPI JDBC interface and the large-object SQL functions already available in every PostgreSQL version PL/Java currently supports.

Typing of parameters in prepared statements

PL/Java currently does not determine the necessary types of PreparedStatement parameters from the results of PostgreSQL's own type analysis of the query (as a network client would, when using PostgreSQL's “extended query” protocol). PostgreSQL added the means to do so in SPI only in PostgreSQL 9.0, and a future PL/Java major release should use it. However, this release does make two small changes to the current behavior.

Without the query analysis results from PostgreSQL, PL/Java tries to type the prepared-statement parameters based on the types of values supplied by the application Java code. It now has two additional ways to do so:

• If Java code supplies a Java user-defined type (UDT)—that is, an object implementing the SQLData interface—PL/Java will now call the SQLData method getSQLTypeName on that object and use the result to pin down the PostgreSQL type of the parameter. Existing code should already provide this method, but could, in the past, have returned a bogus result without detection, as PL/Java did not use it.

• Java code can use the three-argument form of setNull to specify the exact PostgreSQL type for a parameter, and then another method can be used to supply a non-null value for it. If the following non-null value has a default mapping to a different PostgreSQL type, in most cases it will overwrite the type supplied with setNull and re-plan the query. That was PL/Java's existing behavior, and was not changed for this minor release. However, the new types introduced in this release—the java.time types and SQLXML—behave in the way that should become universal in a future major release: the already-supplied PostgreSQL type will be respected, and PL/Java will try to find a usable coercion to it.

Inaccuracies converting TIMESTAMP and TIMESTAMPTZ

When converting between PostgreSQL values of timestamp or timestamptz type and the pre-Java 8 original JDBC type java.sql.Timestamp, there were cases where values earlier than 1 January 2000 would produce exceptions rather than converting successfully. Those have been fixed.

Also, converting in the other direction, from java.sql.Timestamp to a PostgreSQL timestamp, an error of up to 1.998 seconds (averaging 0.999) could be introduced.

That error has been corrected. If an application has stored Java Timestamps and corresponding SQL timestamps generated in the past and requires them to match, it could be affected by this change.

New date/time/timestamp API in Java 8 java.time package

The old, and still default, mappings in JDBC from the SQL date, time, and timestamp types to java.sql.Date, java.sql.Time, and java.sql.Timestamp, were never well suited to represent the PostgreSQL data types. The Time and Timestamp classes were used to map both the with-timezone and without-timezone variants of the corresponding SQL types and, clearly, could not represent both equally well. These Java classes all contain timezone dependencies, requiring the conversion to involve timezone calculations even when converting non-zoned SQL types, and making the conversion results for non-zoned types implicitly depend on the current PostgreSQL session timezone setting.

Applications are strongly encouraged to adopt Java 8 as a minimum language version and use the new-in-Java-8 types in the java.time package, which eliminate those problems and map the SQL types much more faithfully. For PL/Java function parameters and returns, the class in the method declaration can simply be changed. For retrieving date/time/timestamp values from a ResultSet or SQLInput object, use the variants of getObject / readObject that take a Class<?> parameter. The class to use is:

Details on these mappings are added to the documentation.

Newly supported java.sql.SQLXML type

PL/Java has not, until now, supported the JDBC 4.0 SQLXML type. PL/Java functions have been able to work with PostgreSQL XML values by mapping them as Java String, but that conversion could introduce character encoding issues outside the control of the XML APIs, and also has memory implications if an application stores, or generates in queries, large XML values. Even if the processing to be done in the application could be structured to run in constant bounded memory while streaming through the XML, a conversion to String requires the whole, uncompressed, character-serialized value to be brought into the Java heap at once, and any heap-size tuning has to account for that worst-case size. The java.sql.SQLXML API solves those problems by allowing XML manipulation with any of several Java XML APIs with the data remaining in PostgreSQL native memory, never brought fully into the Java heap unless that is what the application does. Heap sizing can be based on the just the application's processing needs.

The SQLXML type can take the place of String in PL/Java function parameters and returns simply by changing their declarations from String to SQLXML. When retrieving XML values from ResultSet or SQLInput objects, the legacy getObject / readObject methods will continue to return String for existing application compatibility, so the specific getSQLXML / readSQLXML methods, or the forms of getObject / readObject with a Class<?> parameter and passing SQLXML.class, must be used. A documentation page has been added, and the PassXML example illustrates use of the API.

A not-built-by-default new example (because it depends on Java 8 and the Saxon-HE XML-processing library) provides a partial implementation of true XMLQUERY and XMLTABLE functions for PostgreSQL, using the standard-specified XML Query language rather than the XPath 1.0 of the native PostgreSQL functions.

New Java property exposes the PostgreSQL server character-set encoding

A Java system property, org.postgresql.server.encoding, is set to the canonical name of a supported Java Charset that corresponds to PostgreSQL's server_encoding setting, if one can be found. If the server encoding's name is not recognized as any known Java Charset, this property will be unset, and some functionality, such as the SQLXML API, may be limited. If a Java Charset does exist (or is made available through a CharsetProvider) that does match the PostgreSQL server encoding, but is not automatically selected because of a naming mismatch, the org.postgresql.server.encoding property can be set (with a -D in pljava.vmoptions) to select it by name.

ResultSet holdability

A ResultSet obtained from a query done in PL/Java would return the value CLOSE_CURSORS_AT_COMMIT to a caller of its getHoldability method, but in reality would become unusable as soon as the PL/Java function creating it returned to PostgreSQL. It now remains usable to the end of the transaction, as claimed.

PostgreSQL 9.6 and parallel query

A function in PL/Java can now be annotated parallel={UNSAFE | RESTRICTED | SAFE}, with UNSAFE the default. A new user guide section explains the possibilities and tradeoffs. (Good cases for marking a PL/Java function SAFE may be rare, as pushing such a function into multiple background processes will require them all to start JVMs. But if a practical application arises, PostgreSQL's parallel_setup_cost can be tuned to help the planner make good plans.)

Although RESTRICTED and SAFE Java functions work in simple tests, there has been no exhaustive audit of the code to ensure that PL/Java's internal workings never violate the behavior constraints on such functions. The support should be considered experimental, and could be a fruitful area for beta testing.

Tuple counts widened to 64 bits with PostgreSQL 9.6

To accommodate the possibility of more than two billion tuples in a single operation, the SPI implementation of the JDBC Statement interface now provides the JDK 8-specified executeLargeBatch and getLargeUpdateCount methods defined to return long counts. The original executeBatch and getUpdateCount methods remain but, obviously, cannot return counts that exceed INT_MAX. In case the count is too large, getUpdateCount will throw an ArithmeticException; executeBatch will store SUCCESS_NO_INFO for any statement in the batch that affected too many tuples to report.

For now, a ResultSetProvider cannot be used to return more than INT_MAX tuples, but will check that condition and throw an error to ensure predictable behavior.

pg_upgrade

PL/Java should be upgraded to 1.5.1 in a database cluster, before that cluster is binary-upgraded to a newer PostgreSQL version using pg_upgrade. A new Upgrading installation-guide section centralizes information on both upgrading PL/Java in a database, and upgrading a database with PL/Java in it.

Suppressing row operations from triggers

In PostgreSQL, a BEFORE ROW trigger is able to allow the proposed row operation, allow it with modified values, or silently suppress the operation for that row. Way back in PL/Java 1.1.0, the way to produce the ‘suppress’ outcome was for the trigger method to throw an exception. Since PL/Java 1.2.0, however, an exception thrown in a trigger method is used to signal an error to PostgreSQL, and there has not been a way to suppress the row operation.

The TriggerData interface now has a suppress method that the trigger can invoke to suppress the operation for the row.

Constraint triggers

New attributes in the @Trigger annotation allow the SQL generator to create constraint triggers (a type of trigger that can be created with SQL since PostgreSQL 9.1). Such triggers will be delivered by the PL/Java runtime (to indicate that a constraint would be violated, a constraint trigger method should throw an informative exception). However, the trigger method will have access, through the TriggerData interface, only to the properties common to ordinary triggers; methods on that interface to retrieve properties specific to constraint triggers have not been added for this release.

PostgreSQL 10 and trigger transition tables

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.

Logging from Java

The way the Java logging system has historically been plumbed to PostgreSQL's, as described in issue 125, can be perplexing both because it is unaffected by later changes to the PostgreSQL settings after PL/Java is loaded in the session, and because it has honored only log_min_messages and ignored client_min_messages. The second part is easy to fix, so in 1.5.1 the threshold where Java discards messages on the fast path is determined by the finer of log_min_messages and client_min_messages.

Conveniences for downstream package maintainers

The mvn command to build PL/Java will now accept an option to provide a useful default for pljava.libjvm_location, when building a package for a particular software environment where the likely path to Java is known.

The mvn command will also accept an option to specify, by the path to the pg_config executable, the PostgreSQL version to build against, in case multiple versions exist on the build host. This was already possible by manipulating PATH ahead of running mvn, but the option makes it more explicit.

A new packaging section in the build guide documents those and a number of considerations for making a PL/Java package.

In 1.5.1-BETA3

• Add a ddr.reproducible option to SQL generator

In 1.5.1-BETA2

• java 8 date/time api
• Annotations don't support CREATE CONSTRAINT TRIGGER
• Let annotations give defaults to row-type parameters
• Improve DDR generator on the dont-repeat-yourself dimension for UDT type mapping
• Support the JDBC 4.0 SQLXML type

In 1.5.1-BETA3

• self-install jar ClassCastException (…ConsString to String), some java 6/7 runtimes
• i386 libjvm_location gets mangled as …/jre/lib/1/server/libjvm.so
• java.lang.ClassNotFoundException installing examples jar
• Preprocessor errors building on Windows with MSVC
• Saxon example does not build since Saxon 9.9 released
• Segfault in VarlenaWrapper.Input on 32-bit
• Windows: self-install jar silently fails to replace existing files
• ERROR: java.sql.SQLException: some Java class name
• SetOfRecordTest with timestamp column influenced by environment

In 1.5.1-BETA2

• PostgreSQL 10: SPI_modifytuple failed with SPI_ERROR_UNCONNECTED
• SPIConnection prepareStatement doesn't recognize all parameters
• Ordinary (non-constraint) trigger has no way to suppress operation
• ResultSetHandle and column definition lists
• PreparedStatement doesn't get parameter types from PostgreSQL (partial improvements)
• internal JDBC: inaccuracies converting TIMESTAMP and TIMESTAMPTZ
• Missing type mapping for Java return byte[]
• The REMOVE section of DDR is in wrong order for conditionals
• Loading PL/Java reinitializes timeouts in PostgreSQL >= 9.3
• JDBC ResultSet.CLOSE_CURSORS_AT_COMMIT reported, but usable life shorter

In 1.5.1-BETA1

• Add support for PostgreSQL 9.6
• Clarify documentation of ResultSetProvider
• pg_upgrade (upgrade failure from 9.5 to 9.6)
• Java logging should honor client_min_messages too

Schema migration

The tables used internally by PL/Java have changed. If PL/Java 1.5.0 is loaded in a database with an existing sqlj schema populated by an earlier PL/Java version (1.3.0 or later), the structure will be updated without data loss (enhancement request 12). Remember that PL/Java runs independently in each database session where it is in use. Older PL/Java versions active in other sessions can be disrupted by the schema change.

A trial installation of PL/Java 1.5.0 can be done in a transaction, and rolled back if desired, leaving the schema as it was. Any concurrent sessions with active older PL/Java versions will not be disrupted by the altered schema as long as the transaction remains open, but they may block for the duration, so such a test transaction should be kept short.

Behavior of readSQL and writeSQL for base and mirror user-defined types

In the course of fixing issue #98, the actual behavior of readSQL and writeSQL with base or mirror types, which had not previously been documented, now is, along with other details of PL/Java's type coercion rules found only in the code. Because machine byte order was responsible for issue #98, it now (a) is selectable, and (b) has different, appropriate, defaults for mirror UDTs (which need to match PostgreSQL's order) and for base UDTs (which must stay big-endian because of how binary COPY is specified). A new documentation section explains in detail.

USAGE to PUBLIC no longer default for java language

Of the two languages installed by PL/Java, functions that declare LANGUAGE javau can be created only by superusers, while those that declare LANGUAGE java can be created by any user or role granted the USAGE privilege on the language.

In the past, the language java has been created with PostgreSQL's default permission granting USAGE to PUBLIC, but PL/Java 1.5.0 leaves the permission to be explicitly granted to those users or roles expected to create Java functions, in keeping with least-privilege principles. See large objects, access control under known issues for background.

SQL generated by Java annotations

Java code developed for use by PL/Java can carry in-code annotations, used by the Java compiler to generate the SQL commands to declare the new functions, types, triggers, etc. in PostgreSQL (enhancement request 1011112, though different in implementation). This eliminates the need to have Java code and the corresponding SQL commands developed in parallel, and the class of errors possible when both are not updated together. It also allows compile-time checks that the Java methods or classes being annotated are suitable (correct access modifiers, signatures, etc.) for their declared SQL purposes, rather than discovering such issues only upon loading the code into PostgreSQL and trying to use it.

The Java compiler writes the generated SQL into a “deployment descriptor” file (pljava.ddr by default), as specified by the SQL/JRT standard. The file can be included in a jar archive with the compiled code, and the commands will be executed by PL/Java when the install_jar function is used to load the jar.

SQL generation is covered in the updated user documentation, and illustrated in the Hello, World example and several other supplied examples. Reference information is in the API documentation. It is currently usable to declare functions, triggers, and user-defined types, both base and composite.

The history of this feature in PL/Java is long, with the first related commits appearing in 2005, six years in advance of an enhancement request for it. It became generally usable in 2013 when building with Java SE 6 or later, using the annotation processing framework Java introduced in that release. 1.5.0 is the first PL/Java numbered release to feature it.

A jar may have more than one deployment descriptor

PL/Java formerly allowed only one entry in a jar to be a deployment descriptor (that is, a file of SQL commands to be executed upon loading or unloading the jar). The SQL/JRT standard allows multiple entries to be deployment descriptors, executed in the order they are mentioned in the jar manifest, or the reverse of that order when the jar is being unloaded. PL/Java now conforms to the standard.

The behavior is useful during transition to annotation-driven deployment descriptor generation for a project that already has a manually-maintained deployment descriptor. PL/Java's own pljava-examples project is an illustration, in the midst of such a transition itself.

Note the significance placed by SQL/JRT on the order of entries in a jar manifest, whose order is normally not significant according to the Jar File Specification. Care can be needed when manipulating manifests with automated tools that may not preserve order.

Conditional execution within deployment descriptors

Deployment descriptors have a primitive conditional-execution provision defined in the SQL/JRT standard: commands wrapped in a
BEGIN IMPLEMENTOR identifier construct will only be executed if the identifier is recognized by the SQL/JRT implementation in use. The design makes possible jars that can be installed on different database systems that provide SQL/JRT features, with database-specific commands wrapped in BEGIN IMPLEMENTOR blocks with an identifier specific to the system. By default, PL/Java recognizes the identifier postgresql (matched without regard to case).

PL/Java extends the standard by allowing the PostgreSQL configuration variable pljava.implementors to contain a list of identifiers that will be recognized. SQL code in a deployment descriptor can conditionally add or remove identifiers in this list to influence which subsequent implementor blocks will be executed, giving a still-primitive but more general control structure.

In sufficiently recent PostgreSQL versions, the same effect could be achieved using DO statements and PL/pgSQL control structures, but this facility in PL/Java does not require either to be available.

Interaction with SET ROLE corrected

PL/Java formerly was aware of the user ID associated with the running session, but not any role ID that user may have acquired with SET ROLE. The result would commonly be failed permission checks made by PL/Java when the session user did not have the needed permission, but had SET ROLE to a role that did. Likewise, within install_jar, PL/Java would execute deployment descriptor commands as the original session user rather than as the user's current role, with permission failures a likely result.

Correcting this issue has changed the PL/Java API, but without a bump of major version because the prior API, while deprecated, is still available.

• getOuterUserName and executeAsOuterUser are new, and correctly refer to the session user or current role, when active.
• getSessionUserName and executeAsSessionUser are still present but deprecated, and their semantics are changed. They are now deprecated aliases for the corresponding new methods, which honor the set role. Use cases that genuinely need to refer only to the session user and ignore the role should be rare, and should be discussed on the mailing list or opened as issues.

Unicode transparency

Since the resolution of bug 21, PL/Java contains a regression test to ensure that character strings passed and returned between PostgreSQL and Java will round-trip without alteration for the full range of Unicode characters, when the database encoding is set to UTF8.

More considerations apply when the database encoding is anything other than UTF8, and especially when it is SQL_ASCII. Please see character encoding support for more.

Since 1.5.0-BETA3

• Build process: accept variation in PostgreSQL version string
• Build process: accommodate PostgreSQL built with private libraries
• Clarified message when CREATE EXTENSION fails because new session needed
• Reduced stack usage in SQL generator (small-memory build no longer needs -Xss)

In 1.5.0-BETA3

• Bogus mirror-UDT values on little-endian hardware
• Base UDT not registered if first access isn't in/out/send/recv
• TupleDesc leak warnings with composite UDTs
• also added regression test from 1010962 report

In 1.5.0-BETA2

• Generate SQL for trigger function with no parameters
• openssl/ssl.h needed on osx el-capitan (latest 10.11.3)/postgres 9.5 (documented)
• Source location missing for some annotation errors
• OS X El Capitan “Java 6” dialog when loading … Java 8
• pljava-api jar missing from installation jar

In 1.5.0-BETA1

• SPIPreparedStatement.setObject() fails with Types.BIT
• SSLSocketFactory throws IOException on Linux
• PL/Java fails to compile with -Werror=format-security
• PL/Java does not build on POWER 7
• The built in functions do not use the correct error codes
• TupleDesc reference leak
• String conversion to enum fails
• segfault if SETOF RECORD-returning function used without AS at callsite
• pl/java PG9.3 Issue
• No-arg functions unusable: “To many parameters - expected 0”
• Exceptions in static initializers are masked
• UDT in varlena form breaks if length > 32767
• PL/Java kills unicode?
• Type.c expects pre-8.3 find_coercion_pathway behavior
• Support PostgreSQL 9.5
• pl/java getting a build on MacOSX - PostgreSQL 9.3.2
• build pljava on windows for PostgreSQL 9.2
• Error while installing PL/Java with Postgresql 9.3.4 64 bit on Windows 7 64 bit System
• pljava does not compile on mac osx ver 10.11.1 and postgres 9.4
• pljava does not compile on centos 6.5 and postgres 9.4
• Error installing pljava with Windows 7 64 Bit and Postgres 9.4

Developments in PostgreSQL not yet covered

Large objects, access control

PL/Java does not yet expose PostgreSQL large objects with a documented, stable API, and the support it does contain was developed against pre-9.0 PostgreSQL versions, where no access control applied to large objects and any object could be accessed by any database user. PL/Java's behavior is proper for PostgreSQL before 9.0, but improper on 9.0+ where it would be expected to honor access controls on large objects (CVE-2016-0768). This will be corrected in a future release. For this and earlier releases, the recommendation is to selectively grant USAGE on the java language to specific users or roles responsible for creating Java functions; see “default USAGE permssion” under Changes.

INSTEAD OF triggers, triggers on TRUNCATE

These are supported by annotations and the SQL generator, and the runtime will deliver them to the specified method, but the TriggerData interface has no new methods to recognize these cases (that is, no added methods analogous to isFiredAfter, isFiredByDelete). For a method expressly coded to be a TRUNCATE trigger or an INSTEAD OF trigger, that is not a problem, but care should be taken when coding a trigger method to handle more than one type of trigger, or creating triggers of these new types that call a method developed pre-PL/Java-1.5.0. Such a method could be called with a TriggerData argument whose existing isFired... methods all return false, likely to put the method on an unexpected code path.

A later PL/Java version should introduce trigger interfaces that better support such evolution of PostgreSQL in a type-safe way.

Constraint triggers

Constraint trigger syntax is not supported by annotations and the SQL generator. If declared (using hand-written SQL), they will be delivered by the runtime, but without any constraint-trigger-specific information available to the called method.

Event triggers

Event triggers are not yet supported by annotations or the SQL generator, and will not be delivered by the PL/Java runtime.

Range types

No predefined mappings for range types are provided.

PRE_PREPARE, PRE_COMMIT, PARALLEL_ABORT, PARALLEL_PRE_COMMIT, and PARALLEL_COMMIT transaction callbacks, PRE_COMMIT subtransaction callbacks

Listeners for these events cannot be registered and the events will not be delivered.

Imperfect integration with PostgreSQL dependency tracking

In a dump/restore, manual intervention can be needed if the users/roles recorded as owners of jars are missing or have been renamed. A current thread on pgsql-hackers should yield a better solution for a future release.

Quirk if deployment descriptor loads classes from same jar

The install_jar function installs a jar, optionally reading deployment descriptors from the jar and executing the install actions they contain. It is possible for those actions to load classes from the jar just installed. (This would be unlikely if the install actions are limited to typical setup, function/operator/datatype creation, but likely, if the install actions also include basic function tests, or if the setup requirements are more interesting.)

If, for any class in the jar, the first attempt to load that class is made while resolving a function declared STABLE or IMMUTABLE, a ClassNotFoundException results. The cause is PostgreSQL's normal treatment of a STABLE or IMMUTABLE function, which relies on a snapshot from the start of the install_jar query, when the jar was not yet installed. A workaround is to ensure that the install actions cause each needed class to be loaded, such as by calling a VOLATILE function it supplies, before calling one that is STABLE or IMMUTABLE. (One could even write install actions to declare a needed function VOLATILE before the first call and then redeclare it.)

This issue should be resolved as part of a broader rework of class loading in a future PL/Java release.

Partial implementation of JDBC 4 and later

The changes to make PL/Java build under Java SE 6 and later, with version 4.0 and later of JDBC, involved providing the specified methods so compilation would succeed, with real implementations for some, but for others only stub versions that throw SQLFeatureNotSupportedException if used. Regrettably, there is nothing in the documentation indicating which methods have real implementations and which do not; to create such a list would require an audit of that code. If a method throws the exception when you call it, it's one of the unimplemented ones.

Individual methods may be fleshed out with implementations as use cases arise that demand them, but for a long-term roadmap, it seems more promising to reduce the overhead of maintaining another JDBC implementation by sharing code with pgjdbc, as has been discussed on pljava-dev.

Exception handling and logging

PL/Java does interconvert between PostgreSQL and Java exceptions, but with some loss of fidelity in the two directions. PL/Java code has some access to most fields of a PostgreSQL error data structure, but only through internal PL/Java API that is not expected to remain usable, and code written for PL/Java has never quite had first-class standing in its ability to generate exceptions as information-rich as those from PostgreSQL itself.

PL/Java in some cases generates the categorized SQLExceptions introduced with JDBC 4.0, and in other cases does not.

This area may see considerable change in a future release. Thoughts on logging is a preview of some of the considerations.

Types with type modifiers and COPY

Although it is possible to create a PL/Java user-defined type that accepts a type modifier (see the example), such a type will not yet be handled by SQL COPY or any other operation that requires the input or receive function to handle the modifier. This is left for a future release.