From: hal.hildebrand at me.com (Hal Hildebrand)
Date: Wed, 17 Oct 2012 18:28:49 -0700
Subject: [Pljava-dev] Savepoints and continuing from SQLExceptions [snapshot
 reference 0x19a1710 is not owned by resource owner TopTransaction]
Message-ID: <96DBE277-8790-4B4A-A120-0408D97D8464@me.com>

So this problem has been driving me nuts and I think I've finally exhausted my googling abilities in searching for the answer.

The issue is that I'd like to recover from a SQLException, roll back the savepoint and then issue another query and then reissue the failing query.  The catch works, I roll back the save point, issue the additional query.  However, when I retry the original query, I get the error:

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
	 ERROR: snapshot reference 0x19a1710 is not owned by resource owner TopTransaction

I'm sure that I am doing something wrong here, as this is the first time I'm trying to use savepoints in this way and I'm doing it inside a PL/Java function...

Anyways, the code is below.  The error above occurs at the last "query.execute();" in the code below.

Any help would be appreciated....  Hopefully, I'm just missing something basic and it's an easy fix....

-Hal

Code:
____________________

    public void addId(Long value, String key) throws SQLException {
        String tempTableName = SHARED_TEMP + key;
        PreparedStatement query = connection.prepareStatement(String.format("INSERT INTO %s VALUES (?)",
                                                                            tempTableName));
        query.setLong(1, value);
        Savepoint savepoint = connection.setSavepoint();
        try {
            query.execute();
        } catch (SQLIntegrityConstraintViolationException e) {
            if (log.isLoggable(Level.INFO)) {
                log.log(Level.INFO, String.format("table %s already has %s",
                                                  tempTableName, value));
            }
        } catch (SQLException e) {
            if (log.isLoggable(Level.INFO)) {
                log.log(Level.INFO,
                        String.format("Creating table %s", tempTableName), e);
            }
            query.close();
            connection.rollback(savepoint);

            PreparedStatement create = connection.prepareStatement(String.format("CREATE TEMPORARY TABLE %s (id BIGINT PRIMARY KEY) ON COMMIT DROP",
                                                                                 tempTableName));
            try {
                create.execute();
            } finally {
                create.close();
            }
            if (log.isLoggable(Level.INFO)) {
                log.log(Level.INFO,
                        String.format("Retrying insert of %s into %s", value,
                                      tempTableName), e);
            }
            query = connection.prepareStatement(String.format("INSERT INTO %s VALUES (?)",
                                                              tempTableName));
            query.setLong(1, value);
            query.execute();
        } finally {
            query.close();
            connection.releaseSavepoint(savepoint);
        }
    }