SQL generator unexpected case-sensitive matching of implementor tags

[jcflack]

PL/Java's SQL generator creates deployment SQL code based on Java source-code annotations. The generated "deployment descriptor" file can be included in a jar and automatically run by sqlj.install_jar and sqlj.remove_jar. The syntax is fixed by the ISO SQL/JRT standard and has a rudimentary conditional-inclusion feature based on implementor tags. SQL statements wrapped in BEGIN/END with an implementor tag are executed only if that tag is recognized by the DBMS when installing or removing the jar.

PL/Java exposes that deployment-descriptor feature to allow more general conditional execution where one SQL statement in a deployment descriptor may test some arbitrary condition and change PostgreSQL's setting of pljava.implementors accordingly, to control which implementor tags are recognized and which SQL statements executed later in the deployment descriptor.

Naturally, this scheme requires the SQL generator to emit the statement that tests the condition earlier in the deployment descriptor than the statements relying on that implementor tag.

The SQL generator has a generalized facility for controlling the order of statements in the deployment descriptor, based on provides and requires elements, taking arbitrary strings, in the Java source code annotations responsible for the generated SQL statements. A statement that requires a particular tag won't be emitted before a statement that provides it.

To cut down verbosity, an implementor tag specified in the annotation for a statement is treated also as an implicit requires for that tag, so the programmer only needs to place an explicit provides element on whatever arbitrary SQL statement tests the condition that will determine if the tag is recognized.

The provides/requires relationship so created differs in three ways from other provides/requires relationships:

• It does not reverse when sqlj.remove_jar is executed. Normal dependencies must be reversed for that case, so dependent objects are removed before those they depend on. By contrast, a condition determining the setting of an implementor tag must be evaluated before the tag is needed, whether the jar is being installed or removed.
• If it does not have a distinct remove action (the usual case), its install action (the condition test and setting of the tag) is used both when installing and removing.
• It is weak. The SQL generator does not flag an error if the implicit requires for an implementor tag is not satisfied by any annotation in the Java source. It is possible for the tag to be set some other way in the DBMS environment where the jar is to be deployed. Faced with statements that require such 'unprovided' implementor tags, the SQL generator just falls back to emitting them as late in the deployment descriptor as possible, after all other statements that do not depend on them.

This scheme, while an easy way to shoehorn conditional execution into the SQL generator's preexisting provides/requires handling, suffers from one impedance mismatch: the values of the existing explicit provides/requires annotation elements have been treated as arbitrary strings, compared for exact matching. An implementor tag, per the ISO SQL/JRT standard, is an SQL identifier, matching case-insensitively unless it is quoted.

PL/Java has been comparing the exact string value of a provides element to the lowercase-folded value of an implementor tag. If the programmer used mixed case, even spelling both values exactly the same, the result is a mismatch, a surprising behavior. Because of the special weak treatment of the implementor/provides relationship, the mismatch is not reported as an error, and the generated deployment descriptor often works thanks to the fallback ordering rule. In less-straightforward cases, such as when the statement testing a condition itself depends on some other statement, problems in the generated ordering can result. For remove actions, the failure to match the provides tag with an implementor tag means failure to assign the special implementor/provides behavior, so the condition test may not be correctly ordered among other remove actions, and may not appear at all. Resulting deployment/undeployment failures can be perplexing.

In the absence of any more complete solution, the exact comparison of provides strings to implementor tags at least should use the programmer-supplied spelling of the latter, rather than the lowercase-folded form. That will be a less-surprising rule, and the usual case where the programmer has spelled the tag exactly the same in both places will not lead to silent failure.

[jcflack]

Believed resolved in 1.6.9.