Tuesday, November 10, 2015

Does PostgreSQL Have an ORA-01795-like Limit?

The Oracle database requires that no more than 1000 entries be used in a SQL IN portion of a WHERE clause and will throw an ORA-01795 error if that number is exceeded. If a value needs to be compared to more than 1000 values, approaches other than use of IN must be applied. I wondered if this limitation applies to PostgreSQL and decided to write a simple application to find out.

For my simple test application, I wanted a very simple table to use with both an Oracle database and a PostgreSQL database.

Oracle: Creating Single Column Table And Inserting Single Row
CREATE TABLE numeral(numeral1 number);
INSERT INTO numeral (numeral1) VALUES (15);
PostgreSQL: Creating Single Column Table and Inserting Single Row
CREATE TABLE numeral(numeral1 numeric);
INSERT INTO numeral (numeral1) VALUES (15);

Building the SQL Query

Java 8 makes it to build up a query to test the condition of more than 1000 values in an IN clause. The next code snippet focuses on how this can be accomplished easily.

Java 8 Construction of SQL Query
final String queryPrefix = "SELECT numeral1 FROM numeral WHERE numeral1 IN ";
final String inClauseTarget =
   IntStream.range(1, numberOfInValues+1).boxed().map(String::valueOf).collect(Collectors.joining(",", "(", ")"));
final String select = queryPrefix + inClauseTarget;

The string constructed by the Java 8 code shown in the last code listing looks like this:

SELECT numeral1 FROM numeral WHERE numeral1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001)

Running the Query

When the above SQL query statement is executed against an Oracle database, the ORA-01795 error is manifest:

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

The PostgreSQL database does not have this same limitation as shown by its output below:


The full Java class I used to demonstrate the above findings in available at https://github.com/dustinmarx/databasedemos/blob/master/dustin/examples/inparameters/Main.java.


There are numerous ways to avoid the ORA-01795 error when using an Oracle database. However, I was curious if the same limitation existed for PostgreSQL and apparently it doesn't (I'm using PostgreSQL 9.4.4 in these examples). In fact, when I tried as many as one million IN values, PostgreSQL was still able to process the query, albeit noticeably slower than with a smaller number of IN values.

Tuesday, November 3, 2015

What Might a New @Deprecated Look Like?

JDK Enhancement Proposal (JEP) 277 ("Enhanced Deprecation") proposes to "revamp the deprecation annotation and provide tools to strengthen the tail end of the feature life cycle." Some of the limitations of the current @java.lang.Deprecated have bothered me for some time. I particularly would like to be able to provide text with @Deprecated rather than being forced to place explanatory text in a corresponding Javadoc @deprecated comment. In this post, I look at a custom annotation that gives a feel for the type of extra metadata JEP 277 proposes be included in a new and improved @Deprecated annotation.

The code listing that follows contains the definition of dustin.examples.annotations.Deprecated, an implementation which mostly mirrors what is described in the JEP 277 proposal.


package dustin.examples.annotations;

import java.lang.annotation.Documented;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import static java.lang.annotation.ElementType.*;
import static java.lang.annotation.ElementType.TYPE;

 * Conceptual improvement on standard @java.lang.Deprecated annotation
 * based on preliminary discussion related to JEP 277 and on
 * desire to include context details with deprecation annotation
 * rather than relying on presence of Javadoc's @deprecated.
 * Javadoc comments in this annotation definition are largely
 * based on descriptions in JEP 277 (http://openjdk.java.net/jeps/277).
public @interface Deprecated
    * JEP 277-defined reasons and associated explanations.
   public enum Reason
       * This API has been deprecated without any reason having been given.
       * This is the default value; everything that's deprecated today
       * implicitly has a deprecation reason of UNSPECIFIED.
       * This API is earmarked for removal in a future JDK release. Note,
       * the use of the word "condemned" here is used in the sense of a
       * structure that is intended to be torn down. The term is not mean
       * to imply any moral censure.
       * Use of this API can lead to data loss, deadlock, security
       * vulnerability, incorrect results, or loss of JVM integrity.
       * This API is no longer necessary, and usages should be removed.
       * No replacement API exists. Note that OBSOLETE APIs might or
       * might not be marked CONDEMNED.
       * This API has been replaced by a newer API, and usages should be
       * migrated away from this API to the newer API. Note that SUPERSEDED
       * APIs might or might not be marked CONDEMNED.
       * Calling this has no effect or will unconditionally throw an exception.
       * This API is not a stable part of the specification, and it may
       * change incompatibly or disappear at any time.

    * Provides any combination of one or more of the enum constants,
    * although not all combinations make sense. It is syntactically possible,
    * though perverse, to provide an empty array. Such a case should be
    * treated as if UNSPECIFIED were present.
    * @return One or more Reasons for deprecation; default value is the enum
    *    constant UNSPECIFIED and absence of values should be treated as such.
   Reason[] value();

    * Provides Strings representing any APIs that replace this API.
    * This should not specify any replacements if reason is OBSOLETE.
    * @return Strings returned by this method should be links to replacement
    *    APIs for the API being deprecated. Each string should be in the same
    *    format as the @link Javadoc tag.
   String[] replacement();

    * Provides the release in which the API was deprecated.
    * @return Release number at which this API became deprecated
    *    in a free-form syntax String with the release numbering
    *    following the same scheme as the @since Javadoc tag.
   String since();

    * Provides the anticipated complete removal of this deprecated API
    * if any known date or version is anticipated for the API's removal.
    * This value is most likely to be set for reasons of CONDEMNED,
    * OBSOLETE, and SUPERSEDED. This value is NOT described in JEP 277.
    * @return Date or version in which it is anticipated that this
    *    API will be removed altogether.
   String anticipatedRemoval() default "Not Planned";

The next code listing provides examples of the above annotation being applied to a deprecated class.

DeprecatedClass.java: Examples of Using Improved @Deprecated

package dustin.examples.annotations.demo;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

import static dustin.examples.annotations.Deprecated.Reason.*;

 * Demonstrates how new and improved @Deprecated might be used.
   since="1.5", anticipatedRemoval="1.9",
public class DeprecatedClass
   final DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ");

      value={DANGEROUS}, since="1.0",
   public DateFormat getDateFormatter()
      return dateFormat;

This last example demonstrates use of the improved @Deprecated annotation. The "anticipatedRemoval" element of the @Deprecated annotation is not mentioned in JEP 277 and I gave it a default value for situations in which a deprecated construct may have no anticipated removal date but rather is deprecated to warn off new uses of it only.

The code listings above demonstrate defining a new and improved @Deprecated annotation such as spelled out in JEP 277. However, JEP 277 proposes much more than the existence of an improved annotation. The proposal also discusses "runtime changes" to provide "warnings about usage of deprecated APIs on an opt-in basis," "dependency-tool enhancements" to analyze annotation dependencies in fashion similar to and possibly even based on the jdeps tool, and "Javadoc enhancements."

Although Java's custom annotations support made it easy to implement a version of @Deprecated that reflects many of the ideas in JEP 277, the new and improved @java.lang.Deprecated would enjoy many benefits that a custom Java annotation does not enjoy such as built-in support in the JDK and use by JDK classes. The JDK-provided @Deprecated would also continue to enjoy IDE and tooling benefits such as striked-out names of deprecated code constructs.

Monday, November 2, 2015

Applying IDE Inspections to Custom Java Annotations

The introduction of annotations with J2SE 5 has changed how we write and process Java. Besides Java SE's predefined annotations, frameworks, IDEs, and toolkits have introduced their own custom annotations. The Checker Framework has provided examples of how custom annotations can be used to add greater type safety in Java. In this post, I look at the writing of a simple custom annotation and using it in NetBeans (8.0.2) and IntelliJ IDEA (14.0.3) to help developers identify issues in their code that need further attention.

In the article Making the Most of Java's Metadata, Part 2: Custom Annotations, Jason Hunter demonstrates an @Unfinished annotation as an example of writing a custom Java annotation. I will demonstrate a different implementation of an @Unfinished annotation in this post. That's the beauty of custom annotations: one can write an annotation to best fit one's needs. The code for my @Unfinished annotation is shown in the next code listing.

Unfinished.java: Defining Custom @Unfinished Annotation

package dustin.examples.annotations;

import static java.lang.annotation.ElementType.*;

import java.lang.annotation.Documented;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

 * Example of a custom annotation that marks Java code constructs
 * that are not yet completed.
 * Notes about custom annotations specific to this example:
 *   - @Documented indicates available for public documentation
 *   - CLASS retention policy means that compiler places annotation
 *     information in compiled .class files, but JVM is NOT aware
 *     of the annotation at runtime.
 *   - @Target parameters (statically imported) indicate that this
 *     annotation can be applied to constructors, fields,
 *     local variables, methods, packages, parameters, and
 *     classes/interfaces.
 *   - Methods defined for this @interface without 'default' are
 *     required settings for application of this annotation. In
 *     this case, the "finishBy" element is NOT required (but
 *     recommended!) but the "value" element is required.
 *   - "value" element has special significance in custom Java
 *     annotations: it is the assumed annotation element if
 *     a String is provided to the annotation without explicit
 *     element name called out.
public @interface Unfinished
   /** Description of the unfinished construct. */
   String value();

    * Date, build, or event by which the annotated construct
    * is anticipated to be finished.
   String finishBy() default "Unknown";

The next code listing shows application of @Unfinished in a simple class that has a lot of work still to be completed on it.

WorkInProgress.java: Applying @Unfinished Custom Annotation

package dustin.examples.annotations.demo;

import dustin.examples.annotations.Unfinished;

 * Demonstrates custom Java annotation @Unfinished.
public class WorkInProgress
   @Unfinished("This will do something good by Revision 2.")
   public void doSomethingGood()

   @Unfinished(value = "Do something good here.", finishBy = "Revision 2")
   public void doSomethingElseGood()

The incomplete class uses the annotation's "value" element by default for one method and then adds use of the "finishedBy" element for the second method. There are a few observations that can be made from the last two code listings definition or and use of @Unfinished:

  1. Inclusion of "default" for an annotation element means that those who use the annotation are not required to provide a value for that element.
  2. The "value" element is assumed if only one value is provided to the annotation and is provided without an explicit element name.
  3. The name "value" does not need to be provided if only a single annotation element is specified, but must be provided if more than one annotation element is specified.
  4. The retention level of CLASS was used here because I felt like tools working with the compiled version of Java classes would be able to use this information and I don't anticipate runtime use of the information.
  5. Careful selection of which annotation elements should have "default" values is desirable because not having a "default" requires an element to be specified, which can be the desired behavior in some cases.

Use of custom annotations can provide a standardized mechanism for building "executable" and more controlled descriptions for other developers and for tools. This approach is often advantageous when compared to leaving messages with comments as comments tend to be less standardized and subject to typos and differences in case sensitivity, spelling, and other discrepancies. Annotations enforce conventions better and allow for tooling to make more efficient use of what they communicate than parsing arbitrary text. Perhaps the most obvious way to gain some of this benefit of custom annotations over arbitrary comments is with an annotation processor. Several IDEs and frameworks such as the Checker Framework process annotations. There are also numerous online references regarding writing of custom annotations processors that can be used with the Jav compiler to provide warnings. In the remainder of this post, I focus on how one can apply two of the more popular Java IDEs (NetBeans and IntelliJ IDEA) to report these annotations as hints/inspections. I am not looking in this post at integrating an annotation processor into the IDEs' compilation processes or at integrating a custom processor with the command-line Java compiler.

Inspecting the @Unfinished Annotation in NetBeans

I have blogged before on Creating a NetBeans 7.1 Custom Hint and the process is pretty much the same still with NetBeans 8. The first step is to use the Refactor -> Inspect and Transform ... option as shown in the next screen snapshot.

When Refactor -> Inspect and Transform... is selected, a popup like that shown next is seen.

I'm going to apply this new inspection to all my open projects as shown in the last screen snapshot's "Inspect" field. Clicking on the "Browse" button leads to the "Manage Inspections" window coming up as shown in the next screen snapshot.

Clicking on the "New..." button allows the developer to create a custom inspection under Custom->Inspection.

You can click the "Edit Script" button to create the custom inspection, which includes the ability to rename the inspection. I have renamed the Inspection "Unfinished Code." The next screen snapshot shows the code I added to the "Unfinished Code" inspection.

In the script code for this "Unfinished Code" inspection (which is also shown below), the description is specified as "Unfinished Code". The source pattern is specified as @dustin.examples.annotations.Unfinished($parameters$) (the entire package name of the @interface defining the custom annotation with $parameters$ indicating one or more parameters). The => symbols point to the target pattern. In this case, the target pattern is empty, indicating that the proposed transformation is to remove the @Unfinished annotation. For more details on the syntax of the NetBeans inspections editor, see Geertjan Wielenga's post Custom Declarative Hints in NetBeans IDE 7.1.

<!description="Unfinished Code">

With the NetBeans inspection created, it is time try it out. The next two screen snapshots demonstrate selecting that Inspection to run and the results of running it.

The output from running the inspection is an example of how we can use NetBeans in conjunction with a custom annotation to quickly identify annotated code and treat it appropriately.

Inspecting the @Unfinished Annotation in IntelliJ IDEA

One way to start creating a custom annotation in IntelliJ IDEA is to open Analyze -> Inspect Code... and click on the "..." button in the "Specify Inspection Scope" pop-up as shown in the next two screen snapshots.

The next screen snapshot shows the "Inspections" dialog box.

The screen snapshot just shown indicates that the "Structural Search Inspection" is NOT checked. Checking it (checkbox is to the right of the name "Structural Search Inspection") leads to the "Severity" level being selectable and allows a particular inspection to be added (plus sign turns from gray to green).

Clicking on the green plus sign (+), leads to two choices: "Add Search template..." or "Add Replace template...". The differentiation here is similar to NetBeans's differentiation between Source -> Inspect and Refactor -> Inspect and Transform... and I'll focus on the "Replace template" here.

When "Add Replace template..." is selected, the "Structural Replace" dialog is displayed.

The easiest way to create a custom inspection here it to adapt an existing template. This is done by clicking on the "Copy existing template..." button. For the two inspections I create for this blog post, I copied the "annotated class" and "annotated methods" existing templates respectively to create my own custom templates "Unfinished Class" and "Unfinished Method."

The screen snapshots above show the "existing templates" I copied and the screen snapshots below showed the custom templates I created from them for "Unfinished Class" and "Unfinished Method."

For each of the custom templates ("Unfinished Class" and "Unfinished Method"), I need to click on the "Edit variables..." button and specify the regular expressions for each variable (identifiers marked with $ on front and back) to be searched. For most variables (such as class name, method name, and so forth), I use an "all characters" regular representation (.*), but for the $Annotation$ in each of these templates, I use dustin.examples.annotations.Unfinished. The next screen snapshot is a representative sample of this that shows the Annotation variable setting for the "Undefined Method" template.

I can use Analyze -> Run Inspection by Name... to run either of my new inspections. The next three screen snapshots demonstrate running the new "Unfinished Method" inspection.

The output from running the inspection is an example of how we can use IntelliJ IDEA in conjunction with a custom annotation to quickly identify annotated code and treat it appropriately.


This post has demonstrated using NetBeans's and IntelliJ IDEA's abilities to create custom inspections to create inspections that can alert developers to the presence of custom annotations in their code. The post demonstrated a simple @Unfinished annotation and how to apply custom inspections in NetBeans and IntelliJ IDEA to help identify code that uses those annotations.

Thursday, October 15, 2015

Better Performing Non-Logging Logger Calls in Log4j2

Using logging guards is a common practice when using Log4j 1.x and wanting to avoid the additional performance impact that can occur on some occasions even if the message is not actually logged. One of the most attractive features that Simple Logging Facade for Java (SLF4J) brought to Java logging was the ability to reduce the number of circumstances in which these log level checks are necessary. In this post, I look at how one can use Log4j 2.x's logging API changes to achieve a similar benefit.

The next code listing demonstrates logging long-running operations. The first example implicitly calls to toString() method on an instance whose name is "slow". The second logging example invokes a method that is long-running.

Traditional, Unguarded Logging

// Will implicitly invoke slow's toString() method
logger.debug("NORMAL: " + slow);
// Will explicitly invoke the long-running method expensiveOperation()

In the previous example, both logging operations will take a long time even when no logging is actually performed. The logging statements in the previous code listing will only actually log when the logging level is DEBUG or a less specific log level such as TRACE, but their expensive operations will be run even when nothing is logged.

There are two ways to deal with this in Log4j 1.x. One approach, and often the best approach, is to try to re-write the log statement so that no long-running operations are involved. When that is impractical (such as when one needs the context associated with the long-running operation for the log message to be useful), logging guards can be used. This approach that works in Log4j 1.x is demonstrated next.

Traditional, Guarded Logging

if (logger.isDebugEnabled())
   logger.debug("GUARDED: " + slow);

Logging guards, as shown in the previous code listing, are effective in preventing long-running operations from being called even when no message would have been logged anyway. However, using logging guards does introduce some disadvantages. Perhaps the most significant disadvantage is the extra (some would say bloated) code that is introduced. Another potential disadvantage is much less common, but much more serious: with the extra scope introduced by the conditional and associated block, it's more susceptible to errant code being introduced in the conditional that could potentially even introduce side effects in a logging-level-dependent block of code.

One of the most common situations in which log calls that do not actually log anything but significantly impact performance is when an object's toString() method is explicitly or implicitly called when an object is passed to the logger call or concatenated with a string passed to the logger call. This situation was demonstrated in the two above code listings with the string passed to the logger call by concatenating the string literal "GUARDED: " to the implicitly invoked toString() method of the variable named "slow".

SLF4J has popularized the concept of parameterized logging calls and Log4j 2 provides similar support in its logging API. The code that follows demonstrates how this is used.

Parameterized Logging

logger.debug("PARAMETERIZED: {}", slow);
logger.debug("{}", expensiveOperation());

When the parameterized logging example above is executed with a log level more specific than DEBUG, the implicit toString() on the "slow" variable will not be attempted thanks to the parameterized logging. However, parameterized logging cannot help the other logging situation as the method expensiveOperation() will be invoked despite the parameterized logging. Note also that while parameterized logging helps in the case of implicit toString() calls, it does not help with explicit toString() calls. Even if the logging level is more specific than DEBUG, a call to slow.toString() in a logger statement would still incur a performance hit.

Log4j 2.4 introduces a Lambda-based mechanism that can be used to defer invocation of methods passed to a logger call such that they don't need to be executed at all if the statement is being logged at a level less specific than the current log level. This is demonstrated in the next code listing where the toString() method is explicitly called on the "slow" variable's object via lambda expression and the expensiveOperation method is invoked via method reference.

Lambda Expression Logging

logger.debug("LAMBDA: ", () -> slow.toString());
logger.debug("{}", this::expensiveOperation);

When the above code is executed with a log level set to a level more specific than DEBUG, neither the "slow" object's toString() method nor the expensiveOperation method will be invoked thanks to the lambda expression based lazy loading. In other words, similar to how the example worked with guards, the use of lambda expressions prevents unnecessary execution of potentially long-running methods unless their results are really going to be logged. This lambda expression support was added to Log4j with version 2.4 and, of course, requires Java 8.


Log4j 2 (2.4) provides multiple approaches for avoiding the performance impact of log statements when the messages aren't actually logged.

  1. Log statements can be re-written such that expensive methods (including expensive toString() calls) are not logged at all.
  2. Logging guards can be used to ensure that a log statement's long-running methods invocations are only executed if the message is to actually be logged.
  3. Log4j 2's parameterized (formatted) logger API can be used to eliminate the invocation of implicit toString() methods unless the message is really being logged.
  4. Log4j 2.4's lambda expression logger API can be used to to eliminate the invocation of any operations (implicit or explicit) needed for the logged message unless the message is really being logged.

Thursday, October 8, 2015

Easy and Consistent Log4j2 Logger Naming

In the post Portable Logger Names with Java 7 Method Handles, I wrote about using Java 7's method handles to name classes' loggers. I stated in that post that advantages of that approach included consistency in logger naming and avoiding accidental copying and pasting of code that might lead to a different class's name being used for the logger name. In this post, I look at how Log4j 2 provides an approach for achieving these same benefits.

Log4j 2 recognizes the prevalent approach to naming loggers based off of classes' names. The "Logger Names" section of the "Log4j 2 API" page in the Log4j 2 Manual states, "In most cases, applications name their loggers by passing the current class's name to LogManager.getLogger. Because this usage is so common, Log4j 2 provides that as the default when the logger name parameter is either omitted or is null."

The following very simple Calculator class demonstrates this, creating a Logger with a parameter-less LogManager.getLogger() call. Because no parameter is passed to the getLogger() method, the logger will be named after the class in which the Logger is created. The Javadoc comment for method LogManager.getLogger() confirms this behavior: "Returns a Logger with the name of the calling class."


package dustin.examples.log4j;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.util.Arrays;

 * Simple calculator class that is intended more for demonstration
 * of Log4j2 than for calculating anything.
public class Calculator
   private static final Logger logger = LogManager.getLogger();

   public String getLoggerName()
      return logger.getName();

    * Add the provided operands together and provide their sum.
    * @param operands Operands to be added together.
    * @return Sum of the provided operands.
   public long add(final long ... operands)
      long sum = 0;
      for (final long operand : operands)
         sum += operand;
      logger.debug("The sum of " + Arrays.toString(operands) + " is " + sum);
      return sum;

With the Calculator class implemented as shown above, the class's logger's name, available by call to Logger.getName() as demonstrated in the Calculator method getLoggerName(), is "dustin.examples.log4j.Calculator". Although not shown here, a Logger retrieved with parameter-less LogManager.getFormatterLogger() will also use "the fully qualified name of the calling Class as the Logger name."


The Log4j 2 approach discussed and demonstrated in this post for naming loggers is easy to use and makes it easy to have consistent and correct logger names, assuming that the chosen logger naming scheme is to use the fully qualified package and class name. This approach is briefer and arguably even more readable than the method handles approach to naming loggers, but this approach is specific to Log4j 2 while the method handles approach can be used with multiple logging frameworks and libraries.

Tuesday, October 6, 2015

Single Quotes in Oracle Database Index Column Specification

In my previous post, I mentioned that a downside of using double quotes to explicitly specify case in Oracle identifiers is the potential for being confused with the use of single quotes for string literals. Although I don't personally think this is sufficient reason to avoid use of double quotes for identifiers in Oracle, it is worth being aware of this potential confusion. When to use single quotes versus when to use double quotes has been a source of confusion for users new to databases that distinguish between the two for some time. In this post, I look at an example of how accidental misuse of single quote where no quote is more appropriate can lead to the creation of an unnecessary index.

The SQL in the simple script createPersonTable.sql generates a table called PEOPLE and an index will be implicitly created for this table's primary key ID column. However, the script also contains an explicit index creation statement that, at first sight, might appear to also create an index on this primary key column.

   id number PRIMARY KEY,
   last_name varchar2(100),
   first_name varchar2(100)

CREATE INDEX people_pk_index ON people('id');

We might expect the statement that appears to explicitly create the primary key column index to fail because that column is already indexed. As the output below shows, it does not fail.

When a query is run against the indexes, it becomes apparent why the explicit index creation did not fail. It did not fail because it was not creating another index on the same column. The single quotes around what appears to be the "id" column name actually make that 'id' a string literal rather than a column name and the index that is created is a function-based index rather than a column index. This is shown in the query contained in the next screen snapshot.

The index with name PEOPLE_PK_INDEX was the one explicitly created in the script and is a function-based index. The implicitly created primary key column index has a system-generated name. In this example, the function-based index is a useless index that provides no value.

It's interesting to see what happens when I attempt to explicitly create the index on the column by using double quotes with "id" and "ID". The first, "id", fails ("invalid identifier") because Oracle case folds the name 'id' in the table creation to uppercase 'ID' implicitly. The second, "ID", fails ("such column list already indexed") because, in this attempt, I finally am trying to create an index on the same column for which an index was already implicitly created.

In my original example, the passing of a literal string as the "column" to the index creation statement resulted in it being created as a useless function-based index. It could have been worse if my intended primary key column index hadn't already been implicitly created because then I might not have the index I thought I had. This, of course, could happen when creating an index for a column or list of columns that won't have indexes created for them implicitly. There is no error message to warn us that the single-quoted string is being treated as a string literal rather than as a column name.


The general rule of thumb to remember when working with quotation marks in Oracle database is that double quotes are for identifiers (such as column names and table names) and single quotes are for string literals. As this post has demonstrated, there are times when one may be misused in place of the other and lead to unexpected results without necessarily displaying an error message.

Monday, October 5, 2015

Downsides of Mixed Identifiers When Porting Between Oracle and PostgreSQL Databases

Both the Oracle database and the PostgreSQL database use the presence or absence of double quotes to indicate case sensitive or case insensitive identifiers. Each of these databases allows identifiers to be named without quotes (generally case insensitive) or with double quotes (case sensitive). This blog post discusses some of the potential negative consequences of mixing quoted (or delimited) identifiers and case-insenstive identifiers in an Oracle or PostgreSQL database and then trying to port SQL to the other database.

Advantages of Case-Sensitive Quoted/Delimiter Identifiers

There are multiple advantages of case sensitive identifiers. Some of the advertised (real and perceived) benefits of case sensitive database identifiers include:

  • Ability to use reserved words, key words, and special symbols not available to identifiers without quotes.
    • PostgreSQL's keywords:
      • reserved ("only real key words" that "are never allowed as identifiers")
      • unreserved ("special meaning in particular contexts," but "can be used as identifiers in other contexts").
      • "Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands."
    • Oracle reserved words and keywords:
      • Oracle SQL Reserved Words that can only be used as "quoted identifiers, although this is not recommended."
      • Oracle SQL Keywords "are not reserved," but using these keywords as names can lead to "SQL statements [that] may be more difficult to read and may lead to unpredictable results."
      • "Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character."
      • "Quoted identifiers can contain any characters and punctuations marks as well as spaces."
  • Ability to use the same characters for two different identifiers with case being the differentiation feature.
  • Avoid dependency on a database's implementation's case assumptions and provide "one universal version."
  • Explicit case specification avoids issues with case assumptions that might be changeable in some databases such as SQL Server.
  • Consistency with most programming languages and operating systems' file systems.
  • Specified in SQL specification and explicitly spells out case of identifiers rather than relying on specific implementation details (case folding) of particular database.
  • Additional protection in cases where external users are allowed to specify SQL that is to be interpreted as identifiers.

Advantages of Case-Insensitive Identifiers

There are also advantages associated with use of case-insensitive identifiers. It can be argued that case-insensitive identifiers are the "default" in Oracle database and PostgreSQL database because one must use quotes to specify when this default case-insensitivity is not the case.

  • Case-insensitivity is the "default" in Oracle and PostgreSQL databases.
  • The best case for readability can be used in any particular context. For example, allows DML and DDL statements to be written to a particular coding convention and then be automatically mapped to the appropriate case folding for various databases.
  • Avoids errors introduced by developers who are unaware of or unwilling to follow case conventions.
  • Double quotes (" ") are very different from single quotes (' ') in at least some contexts in both the Oracle and PostgreSQL databases and not using case-sensitive identifier double quotes eliminates need to remember the difference or worry about the next developer not remembering the difference.
  • Many of the above listed "advantages" may not really be good practices:
    • Using reserved words and keywords as identifiers is probably not good for readability anyway.
    • Using symbols allowed in quoted identifiers that are not allowed in unquoted identifiers may not be necessary or even desirable.
    • Having two different variables of the same name with just different characters cases is probably not a good idea.

Default Case-Insensitive or Quoted Case-Sensitive Identifiers?

In Don’t use double quotes in PostgreSQL, Reuven Lerner makes a case for using PostgreSQL's "default" (no double quotes) case-insensitive identifiers. Lerner also points out that pgAdmin implicitly creates double-quoted case-sensitive identifiers. From an Oracle DBA perspective, @MBigglesworth79 calls quoted identifiers in Oracle an Oracle Gotcha and concludes, "My personal recommendation would be against the use of quoted identifiers as they appear to cause more problems and confusion than they are worth."

A key trade-off to be considered when debating quoted case-sensitive identifiers versus default case-insensitive identifiers is one of being able to (but also required to) explicitly specify identifiers' case versus not being able to (but not having to) specify case of characters used in the identifiers.

Choose One or the Other: Don't Mix Them!

It has been my experience that the worst choice one can make when designing database constructs is to mix case-sensitive and case-insensitive identifiers. Mixing of these make it difficult for developers to know when case matters and when it doesn't, but developers must be aware of the differences in order to use them appropriately. Mixing identifiers with implicit case and explicit case definitely violates the Principle of Least Surprise and will almost certainly result in a frustrating runtime bug.

Another factor to consider in this discussion is case folding choices implemented in Oracle database and PostgreSQL database. This case folding can cause unintentional consequences, especially when porting between two databases with different case folding assumptions. The PostgreSQL database folds to lowercase characters (non-standard) while the Oracle database folds to uppercase characters. This significance of this difference is exemplified in one of the first PostgreSQL Wiki "Oracle Compatibility Tasks": "Quoted identifiers, upper vs. lower case folding." Indeed, while I have found PostgreSQL to be heavily focused on being standards-compliant, this case folding behavior is one place that is very non-standard and cannot be easily changed.

About the only "safe" strategy to mix case-sensitive and case-insensitive identifiers in the same database is to know that particular database's default case folding strategy and to name even explicitly named (double quoted) identifiers with exactly the same case as the database will case fold non-quoted identifiers. For example, in PostgreSQL, one could name all identifiers in quotes with completely lowercase characters because PostgreSQL will default unquoted identifiers to all lowercase characters. However, when using Oracle, the opposite approach would be needed: all quoted identifiers should be all uppercase to allow case-sensitive and case-insensitive identifiers to be intermixed. Problems will arise, of course, when one attempts to port from one of these databases to the other because the assumption of lowercase or uppercase changes. The better approach, then, for database portability between Oracle and PostgreSQL databases is to commit either to using quoted case-sensitive identifiers everywhere (they are then explicitly named the same for both databases) or to use default case-insensitive identifiers everywhere (and each database will appropriately case fold appropriately in its own approach).


There are advantages to both identifiers with implicit case (case insensitive) and to identifiers with explicit (quoted and case sensitive) case in both Oracle database and PostgreSQL database with room for personal preferences and tastes to influence any decision on which approach to use. Although I prefer (at least at the time of this writing) to use the implicit (default) case-insensitive approach, I would rather use the explicitly spelled-out (with double quotes) identifier cases in all cases than mix the approach and use explicit case specification for identifiers in some cases and implicit specification of case of identifiers in other cases. Mixing the approaches makes it difficult to know which is being used in each table and column in the database and makes it more difficult to port the SQL code between databases such as PostgreSQL and Oracle that make different assumptions regarding case folding.

Additional Reading