Re: jdbc excpetions in pg

Kris Jurka wrote:

>> If so, what is the suggested method for classifying the errors to more
>> detail than SQLException? I was hoping to make my code more robust by
>> dealing with the errors more appropriately.
>
> You should check the SQLState of the SQLException to determine the type
> of problem encountered.

For many errors the PostgreSQL driver throws an exception that's wrapped
into the SQLException. This org.postgresql.util.PSQLException contains
additional details like the server error message.

I put together the attached class as a helper/utility to provide a
convenient way to get the simplest error message to summarize a given
error. It was written before it became possible to select your own
SQLState when raising exceptions in PL/PgSQL, and I need to adapt it to
make better use of that ability, but it's still handy, as it lets me
write things like:


             JOptionPane.showMessageDialog(
                 parentFrame,
                 "Your changes have not been saved because an error" +
                 " occurred while trying to save them.\n" +
                 "You may be able to correct the problem described " +
                 "below and press 'save' again.\n\n" +
                 "Problem details: " + DbExceptionUtil.getDbErrorMsg(e),
                 "Unable to save changes to customer",
                 JOptionPane.ERROR_MESSAGE);

without worrying about whether the issue was a Hibernate validation
exception, an error thrown from PL/PgSQL, a constraint check, etc. With
the use of human-readable constraint names, I find this works rather well.

Of course, you should __NEVER__ test the error message text or make
decisions based on the error message text in your code. Rely solely on
exception type and SQLState for that, since they're unaffected by
translations, small changes in wording across versions, etc.

The attached class expects to find Apache ExceptionUtil on the
classpath, as well as Hibernate Validation. It's easy to chop out the
hibernate validator bits if you don't use it.

--
Craig Ringer
package au.com.postnewspapers.bs_jclient.dbutil;

import java.sql.SQLException;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.hibernate.validator.InvalidValue;

/**
 * Utility class for working with database exceptions.
 * Provides tools for extracting simpler error messages SQLExceptions, and for
 * determining if a given exception indicates that an operation may succeed
 * if retried.
 *
 * @author Craig Ringer
 */
public class DbExceptionUtil {

    /**
     * Given the throwable `e', dig through the exception heirachy to find
     * an exception of the passed type and return it. If no exception of the type
     * passsed exists in the heirachy return null.
     *
     * @param <T> (inferred from c) exception type to find
     * @param c The throwable subclass to return
     * @param e A throwable from which a PSQLException should be obtained if possible
     * @return The wrapped PSQLException if any, otherwise a null value
     */
    // ExceptionUtils isn't generics-friendly
    @SuppressWarnings("unchecked")
    private static <T extends Throwable> T getNestedException(Class<T> c, Throwable e) {
        final int idx = ExceptionUtils.indexOfType(e, c);
        if (idx != -1) {
            return (T)ExceptionUtils.getThrowables(e)[idx];
        } else {
            return null;
        }
    }

    /**
     * Compat / brevity wrapper around getNestedException for Pg exceptions
     *
     * @param e Throwable to check
     * @return The PSQLException found, or null if none found
     */
    private static org.postgresql.util.PSQLException getPgException(Throwable e) {
        return getNestedException(org.postgresql.util.PSQLException.class, e);
    }

    /**
     * Compat / brevity wrapper around getNestedException for Hibernate Validation
     * exceptions
     *
     * @param e Throwable to check
     * @return The ValidationException  found, or null if none found
     */
    private static org.hibernate.validator.InvalidStateException getHibernateValidatorException(Throwable e) {
        return getNestedException(org.hibernate.validator.InvalidStateException.class, e);
    }

    /** Given the throwable `e', dig through the exception heirachy to find
     * an SQLException exception and return it. If no SQLException exception
     * exists in the heirachy return null.
     *
     * @param e The throwable to search
     * @return an SQLException if found, otherwise null
     */
    private static SQLException getSQLException(Throwable e) {
        return getNestedException(SQLException.class, e);
    }

    /**
     * Examine an exception and find the message thrown by the PostgreSQL JDBC
     * driver, if any, in the exception chain. If a PostgreSQL exception
     * was thrown, return the server error message text if there was any,
     * otherwise the full exception description.
     *
     * This method will also discover Hibernate Validator error messages and
     * return the validation error message from them.
     *
     * @param e The Throwable to search for Pg / Hibernate Validator exceptions
     * @return A human readable error message (if possible) or exception text
     */
    public static String getDbErrorMsg(Throwable e) {
        org.postgresql.util.PSQLException pe = getPgException(e);
        if (pe != null) {
            org.postgresql.util.ServerErrorMessage sem = pe.getServerErrorMessage();
            if (sem != null)
                return sem.getMessage();
            else
                return pe.toString();
        } else {
            org.hibernate.validator.InvalidStateException he = getHibernateValidatorException(e);
            if (he != null) {
                InvalidValue[] ivs = he.getInvalidValues();
                StringBuilder s = new StringBuilder();
                s.append("There are problems with the input.\nProblems found:\n\n");
                for (InvalidValue iv : ivs) {
                    s.append("    ");
                    String[] nameparts = iv.getBeanClass().getName().split("\\.");
                    s.append(nameparts[nameparts.length - 1]);
                    s.append(" ");
                    s.append(iv.getPropertyName());
                    s.append(": ");
                    s.append(iv.getMessage());
                    s.append("\n");
                }
                return s.toString();
            } else {
                return e.toString();
            }
        }
    }

    /**
     * Attempt to figure out whether a given database error could be transient
     * and might be worth a retry. Detects things like network timeouts,
     * transaction deadlocks, serialization failures, connection drops,
     * etc.
     *
     * @param e Exception thrown by persistence provider
     * @return True if the error might be transient
     */
    public static boolean isDbErrorTransient(Throwable e) {
        final SQLException se = getSQLException(e);
        if (se == null)
            return false;
        final String sqlState = se.getSQLState();
        return     sqlState.startsWith("08")   // Connection exceptions - refused, broken, etc
                || sqlState.startsWith("53")   // Insufficient resources - disk full, etc
                || sqlState.startsWith("57P0") // Db server shutdown/restart
                || sqlState.equals("40001")    // Serialization failure
                || sqlState.equals("40P01");   // Deadlock detected
    }

}