Retrieve inserted identity value from AWS Redshift via JDBC

Question

While working with AWS Redshift is has come to my attention getting the last inserted id from a table with an identity column via JDBC driver cannot be accomplished with either of the following methods:

RETURNING key word

or

Statement.RETURN_GENERATED_KEYS 

as mentioned in the Stack Overflow entry:

How to get a value from the last inserted row?

The above methods are not available as Redshift (as of 10/17/2013) is built on PostgreSQL version 8.0.2. See the following documentation in the following link:

http://docs.aws.amazon.com/redshift/latest/dg/c_high_level_system_architecture.html

If you are intending to use Redshift as a RDBMS it is a worthwhile effort to read the following as well:

http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html

Question:

What is the best strategy for retrieving the last inserted id on an auto-increment/serial/identity column in Redshift via PostgreSQL JDBC driver?

Answer 1

Given the Redshift engine is built on PostgreSQL 8.0.2 and the above RETURNING and Statement.RETURN_GENERATED_KEYS options are not available AND Redshift does not support CREATE SEQUENCE for the use of the CURRVAL/NEXTVAL function suite, one option would be to group two SQL statements together, INSERT and SELECT MAX([identity column]) in a JDBC transaction.

try {

    // create the JDBC connection
    Class.forName(JDBC_DRIVER);
    Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

    // start the transaction
    conn.setAutoCommit(false);  
    // create the prepared statement for insert
    PreparedStatement prpd = conn.prepareStatement(SQL_INSERT_STATEMENT);  

    // set input/output parameters as needed...

    // execute the SQL prepared statement
    int j = prpd.executeUpdate();

    // create a statement for select max()
    Statement stmt = conn.createStatement(); 
    // execute the statement to return a result set           
    ResultSet key = stmt.executeQuery(SQL_SELECT_MAX_STATEMENT);

    // initialize and retrieve the incremented identity value, in this case it is a long (bigint data type in Redshift)
    long id = 0;
    if (key.next()) {
        id = key.getLong(1);
    }

    // commit the entire transaction        
    conn.commit();

} catch (SQLException se) {
    // if an SQL exception occurs, rollback the whole deal
    try {
        if (conn!=null && !conn.isClosed()) {
            conn.rollback();
        }

    } catch (Exception e) {

    }
} catch (Exception e) {
    // roll back if something other than an SQLException occurs
    try {
        if (conn!=null && !conn.isClosed()) {
            conn.rollback();
        }
    } catch (Exception e) {

    }
} finally {
    // do whatever you want to return a value, shut down resources
    // close out JDBC resources
    try {
        if (conn!=null && !conn.isClosed()) {
            conn.setAutoCommit(true);
        }                
    } catch (SQLException se) {

    }

    try {                
        if (prpd!=null && !prpd.isClosed()) {
            prpd.close();
        }                                
    } catch (SQLException se) {

    }

    try {                
        if (stmt!=null && !stmt.isClosed()) {
            stmt.close();
        }                                
    } catch (SQLException se) {

    }

    try {
        if (conn!=null && !conn.isClosed()) {
            conn.close();
        }
    } catch (SQLException se) {

    }
}

The above will work if the SQL_INSERT_STATEMENT writes to/locks a single table. Multiple table locks will require a synchronized keyword to protect against deadlock. Selecting on a locked table will allow for the incremented identity value to be returned in a ResultSet.